Formula results in error - don't know why
 

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

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

Formula results in error - don't know why
 

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



"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
I get a value error with the below,
Any ideas why?

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

Formula results in error - don't know why
 

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))

--
HTH...

Jim Thomlinson


"Jeremy" wrote:

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

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

Formula results in error - don't know why
 

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




"Jeremy" wrote:

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

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

Formula results in error - don't know why
 

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 wrote:

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

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

--

Dave Peterson

Formula results in error - don't know why
 

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

"Dave Peterson" wrote:

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 wrote:

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

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

--

Dave Peterson