Sumif Function within Subtotal

[mcbiz77]

I'm looking for a subtotal equation that I can use that sums a total column when filtered based on a criteria (sumif) for tbd. I'm currently using the following equation........=sumif($A:$A,"tbd*",B:B). The table below shows what I have when a filter is in place. Three names pop up when the filter is in place, however I need to find the total for tbd. What subtotal equation can I use to find the total for tbd when a filter is in place? The output should be tbd=1.

[TABLE="width: 150, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]tbd[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[JohnGSnyder01]

This formula should working if I am understanding you correctly.

=SUMIF(A2:A5,"="&"tbd",B2:B5)

 

[mcbiz77]

Yes that formula =SUMIF(A2:A5,"="&"<acronym title="To be discussed" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">tbd</acronym>",B2:B5) does work however when a filter has been applied to the spreadsheet it shows all the tbd within the entire spreadsheet and not just the tbd within the filter. I'm looking for a formula that performs the same way as a =SUBTOTAL(109...... command.

 

[Jonmo1]

Use a helper column for the subtotal.

Say C2
=SUBTOTAL(103,A2)
And fill down to C5

Then use
=SUMIFS(B2:B5,A2:A5,"tbd",C2:C5,1)

 

[mcbiz77]

[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Level[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Dave[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Dave[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Amy[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]=subtotal(109,C2:C8)[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]=sumif(B2:B8,"John",C2:C8)[/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]



















The formula doesn't seem to be working. The table above is what i'm working with. The total comes out to 10 which is correct. The sum for John (5) is correct too. However, if I was to put a filter on column A and filter only by Level B then I would like my total for John to only sum the Qty under that filter. For example, if I was to filter by level B then my Total changes from 10 to 5 which it does with teh subtotal formula however the total for John will need to go from 5 to 2. Do you know what formula I could use instead of =sumif(B2:B8,"John",C2:C8)?

 

[Jonmo1]

That's not exactly what I suggested

Unknown
	A	B	C	D	E	F
1	Leval	Name	Qty	Subtotal		5
2	A	John	1	1
3	A	Amy	1	1
4	B	Dave	1	1
5	A	John	2	1
6	B	Dave	2	1
7	A	Amy	1	1
8	B	John	2	1
Sheet1

Cell Formulas
Range	Formula
F1	=SUMIFS(C2:C8,B2:B8,"John",D2:D8,1)
D2	=SUBTOTAL(103,A2)
D3	=SUBTOTAL(103,A3)
D4	=SUBTOTAL(103,A4)
D5	=SUBTOTAL(103,A5)
D6	=SUBTOTAL(103,A6)
D7	=SUBTOTAL(103,A7)
D8	=SUBTOTAL(103,A8)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C8)-ROW(C2),0,1),(B2:B8="John")+0)

Note. Function number 9 instead of 109 might also work as intended.

 

[mcbiz77]

After adding the formulas as shown above, i'm having problems with the filter. When filtering for Level A within column A, I get all the Level A but I also get a Level B which I shouldn't. It seems like the formulas in column D are messing up the filters.

 

[Jonmo1]

Is that data in an actual TABLE ?

This is an actual bug in Excel, that the presense of a subtotal function in a Table makes that Table think there is a totals row. That makes the last row ignored.

Try changing the formula in D2 to
=SUM(SUBTOTAL(103,A2))

Or if you have XL2010+
=AGGREGATE(3,1,A2)

 

[mcbiz77]

Aladin, Is there a parenthesis missing in the equation?