Nested SUMIF+cross sheet formula

Kelly Ospina · 04/10/19

I am struggling with a formula.

I have two sheets. One is a list of proposals, the other is a material forecast roll-up.

I am trying to build a formula that will sum up the square feet noted for a particular product within a certain month and year, but I can't seem to get it right.

Here is what I have so far:

=SUMIFS({SqFt}:{SqFt}, {Roof System}, "Product A", AND(IF({Target Ship Date}, ISDATE(@cell), {Target Ship Date}, MONTH(@cell)= 6, {Target Ship Date}, YEAR(@cell)= 2018)))

I keep getting an "Unparsable" error message.

Any help in figuring out where I've gone wrong would be appreciated.

Thank you!

Paul Newcome · 04/10/19

You have the right idea, but you are putting too much into it. The IF(S) part of SUMIF(S) implies that all statements are considered as IF statements. The function will also automatically consider all range/criteria sets to be "inside" of an AND function. You can get rid of the IF as well as the AND. That's how the function is already built to work, so adding them in goes against the correct syntax. (AND can be used in this function, but only as an efficiency when referencing the same range multiple times).

Give this a shot...

=SUMIFS({SqFt}:{SqFt}, {Roof System}, "Product A", {Target Ship Date}, ISDATE(@cell), {Target Ship Date}, MONTH(@cell)= 6, {Target Ship Date}, YEAR(@cell)= 2018)

To expand on the last little bit where AND can be used when referencing multiple sets of criteria for the same range, I notice that you have the {Target Ship Date} referenced 3 times. This can be shortened like so...

=SUMIFS({SqFt}:{SqFt}, {Roof System}, "Product A",@{Target Ship Date}, AND(ISDATE(@cell), MONTH(@cell)= 6, YEAR(@cell)= 2018))

NOTE: You only enter the range one time. You then wrap your criteria in the AND function.

Nested SUMIF+cross sheet formula

Comments

Help Article Resources

Categories