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.

Name	Total
John	1
tbd	1
Rick	1

<tbody>
</tbody>

 

[SpillerBD]

Filtering and summing data from a table.... Use a Pivot Table. It handle both the various subtotals you want. (all levels or none) as well as facilitating all the filtering you may need.
2010 added Slicers for PivotTables and 2013 added the TimeLine-Slicer....

 

[mcbiz77]

Aladin, I found the missing parenthesis. Thank you very much for the formula. It works great.

 

[Aladin Akyurek]

Aladin, I found the missing parenthesis. Thank you very much for the formula. It works great.

Indeed...

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

 

[mcbiz77]

I'm using the equation above however instead of using the name "John", I would add anything that contains "tbd". Some of the names are tbd, tbd 1, tbd 2, etc. I've tried using "*tbd" and "tbd*" however the total doesn't seem to add up.

 

[Aladin Akyurek]

I'm using the equation above however instead of using the name "John", I would add anything that contains "tbd". Some of the names are tbd, tbd 1, tbd 2, etc. I've tried using "*tbd" and "tbd*" however the total doesn't seem to add up.

Needs adjusting for that request...

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