SUMIF with 2 conditions

[Guest]

I am trying to use SUMIf with 2 conditions, I see someone else asked this
question before but I still can't get it to return anything other than #Value!

Can anyone help me?

=SUMPRODUCT(--('Raw Data'!F2:F60000="TEST");--('Raw
Data'!V2:V60000="Y");'Raw Data'!S1:S60000)

Column F and V contain text values and column S contains numerica values.
does this make a difference?

Thanks in advance....

 

[Bondi]

Hi Louise,

Your formula looks correct. I think that if you change the:
Raw Data'!S1:S60000
to
Raw Data'!S2:S60000
So that the array will match it might work.

Regards,
Bondi

 

[Guest]

Try

=SUMPRODUCT(--('Raw Data'!F2:F60000="TEST"),--('Raw
Data'!V2:V60000="Y"),('Raw Data'!S1:S60000))

 

[John Michl]

Bondi is correct. In order to use SUMPRODUCT, each array must have
exactly the same number of members. In your case, the first two arrays
have 59,999 members and the third 60,000. Note that the don't have to
be the same rows, just the same size. Also, I've not used semi-colons
in my Sumproduct formulas. If you are still having problems try a
comma.

- John Michl

 

[Bondi]

Hi John,

The semi-colons are the argument seperator in (at least) some Eurpean
countries.Changable in the regional settings i think.

Regards,
Bondi

 

[Guest]

Thank you John and Bondi - it works perfectly now!

 

[Ken Wright]

Barb, that would fail for the same reason as the original did, ie the ranges
are not equal in size.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------