=SUMPRODUCT formula with three criteria help

[redspanna]

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'

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

any help appreciated

 

[redspanna]

After some playing around (and extensive www searching) I got this to work...

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

 

[FDibbins]

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

 

[AhoyNC]

=SUMPRODUCT(--(Schedule!B4:B250="BELL"),(Schedule!C4:C250="M")+(Schedule!C4:C250="M1"))

 

[Anand Sharma]

use below mentioned formula

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

here "+" work as or operator