count cells with multiple criteria and two rows
 

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:E 30,"CO007") +
count(if(worksehet2!B2:B30,"*d") etc. but that didn't work. Please help.

count cells with multiple criteria and two rows
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Erin" wrote in message
...
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:E 30,"CO007") +
count(if(worksehet2!B2:B30,"*d") etc. but that didn't work. Please help.

count cells with multiple criteria and two rows
 

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

-kc
*Click YES if this helps

"Erin" wrote:

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:E 30,"CO007") +
count(if(worksehet2!B2:B30,"*d") etc. but that didn't work. Please help.