OR or ARRAY help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi

Try the non-array entered formula

=SUMPRODUCT(($F$8:$F$57="W/M")*($C$8:$C$57={"XYZ","ABC","LMN","QRS"}))
 
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)
)
 
Sorry missed one of the conditions

=SUMPRODUCT(((F8:F57="W/M")*(D8:D57="ABC"))+(D8:D57="LMN")+(D8:D57="XYZ")+(D8:D57="QRS"))
 
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!
 
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"}))+SUMPRODUCT(($N$8:$N$57="W/M")*($L$8:$L$57={"XYZ","ABC","LMN","QRS"}))

Thanks for the help you have already been.
 
I get a "Formula Too Long" error since their are two many ranges. Any other
suggestions to make this work?
 
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
 
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!
 
Back
Top