Adding Subtotal to a countifs function

[Cailean]

Hi Could somebody give me some assistance on how i could add a subtotal to allow me to use this Countifs function when filtering data?

=COUNTIFS(Table2[0101-Interface Variation MF],">0",Table2[0101-Interface Variation MF],"<99.99")

Thanks.


Cailean.

 

[miless2111s]

Can you give some more information = what do you mean by sub total and how do you want it displayed / used?

 

[Cailean]

Can you give some more information = what do you mean by sub total and how do you want it displayed / used?

I want to count the number of values in a column that fall between 0 -100. If i filter by week number for example i need the value to update by the number of cells shown?

Hope this is clearer.

Cailean.

 

[miless2111s]

Very hard to make it do exactly as you say without using VBA and I'm not even sure if that's possible (however the wizards around here seem to be able to make it do anything).
One option would be to be able to modify what the calculation is looking at by changing a value in a cell.
Thus you would have your Week number in Cell A1 and have weeknumber as column in the data. Then you can use a countifs type formula to count your data set by looking at weeknumber column and comparing to the contents of A1 (and any other criteria that you want to use.
HTH
Miles

 

[pgc01]

Hi Cailean
Welcome to the board

Try:

=SUMPRODUCT(--(Table2[0101-Interface Variation MF]>0),--(Table2[0101-Interface Variation MF]<99.99),SUBTOTAL(2,OFFSET(Table2[0101-Interface Variation MF],ROW(Table2[0101-Interface Variation MF])-MIN(ROW(Table2[0101-Interface Variation MF])),,1)))

 

[Cailean]

Hi Cailean
Welcome to the board

Try:

=SUMPRODUCT(--(Table2[0101-Interface Variation MF]>0),--(Table2[0101-Interface Variation MF]<99.99),SUBTOTAL(2,OFFSET(Table2[0101-Interface Variation MF],ROW(Table2[0101-Interface Variation MF])-MIN(ROW(Table2[0101-Interface Variation MF])),,1)))

Perfect. This is exactly what i was looking for.

Thank you.

Cailean.

 

[pgc01]

You're welcome. Thanks for the feedback.