Sum uniques values on multiple worksheets

[Fin Fang Foom]

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&D3100,C3:C100&D3100,0)=ROW(INDEX(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

 

[Roger Govier]

Hi

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

 

[Fin Fang Foom]

Hi

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

--
Regards

Roger Govier













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


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

 

[Fin Fang Foom]

Hi

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

--
Regards

Roger Govier













- 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

 

[Fin Fang Foom]

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&D3100,C3:C100&D3100,0)=ROW(INDEX(C3:C100,0))-
ROW(A3)+1),B3:B100)

 

[Fin Fang Foom]

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 -

Can we modify this formula below to work across multiple worksheets?

=SUMPRODUCT(--(RIGHT(A3:A100,5)<>"Total"),--
(MATCH(C3:C100&D3100,C3:C100&D3100,0)=ROW(INDEX(C3:C100,0))-
ROW(A3)+1),B3:B100)

 

[Fin Fang Foom]

Can we modify this formula below to work across multiple worksheets?

=SUMPRODUCT(--(RIGHT(A3:A100,5)<>"Total"),--
(MATCH(C3:C100&D3100,C3:C100&D3100,0)=ROW(INDEX(C3:C100,0))-
ROW(A3)+1),B3:B100)- Hide quoted text -

- Show quoted text -

Bump!