Can I use SUMPRODUCT with IF & AND formula?

**akim125** · 05-17-2012, 03:26 PM

Hi,

Hopefully one of you Excel gurus can help with a problem I am having. I wanted to know if it was possible to use the sumproduct formula using If and And statements.

The end goal is to look up an advertiser, site, and fiscal quarter. In the data set I have spend but it is designated as actual or forecasted. I want the formula to be able to look up if the spend is actual for Q1, give me that spend, if not, give me the forecasted spend. For some reason, the formula is not working, no mater what I do. Is it possible that I can not combine a sumproduct formula with an IF and And statement?

This is the formual i was trying to play with: IF(MONTH="Actual 1/1/2012" and "Actual 2/1/2012" and "Actual 3/1/2012", SUMPRODUCT((Client=$A2)*(Advertiser=$A$1)*(Quarter=B$1)*Spend),(MONTH="Forecasted 1/1/2012" and "Forecasted 2/1/2012" and "Forecasted 3/1/2012", SUMPRODUCT((Client=$A2)*(Advertiser=$A$1)*(Quarter=B$1)*Spend)))

Attached is a dummy file: Sumproduct help.xlsx

Any help would be greatly appreciated!

**NBVC** · 05-17-2012, 03:38 PM

I am not sure I fully understand, but first thing is your named Spent range is not same size as other ranges... fix that and you will see results in the matrix.

So, if at least one of the ACTUALs for the quarter is numeric, just add the ACtuals, else the Forecasts? Is that right?

**daddylonglegs** · 05-17-2012, 03:42 PM

Perhaps better with SUMIFS.....

You need to define Spend range so that it's the same size as the other ranges, then try this formula in B2 copied across and down for the forecasted values

=SUMIFS(Spend,Client,$A2,Advertiser,$A$1,Quarter,B$1,Month,"Forecasted*")

You can use the same syntax for "Actual"