SUMPRODUCT

  • Thread starter Thread starter dwake
  • Start date Start date
D

dwake

Could somebody explain to me why this formula is not counting my data.

SUMPRODUCT((Projected!I:I="Pilot")*(Projected!O:O="FY2009-Q4"))

I think I have written it correctly. I am trying to have the formula look
at a worksheet and count if the cell in row I equals pilot and the cell in
row O equals FY2009-Q4. For some reason excel is not able to calculate the
formula.

On a note, this formula used to calculate, but I have a macro select all the
cells and delete before importing the data with column headers and
formatting. After I deleted the cells and repasted the new data, it is not
calculating even after I engage a recalc. Any help would be appreciated.
 
Hello,

Don't use I:I and O:O but something like I1:I100 and O1:O100
or
buy Excel 2007.

Regards,
Bernd
 
Unless you are using Excel 2007, you can't use whole columns with SUMPRODUCT.
This works for me (edit range to match your data):

=SUMPRODUCT((Projected!I1:I10000="Pilot")*(Projected!O1:O10000="FY2009-Q4"))

Hope this helps,

Hutch
 
Hi,

The formula works fine in E2007 but in earlier versions you can't use full
columns. What result are you getting?

Mike
 
I'm getting a number error.

Mike H said:
Hi,

The formula works fine in E2007 but in earlier versions you can't use full
columns. What result are you getting?

Mike
Click to expand...
 
I'm getting a number error.

Then if you read either my first ressponse or either of the other 2 you have
then you'll see what the remedy is!!

Mike
 
Back
Top