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?

 

[Aladin Akyurek]

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.

If AutoFilter is not applied to the data area, there is no need for such a formula...

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.

Sounds like you want...

=SUMPRODUCT(--(J14:J61="Facility Design"),--(F14:F61=1))

 

[matthew.pugsley]

Thank you, Aladin!

This solves my problem perfectly. I needed to exclude filtered rows. I needed to also check a condition which would vary in another column. Subtotal what is both visible and is in a row with a particular value in another field.

 

[ferre83]

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

Great solution from Aladin, however, I can't quite figure out one thing about the formula so I'm hoping someone can help me. This is a long shot since this is a very old post but hoping for an answer.

Why on the offset part of the formula, the condition for 'rows' is done with "ROW(C2:C50)-MIN(ROW(C2:C50))"? This seems to always return zero, but if adding zero on this specific formula, I get an #VALUE! error.

I'd really would appreciate an explanation since I am currently puzzled with this and since I really liked the solution I'd like to understand how it works.

Thanks.