Summary count from different worksheets?

[andrew]

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?

 

[muddan madhu]

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")))

 

[andrew]

Hi, its not working as it returned a 0 (zero) instead of 3 based on example
table and formula given. Any ideas?

 

[muddan madhu]

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?

 

[andrew]

err..no. if you look carefully, the data in the respective rows are not the
same alphabets. See table below:

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

In row 1 for all Sheets, X only appears in Sheet 1, 3 and 4.
In row 3 for all Sheets, Z only appears in Sheet 1 and 4.