Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2.

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't count
it.

Cheers
--
Deirdre

Try this:

=SUMPRODUCT(((NoPass="W")+(NoPass="N")+(NoPass="D" ))*
(Matchup="Matched"))

The * can be read as AND and the + as OR.

Hope this helps.

Pete

On Jan 15, 12:23*pm, Diddy wrote:
Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2.

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't count
it.

Cheers
--
Deirdre

Your current formula doesn't work, the call cannot be W, N and D.

=SUMPRODUCT((NoPass={"W","N","D"})*(Matchup="Marke d"))

--
__________________________________
HTH

Bob

"Diddy" wrote in message
...
Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is
a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and
D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2.

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't
count
it.

Cheers
--
Deirdre

Thanks Pete,

I knew I wasn't getting it!!!

Thanks for the explanation of * and +.

Thanks again
--
Deirdre


"Pete_UK" wrote:

Try this:

=SUMPRODUCT(((NoPass="W")+(NoPass="N")+(NoPass="D" ))*
(Matchup="Matched"))

The * can be read as AND and the + as OR.

Hope this helps.

Pete

On Jan 15, 12:23 pm, Diddy wrote:
Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2.

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't count
it.

Cheers
--
Deirdre

Thanks Bob,

Great solution!

As I was posting the question I did wonder if sumproduct was even what I
needed, but I shouldn't have worried.

Cheers and thanks again
--
Deirdre


"Bob Phillips" wrote:

Your current formula doesn't work, the call cannot be W, N and D.

=SUMPRODUCT((NoPass={"W","N","D"})*(Matchup="Marke d"))

--
__________________________________
HTH

Bob

"Diddy" wrote in message
...
Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is
a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and
D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2.

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't
count
it.

Cheers
--
Deirdre

You're welcome, Deirdre - thanks for feeding back.

Pete

On Jan 18, 11:03*pm, Diddy wrote:
Thanks Pete,

I knew I wasn't getting it!!!

Thanks for the explanation of * and +.

Thanks again
--
Deirdre



"Pete_UK" wrote:
Try this:

=SUMPRODUCT(((NoPass="W")+(NoPass="N")+(NoPass="D" ))*
(Matchup="Matched"))

The * can be read as AND and the + as OR.

Hope this helps.

Pete

On Jan 15, 12:23 pm, Diddy wrote:
Hi everyone,

I'm using the following sumproduct formula

=SUMPRODUCT(--(NoPass="W"),--(NoPass="N"),--(NoPass="D")) where NoPass is a
named dynamic range in Column C in Sheet 2. Formula is in Sheet 1

I would like to add a further criteria so that it will only count W, N and D
if corresponding cell in Range Matchup = "Matched" Column F Sheet 2..

As I'm typing this I'm wondering if it's possible and If I'm way off mark
here trying to do this with this formula ???

Basically what I would like to happen is if there is a W,D or N in say C2
and F2 =Matched then count it, if F2 does not equal matched then don't count
it.

Cheers
--
Deirdre- Hide quoted text -

- Show quoted text -