On Feb 26, 6:56 am, "Fin Fang Foom" wrote:
On Feb 26, 6:33 am, "Roger Govier"
wrote:





Hi

Could you not just use
=SUM(Sheet1:Sheet20!B:B)/2

--
Regards

Roger Govier

"Fin Fang Foom" wrote in oglegroups.com...

Hi everyone,

I have tough one here. I would like a formula to Sum uniques values
across multiple worksheets. If you notice both worksheets has the same
data and thats fine because thoses are 2 different days of the week. I
would like to sum the Net hours in column B on both worksheets just
the unique values. The correct total should be 28.68. Here is a small
example of my layout and formula below.

=SUMPRODUCT(--(RIGHT(A3:A100,5)<"Total"),--
(MATCH(C3:C100&D3:D100,C3:C100&D3:D100,0)=ROW(INDE X(C3:C100,0))-
ROW(A3)+1),B3:B100)

Worksheet (1)

Net STD Total
Clock Hours Pieces Bonus
75 7.44 79.83 2.12
75 Total 7.44 2.12

14 3.2 86.66 0.14
14 2.3 49.40 1.23
14 1.4 30.07 0.07
14 Total 6.9 1.45

52 7.44 79.83 2.12
52 Total 7.44 2.12

Worksheet (2)

Net STD Total
Clock Hours Pieces Bonus
75 7.44 79.83 2.12
75 Total 7.44 2.12

14 3.2 86.66 0.14
14 2.3 49.40 1.23
14 1.4 30.07 0.07
14 Total 6.9 1.45

52 7.44 79.83 2.12
52 Total 7.44 2.12- Hide quoted text -

- Show quoted text -

Hi Roger Govier

Thank You so much for replying.

I tried your suggestion and I get 43.56. The correct total should be
28.68. The data I displayed is only a sample its not always going to
have the same data. Worksheet 3 could have have different vaules.
Example: If there is a worksheet 3 then the correct total is 42.02.

Worksheet (1)

Clock Net STD Total
75 7.44 79.83 2.12
Total 7.44 2.12

14 3.2 86.66 0.14
14 2.3 49.40 1.23
14 1.4 30.07 0.07
Total 6.9 1.45

52 7.44 79.83 2.12
Total 7.44 2.12

Worksheet (2)

Clock Net STD Total
75 7.44 79.83 2.12
Total 7.44 2.12

14 3.2 86.66 0.14
14 2.3 49.40 1.23
14 1.4 30.07 0.07
Total 6.9 1.45

52 7.44 79.83 2.12
Total 7.44 2.12

Worksheet (3)

Clock Net STD Total
75 9.44 59.83 1.12
Total 9.44 1.12

14 1.2 76.66 0.10
14 1.3 19.40 1.03
14 1.4 20.07 0.01
Total 14.9 1.14

52 9.44 59.83 1.12
Total 9.44 1.12- Hide quoted text -

- Show quoted text -

This is the formula I'm currently using to unique values across
multiple worksheets.


=SUMPRODUCT(--(RIGHT(A3:A100,5)<"Total"),--
(MATCH(C3:C100&D3:D100,C3:C100&D3:D100,0)=ROW(INDE X(C3:C100,0))-
ROW(A3)+1),B3:B100)