Complicated SUMPRODUCT
 

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!

Complicated SUMPRODUCT
 

Hi Barb,

=sumproduct(--(i2:av2="Sht In Form")*(i3:av3="Yes))
=sumproduct(--(i2:av2="Sheet Present")*(i3:av3="Yes))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Barb Reinhardt" escreveu:

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!

Complicated SUMPRODUCT
 

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!

Complicated SUMPRODUCT
 

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!

Complicated SUMPRODUCT
 

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!

Complicated SUMPRODUCT
 

Not sure, but perhaps a "combination" like this:
=SUMPRODUCT((ISNUMBER(MATCH(I2:AV2,{"Sht In Form","Sheet
Present"},0)))*(I3:AV3 = "YES"))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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?

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!

Complicated SUMPRODUCT
 

I'm still not sure what you mean if the formula in my last post didn't cover
what you wanted... i.e.:
=SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES"))

Can you post some examples using english, try covering every possible
combination of values and what you would like the result to be... e.g.:

I2="Sht in Form" AND I3="YES" then return TRUE
I2="Sheet Present" AND I3="YES" then return TRUE
I2="Sht in Form" AND I3="NO" then return FALSE
I2="blah" AND I3="YES" then return FALSE
etc. etc....

--
Regards,
Dave


"Barb Reinhardt" wrote:

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!

Complicated SUMPRODUCT
 

David,

Well I can give you the formula for that right now:

=0

I totally understand what you said. I am trying to think of a situation that it won't be zero. The only thing that comes to mind is the following.

I2:AV2 on two *different* worksheets or workbooks.

If we have two sheets, can we say "intersect?" Can we use SUMPRODUCT on two different sheets?

Please forgive me if I am not making sense or making it more confusing. But I am curious. Feel free to correct me.

I'll stay tuned. This sounds like an interesting puzzle.

Epinn

"David Billigmeier" wrote in message ...
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!