IF formula in SUMPRODUCT

[rezary]

Hi everybody,

I'm trying to write a SUMPRODUCT formula (cell H2 in the attachment) that gives different outputs according to the value of another cell (H1), but the output values are incorrect. I suppose it's because the "else" value given in the IF formula won't be recognized as a formula but as a text. I would appreciate a lot if anybody found a solution to this problem. Thanks a lot in advance.

[zbor]

How about this:

=IF(H1="ALL";SUM(C:C);SUMIF(B:B;H1;C:C))

[rezary]

Hi zbor,
Thanks for your suggestion. The thing is that I want to include several conditions, as seen in the updated attachment. I guess it could be done with the SUMIFS formula, but the wanted formula is going to be entered into a lot of cells with reference to huge amounts of data. So I want to avoid nesting that many IF formulas in order to prevent the file from slowing down.

[daddylonglegs]

Try like this

=SUMPRODUCT((data_category=J1)*(data_item=J3),(J2="All")+(data_country=J2),data_sales)

[daddylonglegs]

The above just accommodates J2 potentially being "All", if you want to allow that for J1 and J3 too then try like this

=SUMPRODUCT((J1="All")+(data_category=J1),(J3="All")+(data_item=J3),(J2="All")+(data_country=J2), data_sales)

Note: assumes that "All" won't appear in the data......

[rezary]

Hi daddylonglegs,

That's exactly what I was looking for. Thank you very much!

Cheers