count cells with multiple criteria and two rows

[Erin]

I have a spreadsheet with 4 different worksheets. I basically want to sum the
information from the 4 worksheets on a separate worksheet. From worksheet1, I
want to count the cells that contain the letter "D" from B2:B30, but only
count those that have a "D" if "CO007" is in E2:30......same criteria from
worksheet2, 3 and 4. I started with
=count(if(worksheet1!B2:B30,"*d")+(worksheet1!E2:E30,"CO007") +
count(if(worksehet2!B2:B30,"*d") etc. but that didn't work. Please help.

 

[Don Guillett]

something like
=sumproduct((b2:b22="d")*(e2:e22="c0007"))

 

[KC]

=SUMPRODUCT(--(ISERROR(SEARCH("*D*",Sheet2!B2:B30))=FALSE),--(Sheet2!E2:E30="CO007"))

-kc
*Click YES if this helps