Calling all MS Excel Gurus: Nested SUMIF (?)

[ChristopherPaul]

Anyone here know the best way to nest multiple SUMIF formulas?

I need to sum only those cells that fall within a date range AND if they meet a code requirement within that conditional date range.

So far I can SUMIF the date range and SUMIF the code requirement, but not SUM the code requirement within the specified range.

I can accomplish my purposes with a pivot table for the time being, but ultimately I would like to create a formula to fit nice and neat within a summary sheet.

 

[tcalbrecht]

SUMIF only works on a single criterion. I'm not aware of any way to nest formulas using SUMIF.

You can do something like this:

=SUMPRODUCT((A2:A6>DATEVALUE("4/1/2006"))*(B2:B6="B"),C2:C6)

       A         B      C
1    Date       Code  Value
2  3/21/2006     A     100
3  3/21/2006     B     120
4  4/21/2006     A     200
5  4/21/2006     B     240
6  5/21/2006     C     300

The formula assumes column A contains real date serial numbers.

 

[ChristopherPaul]

SUMIF only works on a single criterion. I'm not aware of any way to nest formulas using SUMIF.

You can do something like this:

=SUMPRODUCT((A2:A6>DATEVALUE("4/1/2006"))*(B2:B6="B"),C2:C6)

       A         B      C
1    Date       Code  Value
2  3/21/2006     A     100
3  3/21/2006     B     120
4  4/21/2006     A     200
5  4/21/2006     B     240
6  5/21/2006     C     300

The formula assumes column A contains real date serial numbers.

Tom you did it, thank you. I should have just sought council in the first place instead of forever trying to fit a square SUMIF function into a round SUMPRODUCT hole.

You made my day!

 

[ChristopherPaul]

For reference (mainly mine), the exact formula that does all that I need and checks out in comparrison to the pivot table data is:

=SUMPRODUCT ( ( $A$3:$A$65536 >= DATEVALUE ( "10/1/2007" ) ) * ( $A$3:$A$65536 < DATEVALUE ( "11/1/2007" ) )*( $B$3:$B$65536="F-D" ),$C$3:$C$65536 )

I had to make the array ranges equal the first cell with data to the last possible cell in MS Excel 2003 (2007 goes to a million something). I guess because of the column headers the simple "A:A" would not work, so the only way I could make the formula work with constantly changing row numbers is to enter it as "A3:A65536."

Anyway, this really did make my day. I have been pondering a possible solution for a while. Thanks again Tom!