# SUBTOTAL and IF

This is a discussion on SUBTOTAL and IF within the Excel Questions forums, part of the Question Forums category; I have a database with subtotals for certain columns and auto filters for row filtering. The subtotal function is handy ...

1. ## SUBTOTAL and IF

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?

2. ## Re: SUBTOTAL and IF

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

3. ## Re: SUBTOTAL and IF

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

4. ## Re: SUBTOTAL and IF

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

6. ## Re: SUBTOTAL and IF

Originally Posted by 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
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,

7. ## Re: SUBTOTAL and IF

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

8. ## Re: SUBTOTAL and IF

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

9. ## Re: SUBTOTAL and IF

Originally Posted by 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
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C50,ROW(C2:C50)-MIN(ROW(C2:C50)),,1)),--(B2:B50>0),C2:C50)

10. 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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•