Sum uniques values on multiple worksheets

F

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&D3:D100,C3:C100&D3:D100,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
 
F

Fin Fang Foom

Hi

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

--
Regards

Roger Govier













- Show quoted text -
Click to expand...

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
 
F

Fin Fang Foom

Hi

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

--
Regards

Roger Govier













- Show quoted text -
Click to expand...

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
 
F

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 -
Click to expand...

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

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 -
Click to expand...

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

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

Fin Fang Foom

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

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

- Show quoted text -
Click to expand...


Bump!
 
R

Roger Govier

Hi

I'm sorry, but I'm not understanding what you are after.
I thought you wanted the total of the times, excluding the subtotals
already created on each page.
I can't see where your answers are coming from
 
F

Fin Fang Foom

Hi

I'm sorry, but I'm not understanding what you are after.
I thought you wanted the total of the times, excluding the subtotals
already created on each page.
I can't see where your answers are coming from

--
Regards

Roger Govier






- Show quoted text -
Click to expand...





Sorry Roger Govier I'm going to try to explain more carefully. Look at
the data below. Clock in cloumn A, Net in column B, STD column C,
Total column D. What I would like is to Sum unique values in column B
excluding the "Total". So using this formula lets break it down

Exclues "Total" in column A
=SUMPRODUCT(--(RIGHT(A3:A100,5)<>"Total"),--

Look for the unique values that coresponds each other of columns C & D
(MATCH(C3:C100&D3:D100,C3:C100&D3:D100,0)=ROW(INDEX(C3:C100,0))-

Sum those values in column B that will be "Net"
ROW(A3)+1),B3:B100)-

Here are the values that the formula should sum from those worksheets:

7.44
3.2
2.3
1.4

7.44
3.2
2.3
1.4

9.44
1.2
1.3
1.4


So here are the correct totals for each worksheet should be:

Worksheet (1) 14.34
Worksheet (2) 14.34
Worksheet (2) 13.34

Total should be 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
 
L

Lori

Easier with Longre's THREED function but try this array formula (CSE):

=SUM((COUNTIF(OFFSET(INDIRECT(Shts&"!a1"),ROW(3:99)-1,),"*Total")=0)
*(COUNTIF(OFFSET(INDIRECT(Shts&"!
c1"),,,ROW(3:99)),N(OFFSET(INDIRECT(Shts&"!c1"),ROW(3:99)-1,)))=1)
*(COUNTIF(OFFSET(INDIRECT(Shts&"!
d1"),,,ROW(3:99)),N(OFFSET(INDIRECT(Shts&"!d1"),ROW(3:99)-1,)))=1)
*N(OFFSET(INDIRECT(Shts&"!b1"),ROW(3:99)-1,)))

Where "Shts" is a horizontal array of sheets. You could also define
the name "Shts" to refer to "=GET.WORKBOOK(1)" for all sheets in
workbook.

Note: the first term in the sum checks for "total" in column A, the
second and third terms check uniqueness in columns C-D, the last
returns the corresponding value from column B.

(Remove any extra -s when pasting)
 
Top