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!

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

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

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

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

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?

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

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

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" <(E-Mail Removed)> wrote in message news:7638356A-058B-4E06-B106-(E-Mail Removed)...
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!
> > >