Sumproduct Value Error Message

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SunshineMR - 19 Mar 2008 04:05 GMT

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!

Reply to this Message

T. Valko - 19 Mar 2008 04:51 GMT

> Hi google groups?
>
[quoted text clipped - 10 lines]
>
> Thanks!

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

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Adilson Soledade - 19 Mar 2008 11:08 GMT

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.

Signature

Adilson Soledade

> > Hi google groups?
> >
[quoted text clipped - 13 lines]
> There's nothing wrong with your formula. Are there any #VALUE! errors in any
> of the referenced ranges?

Reply to this Message

T. Valko - 19 Mar 2008 18:18 GMT

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

Signature

Biff
Microsoft Excel MVP

> Try this:
> =SUMPRODUCT(N('Other Worksheet'!$K$4:$K$2000=1997)*N('Other
[quoted text clipped - 22 lines]
>> any
>> of the referenced ranges?

Reply to this Message

chefelizabethm@gmail.com - 31 Mar 2008 17:40 GMT

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.

Reply to this Message

David Biddulph - 31 Mar 2008 18:25 GMT

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)?
--
David Biddulph

>I am having a similar problem this morning.
>
[quoted text clipped - 3 lines]
> It is returning the #VALUE! error. The arguements are all correct and
> my reference data is also correct on the other sheet.

Reply to this Message

Dave Peterson - 31 Mar 2008 18:29 GMT

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.

> I am having a similar problem this morning.
>
[quoted text clipped - 3 lines]
> It is returning the #VALUE! error. The arguements are all correct and
> my reference data is also correct on the other sheet.

Signature

Dave Peterson

Reply to this Message