# Expand SUMPRODUCTS Formula

#### Mister H

##### Well-known Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Maybe

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

M.

If you need to specify those options:

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

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

Hi HOTPEPPER, THAKS for your help.

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.

SORRY I type TOO SLOW...

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

Have a GREAT day,
Mark

Replies
4
Views
456
Replies
1
Views
188
Replies
0
Views
111
Replies
0
Views
243
Replies
5
Views
490

### Forum statistics

1,203,728
Messages
6,057,021
Members
444,902
Latest member
ExerciseInFutility

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back