# =SUMPRODUCT formula with three criteria help

#### redspanna

##### Well-known Member
Hi all

I would like a formula that will check thru range B4:B250 of the 'schedule' sheet for a match to the word "BELL", then count the occasions "M" and "M1" appear thru range C4:C250

I have a SUMPRODUCT formula that works for one of the criteria but unsure how to get it to count both 'M' and 'M1'

Code:
``=SUMPRODUCT((Schedule!B4:B250="BELL")*(Schedule!C4:C250="M"))``

any help appreciated

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### redspanna

##### Well-known Member
After some playing around (and extensive www searching) I got this to work...

Code:
``=SUM(COUNTIFS(Schedule!C4:C250,{"M1","M"},Schedule!B4:B250,"Bell"))``

#### FDibbins

##### Well-known Member
Will there be other "M"'s? eg M2, M3 etc, or just M and M1?
If only M and M1....
=countifs(Schedule!B4:B250,"BELL",Schedule!C4:C250,"M?")

If more than just M1...
=SUM(COUNTIFs(Schedule!B4:B250,"BELL",Schedule!C4:C250,{"M", "M1"})

edit: cool, you got what you needed - almost the same as my suggestion

Last edited:

#### AhoyNC

##### Well-known Member
Code:
``=SUMPRODUCT(--(Schedule!B4:B250="BELL"),(Schedule!C4:C250="M")+(Schedule!C4:C250="M1"))``

#### Anand Sharma

##### Board Regular
use below mentioned formula

=SUMPRODUCT(--(J5:J10="apple"),--(K5:K10="m")+(K5:K10="m1"))

here "+" work as or operator

Replies
0
Views
481
Replies
4
Views
218
Replies
10
Views
200
Replies
1
Views
2K
Replies
5
Views
483

1,191,203
Messages
5,985,248
Members
439,953
Latest member
suchitha

### 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