Sumproduct Value Error Message

[SunshineMR]

Hi google groups?

I have been searching online to find a way to make a count by two
criteria - year and Y/N. In all the searching it seems I have to do a
sumproduct, and wrote the formula below. When I do countifs for each
individual criteria, I get the actual number (e.g. =COUNTIF('Other
Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I
receive a #VALUE! error message. I've used the " " for the text.
What else could cause this error message?

=SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M
$4:$M$2000="Y"))

Thanks!

 

[T. Valko]

Hi google groups?

I have been searching online to find a way to make a count by two
criteria - year and Y/N. In all the searching it seems I have to do a
sumproduct, and wrote the formula below. When I do countifs for each
individual criteria, I get the actual number (e.g. =COUNTIF('Other
Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I
receive a #VALUE! error message. I've used the " " for the text.
What else could cause this error message?

=SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M
$4:$M$2000="Y"))

Thanks!

There's nothing wrong with your formula. Are there any #VALUE! errors in any
of the referenced ranges?

 

[Adilson Soledade]

Try this:
=SUMPRODUCT(N('Other Worksheet'!$K$4:$K$2000=1997)*N('Other
Worksheet'!$M$4:$M$2000="Y"))
SUMPRODUCT needs numeric arguments. The arguments in the previous version of
your formula were an array of TRUE and FALSE values. Using the N funtion
these will become an array of ! and ) values.

 

[T. Valko]

The arguments in the previous version of your

formula were an array of TRUE and FALSE values
Using the N funtion these will become an
array of ! and ) values.

I guess you missed this part of the formula:

....2000=1997)*('Other Worksheet'!...

N does nothing that the "*" already doesn't do so the use of the N function
is redundant.

 

[chefelizabethm]

I am having a similar problem this morning.

=SUMPRODUCT(('0107'!$E1:$E200="CURRENT")*('0107'!
$F1:$F200="VIRTUOSO")*('0107'!$C1:$C200))

It is returning the #VALUE! error. The arguements are all correct and
my reference data is also correct on the other sheet.

 

[David Biddulph]

So are you going to help other readers of the group by telling them the
cause of your problem? Normally we would have assumed that there was an
error in your input values, but you are assuring us that this is not the
case, so I am sure that other readers would be delighted to hear your
explanation.

Just to satisfy our idle curiosity, what do you get from the formula
=SUM(--ISTEXT('0107'!C1:C200)) entered as an array formula (Control Shift
Enter, which will put curly brackets around the formula)?

 

[Dave Peterson]

Do you have any text in C1:C200 of the 0107 worksheet?

Do you have any errores in E1:E200 or F1:F200 of that same sheet?

Remember to look at headers and hidden (by autofilter???) rows, too.