Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif or sumproduct
Hello,
I am using a pivot table to bring in the data below from Access into Excel. The first column (A) is the date the second column (B) is the store number, the third column (C) is the prompt that was used on our phone system and the fourth column (D) is the number of times the prompt was used with the store listed on that line. A B C D 1/3/2005 101 0 2 1/3/2005 101 1 27 1/3/2005 101 2 40 1/3/2005 105 1 1 1/3/2005 105 2 7 1/3/2005 106 1 5 1/3/2005 106 2 4 1/3/2005 107 1 1 1/3/2005 107 2 5 1/3/2005 108 1 5 1/3/2005 108 2 7 1/3/2005 109 1 6 1/3/2005 109 2 13 1/3/2005 110 1 5 1/3/2005 110 2 3 1/3/2005 111 1 7 1/3/2005 111 2 2 I have two requests: The first is a count/total of column D of all the times the prompt in column C (for example 1) was used for a specific date (1/3/2005 for this example). In other words, from the example above the 0 prompt (column C) appears 2 times on the third. So in the sheet below the count (2 in this case) appears on the same row as the date. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 0 Monday, January 03, 2005 2 Tuesday, January 04, 2005 0 Wednesday, January 05, 2005 0 For the next request I would like a total count of the number of times the 1 prompt was used on 1/3/05 if the 1 was used for store numbers 101, 107, and 109. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 Monday, January 03, 2005 Tuesday, January 04, 2005 Wednesday, January 05, 2005 Thanks for the help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif or sumproduct
For the first...
H2, copied down and across: =SUMPRODUCT(--($A$1:$A$17=$G2),--($C$1:$C$17=H$1)) For the second, you can use the following formula... =SUMPRODUCT(--($A$1:$A$17=$G2),--($B$1:$B$17=101),--($C$1:$C$17=H$1)) Hope this helps! In article , Jim wrote: Hello, I am using a pivot table to bring in the data below from Access into Excel. The first column (A) is the date the second column (B) is the store number, the third column (C) is the prompt that was used on our phone system and the fourth column (D) is the number of times the prompt was used with the store listed on that line. A B C D 1/3/2005 101 0 2 1/3/2005 101 1 27 1/3/2005 101 2 40 1/3/2005 105 1 1 1/3/2005 105 2 7 1/3/2005 106 1 5 1/3/2005 106 2 4 1/3/2005 107 1 1 1/3/2005 107 2 5 1/3/2005 108 1 5 1/3/2005 108 2 7 1/3/2005 109 1 6 1/3/2005 109 2 13 1/3/2005 110 1 5 1/3/2005 110 2 3 1/3/2005 111 1 7 1/3/2005 111 2 2 I have two requests: The first is a count/total of column D of all the times the prompt in column C (for example 1) was used for a specific date (1/3/2005 for this example). In other words, from the example above the 0 prompt (column C) appears 2 times on the third. So in the sheet below the count (2 in this case) appears on the same row as the date. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 0 Monday, January 03, 2005 2 Tuesday, January 04, 2005 0 Wednesday, January 05, 2005 0 For the next request I would like a total count of the number of times the 1 prompt was used on 1/3/05 if the 1 was used for store numbers 101, 107, and 109. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 Monday, January 03, 2005 Tuesday, January 04, 2005 Wednesday, January 05, 2005 Thanks for the help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif or sumproduct
Neither of your solutions actually address my questions completely. I am
going to repost the question lets let someone else have a try - thanks for the attempt. "Domenic" wrote: For the first... H2, copied down and across: =SUMPRODUCT(--($A$1:$A$17=$G2),--($C$1:$C$17=H$1)) For the second, you can use the following formula... =SUMPRODUCT(--($A$1:$A$17=$G2),--($B$1:$B$17=101),--($C$1:$C$17=H$1)) Hope this helps! In article , Jim wrote: Hello, I am using a pivot table to bring in the data below from Access into Excel. The first column (A) is the date the second column (B) is the store number, the third column (C) is the prompt that was used on our phone system and the fourth column (D) is the number of times the prompt was used with the store listed on that line. A B C D 1/3/2005 101 0 2 1/3/2005 101 1 27 1/3/2005 101 2 40 1/3/2005 105 1 1 1/3/2005 105 2 7 1/3/2005 106 1 5 1/3/2005 106 2 4 1/3/2005 107 1 1 1/3/2005 107 2 5 1/3/2005 108 1 5 1/3/2005 108 2 7 1/3/2005 109 1 6 1/3/2005 109 2 13 1/3/2005 110 1 5 1/3/2005 110 2 3 1/3/2005 111 1 7 1/3/2005 111 2 2 I have two requests: The first is a count/total of column D of all the times the prompt in column C (for example 1) was used for a specific date (1/3/2005 for this example). In other words, from the example above the 0 prompt (column C) appears 2 times on the third. So in the sheet below the count (2 in this case) appears on the same row as the date. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 0 Monday, January 03, 2005 2 Tuesday, January 04, 2005 0 Wednesday, January 05, 2005 0 For the next request I would like a total count of the number of times the รขฌล1รขฌย prompt was used on 1/3/05 if the รขฌล1รขฌย was used for store numbers 101, 107, and 109. G H I J K Date 0 1 2 3 Sunday, January 02, 2005 Monday, January 03, 2005 Tuesday, January 04, 2005 Wednesday, January 05, 2005 Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions | |||
Sumif or Sumproduct | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions |