OR or ARRAY help
 

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Try this

=SUMPRODUCT(((F8:F57="W/M")*(D8:D57="ABC"))+(D8:D57="LMN")+(D8:D57="XYZ "))

"Toppers" wrote:

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Hi

Try the non-array entered formula

=SUMPRODUCT(($F$8:$F$57="W/M")*($C$8:$C$57={"XYZ","ABC","LMN","QRS"}))

--
Regards

Roger Govier


"Bigfoot17" wrote in message
...
I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are
"XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Hi Big Foot:

Try using sum product with the ors added together and then checked if larger
than 0.

=SUMPRODUCT(
--($F$8:$F$57="W/M"),
--(
(--($D$8:$D$57="XYZ")
--($D$8:$D$57="ABC")
--($D$8:$D$57="LMN")
)0)
)
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Sorry missed one of the conditions

=SUMPRODUCT(((F8:F57="W/M")*(D8:D57="ABC"))+(D8:D57="LMN")+(D8:D57="XYZ")+ (D8:D57="QRS"))

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Try...

=SUM(IF($F$8:$F$57="W/M",IF(ISNUMBER(MATCH($D$8:$D$57,{"XYZ","ABC","LMN" ,
"QRS"},0)),1)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Bigfoot17 wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

This was extremely helpful, and it appears I was so confused I was making it
harder than it needed to be. However, I'd like to push the envelope a bit
further ...

I need to evaluate several ranges for the "W/M" and corresponding "XYZ" etc.
Would this be a matter of adding several SUMPRODUCTS together?
=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))+SUMPRO DUCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.


"Toppers" wrote:

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC", "LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Hi

Yes, that would be the way to do it.

--
Regards

Roger Govier


"Bigfoot17" wrote in message
...
This was extremely helpful, and it appears I was so confused I was
making it
harder than it needed to be. However, I'd like to push the envelope a
bit
further ...

I need to evaluate several ranges for the "W/M" and corresponding
"XYZ" etc.
Would this be a matter of adding several SUMPRODUCTS together?
=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))+SUMPRO DUCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.


"Toppers" wrote:

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that
are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC",
"LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

I get a "Formula Too Long" error since their are two many ranges. Any other
suggestions to make this work?

"Roger Govier" wrote:

Hi

Yes, that would be the way to do it.

--
Regards

Roger Govier


"Bigfoot17" wrote in message
...
This was extremely helpful, and it appears I was so confused I was
making it
harder than it needed to be. However, I'd like to push the envelope a
bit
further ...

I need to evaluate several ranges for the "W/M" and corresponding
"XYZ" etc.
Would this be a matter of adding several SUMPRODUCTS together?
=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))+SUMPRO DUCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.


"Toppers" wrote:

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s that
are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC",
"LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Hi

How many ranges are we talking about?
You could name the ranges e,g, Rg1 for $F$8:$F$57 would reduce the
length by 7 for each range used.
Split the formula into 2 or more cells, then have your result as the sum
of the 2 (or more) cells

--
Regards

Roger Govier


"Bigfoot17" wrote in message
...
I get a "Formula Too Long" error since their are two many ranges. Any
other
suggestions to make this work?

"Roger Govier" wrote:

Hi

Yes, that would be the way to do it.

--
Regards

Roger Govier


"Bigfoot17" wrote in message
...
This was extremely helpful, and it appears I was so confused I was
making it
harder than it needed to be. However, I'd like to push the
envelope a
bit
further ...

I need to evaluate several ranges for the "W/M" and corresponding
"XYZ" etc.
Would this be a matter of adding several SUMPRODUCTS together?
=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))+SUMPRO DUCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.


"Toppers" wrote:

TRY:

=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))

HTH

"Bigfoot17" wrote:

I am struggling!

This statement will count the number of "W/M"s that are "XYZ":
{=SUM(IF(($F$8:$F$57="W/M")*($D$8:$D$57="XYZ"),1,0))}

But what I would really like to know is the number of "W/M"s
that
are "XYZ",
"ABC", "LMN", etc. I thought this would work, but it doesn't:
{=SUM(IF(ISNA(($F$8:$F$57="W/M")*($D$8:$D$57=OR("XYZ", "ABC",
"LMN",
"QRS"))),1,0))}

Any guidance is appreciated.

OR or ARRAY help
 

Try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(MOD(COLUMN($D$8:$L$57)-COLUMN($D$8),8)=0,IF(ISNUMBER(MATCH($D$8:$
L$57,{"ABC","LMN","QRS","XYZ"},0)),IF($F$8:$N$57=" W/M",1))))

or

=SUM(IF(MOD(COLUMN($D$8:$L$57)-COLUMN($D$8),8)=0,IF(ISNUMBER(MATCH($D$8:$
L$57,$P$2:$P$5,0)),IF($F$8:$N$57=$Q$2,1))))

....where P2:P5 contains ABC, LMN, QRS, and XYZ, and Q2 contains W/M.
The ranges can be adjusted to include other columns. Adjust them
accordingly.

Hope this helps!

In article ,
Bigfoot17 wrote:

This was extremely helpful, and it appears I was so confused I was making it
harder than it needed to be. However, I'd like to push the envelope a bit
further ...

I need to evaluate several ranges for the "W/M" and corresponding "XYZ" etc.
Would this be a matter of adding several SUMPRODUCTS together?
=SUMPRODUCT(($F$8:$F$57="W/M")*($D$8:$D$57={"XYZ","ABC","LMN","QRS"}))+SUMPRO D
UCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.