Formula results in error - don't know why

[Jeremy]

I get a value error with the below,
Any ideas why?

=SUMPRODUCT((Data!K7:K9999='Monthly Summary'!A10)*(Data!AE7:AE9999=Data!C1))

 

[Peo Sjoblom]

You have either text values in one or both of the ranges or you have a value
error in one of them.

If you use the below instead


=SUMPRODUCT(--(Data!K7:K9999='Monthly
Summary'!A10),--(Data!AE7:AE9999=Data!C1))


then it should ignore text, of course if you intended to have all numbers
they will not be discovered this way


--


Regards,


Peo Sjoblom

 

[Jim Thomlinson]

The syntax of the formula is fine but depending on your data this might work
better...

=SUMPRODUCT(--(Data!K7:K9999='Monthly Summary'!A10),
--(Data!AE7:AE9999=Data!C1))

 

[Jeremy]

Jim and Peo:
Since both of you gave me the same formula it looks like you're in the same
train of thought.

The formula you provided also gave a value error. Perhaps I should explain
a bit more about what I'm trying to do.

1-Data!K7:K9999 is a problem category input chosen from a validation list

2-Data!AE7:AE999 is a calculation of month and date based on when the
complaint was filed (Data!N7:N9999)

=IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7))

***This is a calculaiton that my boss put in, I don't get it or like it

3-'Monthly Summary'$C$1 is the cell I am using to change all of the data on
the summary table below. Esentially I want to be able to type in the month
and year and have all the data below change based on the information in Data!

Once I can understand how this formula works, I think I can change it a
little for each part I need.

Let me know if this clears it all up

 

[Dave Peterson]

Do you have any text in K7:K9999 on Data or in AE7:AE9999 on Data?

Do you have any errors in those ranges?

If you have text, you can change your formula:
=SUMPRODUCT(--(Data!K7:K9999='Monthly Summary'!A10),--(Data!AE7:AE9999=Data!C1))

If you have errors, you can use an array formula or fix the errors.

 

[Jeremy]

yes, I did. I corrected them and it all works now. Thanks for the help all
of you guys.