Help: Excel Formula, Maybe code?

[Walshie1987]

Hi,

I've got a Spreadsheet with various info on it. I use filters and want to add a formula to do the following:

e.g/
A B C
1 Test
__________________________________________

10 1000 Test

16 100 Not inc.

20 50 Test.


Imagine above is a spreadsheet, after being filtered (thats why numbers are skipped).

I want a formula that will do the following:

Look at the visible data after the filter, If any of the info matches cell A1 then do a sum of the column next to it e.g find the word "Test" in filter (C10 and C20) look next to the cells (B10 and B20) and add the values, ignoring the others (C16)

Hope I made that clear enough, quite hard to explain.

Cheers
Chris

 

[AnotherHiggins]

How about you also filter on C for "Test", then use the SubTotal function. It will only consider the visible cells.

=subtotal(9,B10:B20)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Walshie1987]

Thanks for that, That will also include the value in B16 though which I don't want included. It's got me this one lol been trying to build SUMIF function into the SUBTOTAL function but no look yet.

Cheers

 

[AnotherHiggins]

So why not do an additional filter on C to get rid of everything except "TEST"? That would eliminate row 16 from the filtered data and allow a simple SubTotal to work.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Walshie1987]

Yeah sorry I see what you mean, The thing is I also want to use the formula do a SUM for "Not Inc." too and for others so

In my "Summary" Section I'll have:

Test in one cell and the total (1050) in the cell next to it

Then below
Notinc. in one cell and the total (100) in the cell next to it.

May have to just go for your option though

Thanks again

 

[AnotherHiggins]

Go to Data > Subtotals. This can be done within filtered data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Is that working for you, Walshie1987?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Walshie1987]

Sorry finished work at 5pm GMT yesterday so didn't get chance to try the above. Will give it a go when I get chance today.

Thanks

 

[AnotherHiggins]

Any luck?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WinblowsME]

Sub Group_Data()
   Dim dict As Variant, curr_key As Variant
   Dim i As Integer, last_row As Long
   Dim quantity As Integer, desc As String
   
   Set dict = CreateObject("Scripting.Dictionary")
   
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
                                     SearchOrder:=xlByRows).Row
   
   For i = 2 To last_row
      ' The AutoFilter sets the RowHeight of the excluded rows to 0

      If Rows(i).RowHeight > 0 Then
         quantity = CInt(Cells(i, 2))
         desc = Cells(i, 3)
         dict.item(desc) = dict.item(desc) + quantity
      End If
   Next i

   For Each curr_key In dict.Keys
      Debug.Print curr_key & " " & dict.item(curr_key)
   Next
End Sub

 

[WinblowsME]

Dim i As [COLOR=red]Long[/color], last_row As Long