Sumproduct with three arrays

[Alcala]

Hello and thank you in advance for your assistance.

I have a spreadsheet as follows:

Column C= Revenue
Column D= Units
Column E = Months, as a number (i.e. 1-12)
Column F = Status, expressed as either Prospect or Definite
Column G = Year (i.e. 2009)

Column A,B are labels and not used in the calculations.

I want to sum the units and revenue based on year, month, and status (i.e.,
how many units sold in January of 2009 that are in Prospect status). The
result for unit and revenue are in two other columns, K & L (i.e. there is a
summary cell for unit based on month, year and status, and another for
revenue based on month, year, and status)

The formula I am using is
=SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),D1500) for units, and

=SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),C1:C500) for revenue

Excel returns 0, which I know is incorrect. I have tried removing the
quotes from the year and the month, but still get 0. I have checked the cell
format for the result cell and it is set to number format.

Thanks again for your thoughts.

Best regards,

Alberto

 

[~L]

Definitely leave the quotes off on the year and month and make sure numbers
are numbers and not stored as text.
Even if you set a number-stored-as-text to number format, it may still be
read as text by Excel. Try copying a blank cell and using paste special to
subtract from all of the cells in your year and month ranges.

Also, use absolute references on your ranges if the formula is going to move
at all.

Finally, in your data make sure there are no leading or trailing spaces (or
characters that look like spaces) in the text columns by editing the entry in
the formula bar and selecting the entire contents.

=SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$D$1:$D$500)

=SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$C$1:$C$500)

If none of the above work, try hitting F9 to see if your workbook is on
manual calculation.

And if none of that works, open a blank sheet and do the following:
Highlight 500 cells and in the first cell type =Sheet1!$G$1:$G$500=2009 then
enter it using control+shift+enter (this assumes your sheet name is sheet1,
change that if you need to). Repeat this for ranges F and E. This will show
you exactly how the contents of each array are evaluating.
If something is not a match and you know it should be, then you know
something is wrong with the data entered in that cell. Copy the entire
contents of the offending cell, push CTRL+H, paste it into the find field and
replace with a clean entry that you know is correct.

 

[Alcala]

Thanks! It is working now, and I suspect it is the absolute reference and
removing the "" that did the trick as the formulas are now returning
confirmed numbers and I did not do any of the other steps.

Thanks again for your assistance, and have a great weekend!.

Regards,

Alberto