| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum uniques values on multiple worksheets
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
Hi
Could you not just use =SUM(Sheet1:Sheet20!B:B)/2 -- Regards Roger Govier "Fin Fang Foom" wrote in message ups.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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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 - 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(INDE X(C3:C100,0))- ROW(A3)+1),B3:B100) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
On Feb 26, 7:28 am, "Fin Fang Foom" wrote:
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 - 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(INDE X(C3:C100,0))- ROW(A3)+1),B3:B100)- Hide quoted text - - Show quoted text - Bump! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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 "Fin Fang Foom" wrote in message oups.com... On Feb 26, 7:28 am, "Fin Fang Foom" wrote: 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 - 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(INDE X(C3:C100,0))- ROW(A3)+1),B3:B100)- Hide quoted text - - Show quoted text - Bump! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I accrue certain cell values across multiple worksheets? | Excel Discussion (Misc queries) | |||
| Adding values for multiple worksheets | Excel Discussion (Misc queries) | |||
| Average Values / Multiple Worksheets | Excel Worksheet Functions | |||
| Footer values on multiple worksheets | Excel Discussion (Misc queries) | |||
| How to sum values in multiple worksheets | Excel Worksheet Functions | |||
Hybrid Mode
