Complicated SUMPRODUCT
I guess I'm not making myself clear.
I want something like this:
=SUMPRODUCT(--(IF($I$2:$AV$2="Sht in
Form",$I3:$AV3="YES")),--(IF($I2:$AV2="Sheet Present",$I3:$AV3 = "YES")))
But I'm getting a #VALUE error. Suggestions?
"David Billigmeier" wrote:
What do you mean, you need a count of when I2:AV2="Sht In Form" *AND*
I2:AV2="Sheet Present" *AND* I3:AV3="YES"??? Well I can give you the formula
for that right now:
=0
There will never be a time when a cell equals "Sht In Form" *AND* "Sheet
Present"
Do you mean you need a count of when (I2:AV2="Sht In Form" *OR*
I2:AV2="Sheet Present") AND I3:AV3="YES"? In this case (by the way this is
called the Union of the I2:AV2 ranges, not Intersection):
=SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES"))
--
Regards,
Dave
"Barb Reinhardt" wrote:
That gives me the count of one or the other, but I need the count of when the
two intersect. Any other suggestions?
Thanks
"David Billigmeier" wrote:
Looks like you need 2 different formula's:
=SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES"))
=SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES"))
--
Regards,
Dave
"Barb Reinhardt" wrote:
I need a formula to do the following:
If I2:AV2 = "Sht In Form" and I3:AV3 = "YES"
If I2:AV2 = "Sheet Present" and I3:AV3 = "YES"
What I really need is to count when the first set intersects the second set
and I'm not sure how to write that equation.
Thanks!
|