SUBTOTAL and IF

[BuzzG]

I have a database with subtotals for certain columns and auto filters for row filtering. The subtotal function is handy for getting totals for only the visible cells. Can you use the subtotal function in conjunction with a criteria? (something like "SUBTOTALIF"). I want to get sum information on the visible cells but also want exclude the values that do not satisfy the "IF" criteria. Any suggestions?

 

[Marshalls]

try this:

=if(subtotal(A1:A12=whatever,"",(subtotal(A1:A12)

something like that although i havent used subtotal before so you may need to adjust it

HTH

 

[Barrie Davidson]

Can you insert a column and use an IF statement in that column and then use that column for your subtotals?

 

[BuzzG]

The criteria is in an another column. I need a formula that solves:
"Subtotal range C2:C50 if range B2:B50 is >0". I want to use the SUMIF logic but with filtering flexibility of the SUBTOTAL function.
BuzzG

 

[Marshalls]

post your current formula here

 

[Barrie Davidson]

The criteria is in an another column. I need a formula that solves:
"Subtotal range C2:C50 if range B2:B50 is >0". I want to use the SUMIF logic but with filtering flexibility of the SUBTOTAL function.
BuzzG

Insert a column and put this in row 2
=IF(B2>0,C2,0)

Copy this down to row 50 and then use your subtotal on that column. Excel does not have a SUMIF capability in conjunction with SUBTOTAL.

Best regards,

 

[Marshalls]

Barrie is it not possible to use something like this:

=if(subtotal(A1:A12=whatever,"",(subtotal(A1:A12)

this in theory gives you a subtotalIF

 

[BuzzG]

Barrie's solution will work. I was just hoping for a fancy SUBTOTALIF solution from Microsoft.
Thanks for your help
BuzzG

 

[Aladin Akyurek]

The criteria is in an another column. I need a formula that solves:
"Subtotal range C2:C50 if range B2:B50 is >0". I want to use the SUMIF logic but with filtering flexibility of the SUBTOTAL function.
BuzzG

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C50,ROW(C2:C50)-MIN(ROW(C2:C50)),,1)),--(B2:B50>0),C2:C50)

 

[Jakobi]

I am using this formula and trying to get to do one more thing:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F14:F61,ROW(F14:F61)-MIN(ROW(F14:F61)),,1)),--(J14:J61="Facility Design"),F14:F61)

I want to subtotal F14:F61 only if it equals 100% and has "Facility Design" in another column. I have tried different things but am rather new to the world of formulas.

I dont even know if I need such a complex formula for my needs. I just to total the 100% in a column only if another column has "blah blah".

Any insight would be great. Thanks.