Adding a clause to SUMPRODUCT

[bradharris2]

I can't seem to correctly add in an additional clause to my sumproduct formula below. Trying to add Data2!$E$3:$E$39073="Active" as an additional clause however having issues. Any help is greatly appreciated!

=SUMPRODUCT((Data2!$D$3:$D$39073=$A2)*(Data2!$F$1:$BJ$1=$A2),Data2!$F$3:$BJ$39073)

[TMS]

Maybe:

=SUMPRODUCT((Data2!$D$3:$D$39073=$A2)*(Data2!$F$1:$BJ$1=$A2)*(Data2!$F$3:$BJ$39073)*(Data2!$E$3:$E$39073="Active"))

Can't test it, no data.


Regards, TMS

[bradharris2]

Thanks TMS. Unfortunately this doesn't work as I get a #VALUE! error which is what I was getting before.

[Kevin UK]

How about uploading a small sample file bradharris2

[TMS]

Well, generally, in an Array formula (which is what SUMPRODUCT is), the ranges are usually the same size. But, I assumed that what you had worked, so I tagged on the additional criterion. Comes back as 0 in my test but then I don't have any data to test it with.

Regards, TMS

[Norie]

On a blank worksheet Trevor's formula works and gives 0.

Perhaps there's a problem with the data, not the formula?

[bradharris2]

Hey All - It was a one cell data error. Thank you all for your help!

[TMS]

Yep, this works:

=SUMPRODUCT((Data2!$D$3:$D$39073="Q3 2009")*(Data2!$F$1:$BJ$1="Q3 2009")*(Data2!$F$3:$BJ$39073)*(Data2!$E$3:$E$39073="Active"))


But it looks like you have no data that meets the criteria. I changed some data to test it and it will pick up the values

Regards, TMS