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



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?

[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" 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?

[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?




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 -

[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.



"muddan madhu" wrote:

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 -