Its working fine for me....

ur data look like this in,
Sheet 1
Col A Col B
1 X
2 Y
3 Y

similarlarly for sheet2, sheet3 and sheet4. Is I am right?




On Jun 13, 11:45*am, andrew wrote:
Hi, its not working as it returned a 0 (zero) instead of 3 based on example
table and formula given. Any ideas?



"muddan madhu" wrote:
try this one
=SUMPRODUCT(--((Sheet1!A2:A4=1)*(Sheet1!B2:B4="X")))+SUMPRODUCT(--
((Sheet2!A2:A4=1)*(Sheet2!B2:B4="X")))+SUMPRODUCT(--((Sheet3!
A2:A4=1)*(Sheet3!B2:B4="X")))+SUMPRODUCT(--((Sheet4!A2:A4=1)*(Sheet4!
B2:B4="X")))

On Jun 13, 8:04 am, andrew wrote:
I have 4 worksheets with the following tables respectively:

Sheet1 *Sheet2 *Sheet3 *Sheet4
A B * * * A B * * * *A B * * * A B
1 X * * * 1 Y * * * *1 X * * * 1 X
2 Y * * * 2 Y * * * *2 Z * * * 2 Y
3 Z * * * 3 X * * * *3 Y * * * 3 Z

I'm trying to create a separate summary sheet where the formula cell will
calculate the following from the 4 worksheets:
1) checks if the column A has "1", then check if column B has "X" in same
row of worksheet;
2) if both criteria are met, it counts as 1 in the formula cell.
3) This step will be repeated for every worksheet.

In the case of the above example worksheets, the formula cell will show a
total of 3 (X appearing 3 times for row 1 where A=1). To elaborate further,
if i now want to count how many "Z" appeared in same row with "3" in the
column A - the result would be 2 per example worksheets above.

Can this be done?- Hide quoted text -

- Show quoted text -