Expand SUMPRODUCTS Formula

[Mister H]

Hi All:

I have been suing a SUMPRODUCTS formula for a while but I now need to expand on it or get a new formula. I was using this:

=SUMPRODUCT(($A$17:$A$266=$C6)*($F$17:$F$266>0))

cell C6 contains the text AR

I now need to expand this formula to say if A17:A266 is equal to:

"AR" or "AR-DIV" or "AR-NSL" then I require the count.

I tried this nut no luck:

=SUMPRODUCT(($A$17:$A$266="AR","AR-DIV","AR-NSL")*($F$17:$F$266>0))</SPAN>

Am I close? Should I use a different formula. THANKS to anyone that can assist.

Take Care,
Mark

 

[Scott Huish]

Are these the only options that would start with AR?

 

[Marcelo Branco]

Maybe

=SUMPRODUCT((ISNUMBER(MATCH($A$17:$A$266,{"AR";"AR-DIV";"AR-NSL"},0)))*($F$17:$F$266>0))

M.

 

[Scott Huish]

If you need to specify those options:

=SUMPRODUCT(--((A17:A266="AR")+(A17:A266="AR-DIV")+(A17:A266="AR-NSL")>0),--(F17:F266>0))

or if you only need the options that start with AR:

=SUMPRODUCT(--(LEFT(A17:A266,2)="AR"),--(F17:F266>0))

 

[Mister H]

Hi HOTPEPPER, THAKS for your help.

These are the only options that currently start with AR.

NOTE: Once I get the formula I am hoping to alter the formula and use it elsewhere (the ER's if possible).

These are the current things that could appear in A17 to A266:

AR
AR-DIV
AR-NSL
REV
NPC
ER
H-ER

My only requirements at this time are to get a count on the ones that begin with AR as well I will need to get a count on the ones that contain ER. The counts will be in different cells.

THANKS Again,
Mark

Hopefully I can alter the formula you suggest.

 

[Mister H]

SORRY I type TOO SLOW...

I will play around with BOTH of your suggestions. THANKS Marcelo and HOTPEPPER.

Have a GREAT day,
Mark