| 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! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
On Feb 26, 10:06 am, "Roger Govier"
wrote: 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 ooglegroups.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!- Hide quoted text - - Show quoted text - 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(INDE X(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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum uniques values on multiple worksheets
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"),RO W(3:99)-1,)))=1) *(COUNTIF(OFFSET(INDIRECT(Shts&"! d1"),,,ROW(3:99)),N(OFFSET(INDIRECT(Shts&"!d1"),RO W(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) On Feb 26, 6:47 pm, "Fin Fang Foom" wrote: On Feb 26, 10:06 am, "Roger Govier" wrote: 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 ooglegroups.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!- Hide quoted text - - Show quoted text - 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(INDE X(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 |
| 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 | |||
Linear Mode
