I have an overview worksheet which requires some calculation done from 10
worksheets (which are 'sandwiched' between a dummy Start and End sheet).

Within each of the 10 w'sheets, it comprises the following table:
A B
1 X
2 Y
3 Z
4 Y
5 X

I have an Overview worksheet (placed in front of all the w'sheets) which has
a cell that calculates the following:
1) check column A for the number 1 in the 10 w'sheets,
2) if match, check the same row and count how many times "X" appeared in the
same row in all w'sheets.

I tried using this formula but didn't work (value returned as #NAME?):
=SUM((Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

Anyone can help?

I thinnk you want sumproduct instead of sum

=SUMPRODUCT(--(Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

The -- is need to change the TRUE/False to 1's and 0's

I like to use a comma instead of the *
=SUMPRODUCT(--(Start:End!A1:A10=1),--(Start:End!B1:B10="X"))

"andrew" wrote:

I have an overview worksheet which requires some calculation done from 10
worksheets (which are 'sandwiched' between a dummy Start and End sheet).

Within each of the 10 w'sheets, it comprises the following table:
A B
1 X
2 Y
3 Z
4 Y
5 X

I have an Overview worksheet (placed in front of all the w'sheets) which has
a cell that calculates the following:
1) check column A for the number 1 in the 10 w'sheets,
2) if match, check the same row and count how many times "X" appeared in the
same row in all w'sheets.

I tried using this formula but didn't work (value returned as #NAME?):
=SUM((Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

Anyone can help?

I'm still getting the error #NAME?.

When i checked, i realised that the cells in column B is with formula [
=IF(T7="","",IF((T7-V7)+S70,"X",IF((T7-V7)+S7=0,"Y",IF((T7-V7)+S7<0,"Z"))))
]. Within each of the 10 worksheets, the cells in column B will display
either X, Y or Z as a result. How do i 'grab' the result to place it in the
overview worksheet to check based on the below SUMPRODUCT formula??

I'm really going nuts trying to figure this out. Help pls!


"Joel" wrote:

I thinnk you want sumproduct instead of sum

=SUMPRODUCT(--(Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

The -- is need to change the TRUE/False to 1's and 0's

I like to use a comma instead of the *
=SUMPRODUCT(--(Start:End!A1:A10=1),--(Start:End!B1:B10="X"))

"andrew" wrote:

I have an overview worksheet which requires some calculation done from 10
worksheets (which are 'sandwiched' between a dummy Start and End sheet).

Within each of the 10 w'sheets, it comprises the following table:
A B
1 X
2 Y
3 Z
4 Y
5 X

I have an Overview worksheet (placed in front of all the w'sheets) which has
a cell that calculates the following:
1) check column A for the number 1 in the 10 w'sheets,
2) if match, check the same row and count how many times "X" appeared in the
same row in all w'sheets.

I tried using this formula but didn't work (value returned as #NAME?):
=SUM((Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

Anyone can help?

I would put the equivalent formula in a cell on each sheet (say x1)--but in the
same cell.

=SUMproduct(--(A1:A10=1),--(B1:B10="X"))

Then I'd just use:
=sum(start:end(x1))
to get the total sum.

andrew wrote:

I have an overview worksheet which requires some calculation done from 10
worksheets (which are 'sandwiched' between a dummy Start and End sheet).

Within each of the 10 w'sheets, it comprises the following table:
A B
1 X
2 Y
3 Z
4 Y
5 X

I have an Overview worksheet (placed in front of all the w'sheets) which has
a cell that calculates the following:
1) check column A for the number 1 in the 10 w'sheets,
2) if match, check the same row and count how many times "X" appeared in the
same row in all w'sheets.

I tried using this formula but didn't work (value returned as #NAME?):
=SUM((Start:End!A1:A10=1)*(Start:End!B1:B10="X"))

Anyone can help?

--

Dave Peterson