software

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.

ABCD
1/3/200510102
1/3/2005101127
1/3/2005101240
1/3/200510511
1/3/200510527
1/3/200510615
1/3/200510624
1/3/200510711
1/3/200510725
1/3/200510815
1/3/200510827
1/3/200510916
1/3/2005109213
1/3/200511015
1/3/200511023
1/3/200511117
1/3/200511122

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.

I need to count column D only if A, B and C are met first.

G HIJK
Date 0123
Sunday, January 02, 20050
Monday, January 03, 20052
Tuesday, January 04, 20050
Wednesday, January 05, 20050For 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 HIJK
Date 0123
Sunday, January 02, 2005
Monday, January 03, 2005
Tuesday, January 04, 2005
Wednesday, January 05, 2005Thanks for the help.


Jim wrote:
gt; Hello,
gt;
gt; I am using a pivot table to bring in the data below from Access into Excel.
gt; The first column (A) is the date the second column (B) is the store number,
gt; the third column (C) is the prompt that was used on our phone system and the
gt; fourth column (D) is the number of times the prompt was used with the store
gt; listed on that line.
gt;
gt; ABCD
gt; 1/3/200510102
gt; 1/3/2005101127
gt; 1/3/2005101240
gt; 1/3/200510511
gt; 1/3/200510527
gt; 1/3/200510615
gt; 1/3/200510624
gt; 1/3/200510711
gt; 1/3/200510725
gt; 1/3/200510815
gt; 1/3/200510827
gt; 1/3/200510916
gt; 1/3/2005109213
gt; 1/3/200511015
gt; 1/3/200511023
gt; 1/3/200511117
gt; 1/3/200511122
gt;
gt; I have two requests: The first is a count/total of column D of all the
gt; times the prompt in column C (for example 1) was used for a specific date
gt; (1/3/2005 for this example).
gt;
gt; In other words, from the example above the 0 prompt (column C) appears 2
gt; times on the third. So in the sheet below the count (2 in this case) appears
gt; on the same row as the date.
gt;
gt; I need to count column D only if A, B and C are met first.
gt;
gt; G HIJK
gt; Date 0123
gt; Sunday, January 02, 20050
gt; Monday, January 03, 20052
gt; Tuesday, January 04, 20050
gt; Wednesday, January 05, 20050
gt;
gt;

What is the expected count on 1/3/05 for the prompt 1?

gt; For the next request I would like a total count of the number of times the
gt; “1” prompt was used on 1/3/05 if the “1” was used for store numbers 101, 107,
gt; and 109.
gt; G HIJK
gt; Date 0123
gt; Sunday, January 02, 2005
gt; Monday, January 03, 2005
gt; Tuesday, January 04, 2005
gt; Wednesday, January 05, 2005
gt;

Are you looking for a count of different stores with prompt 1 on 1/3/05?

quot;Jimquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I am using a pivot table to bring in the data below from Access into
Excel.
gt; The first column (A) is the date the second column (B) is the store
number,
gt; the third column (C) is the prompt that was used on our phone system and
the
gt; fourth column (D) is the number of times the prompt was used with the
store
gt; listed on that line.
gt;
gt; A B C D
gt; 1/3/2005 101 0 2
gt; 1/3/2005 101 1 27
gt; 1/3/2005 101 2 40
gt; 1/3/2005 105 1 1
gt; 1/3/2005 105 2 7
gt; 1/3/2005 106 1 5
gt; 1/3/2005 106 2 4
gt; 1/3/2005 107 1 1
gt; 1/3/2005 107 2 5
gt; 1/3/2005 108 1 5
gt; 1/3/2005 108 2 7
gt; 1/3/2005 109 1 6
gt; 1/3/2005 109 2 13
gt; 1/3/2005 110 1 5
gt; 1/3/2005 110 2 3
gt; 1/3/2005 111 1 7
gt; 1/3/2005 111 2 2
gt;
gt; I have two requests: The first is a count/total of column D of all the
gt; times the prompt in column C (for example 1) was used for a specific date
gt; (1/3/2005 for this example).
gt;
gt; In other words, from the example above the 0 prompt (column C) appears 2
gt; times on the third. So in the sheet below the count (2 in this case)
appears
gt; on the same row as the date.
gt;
gt; I need to count column D only if A, B and C are met first.
gt;
gt; G H I J K
gt; Date 0 1 2 3
gt; Sunday, January 02, 2005 0
gt; Monday, January 03, 2005 2
gt; Tuesday, January 04, 2005 0
gt; Wednesday, January 05, 2005 0

=SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)

and copy across and downgt; For the next request I would like a total count of the number of times the
gt; quot;1quot; prompt was used on 1/3/05 if the quot;1quot; was used for store numbers 101,
107,
gt; and 109.
gt; G H I J K
gt; Date 0 1 2 3
gt; Sunday, January 02, 2005
gt; Monday, January 03, 2005
gt; Tuesday, January 04, 2005
gt; Wednesday, January 05, 2005

=SUMPRODUCT(--($A$1:$A$1000=$G2),--(ISNUMBER(MATCH($B$1:$B$1000,{101,107,109
},0))),--($C$1:$C$1000=H$1),$D$1:$D$1000)
I am not receiving data from this formula. Can I get you a file to look at?

quot;Bob Phillipsquot; wrote:

gt; quot;Jimquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; I am using a pivot table to bring in the data below from Access into
gt; Excel.
gt; gt; The first column (A) is the date the second column (B) is the store
gt; number,
gt; gt; the third column (C) is the prompt that was used on our phone system and
gt; the
gt; gt; fourth column (D) is the number of times the prompt was used with the
gt; store
gt; gt; listed on that line.
gt; gt;
gt; gt; A B C D
gt; gt; 1/3/2005 101 0 2
gt; gt; 1/3/2005 101 1 27
gt; gt; 1/3/2005 101 2 40
gt; gt; 1/3/2005 105 1 1
gt; gt; 1/3/2005 105 2 7
gt; gt; 1/3/2005 106 1 5
gt; gt; 1/3/2005 106 2 4
gt; gt; 1/3/2005 107 1 1
gt; gt; 1/3/2005 107 2 5
gt; gt; 1/3/2005 108 1 5
gt; gt; 1/3/2005 108 2 7
gt; gt; 1/3/2005 109 1 6
gt; gt; 1/3/2005 109 2 13
gt; gt; 1/3/2005 110 1 5
gt; gt; 1/3/2005 110 2 3
gt; gt; 1/3/2005 111 1 7
gt; gt; 1/3/2005 111 2 2
gt; gt;
gt; gt; I have two requests: The first is a count/total of column D of all the
gt; gt; times the prompt in column C (for example 1) was used for a specific date
gt; gt; (1/3/2005 for this example).
gt; gt;
gt; gt; In other words, from the example above the 0 prompt (column C) appears 2
gt; gt; times on the third. So in the sheet below the count (2 in this case)
gt; appears
gt; gt; on the same row as the date.
gt; gt;
gt; gt; I need to count column D only if A, B and C are met first.
gt; gt;
gt; gt; G H I J K
gt; gt; Date 0 1 2 3
gt; gt; Sunday, January 02, 2005 0
gt; gt; Monday, January 03, 2005 2
gt; gt; Tuesday, January 04, 2005 0
gt; gt; Wednesday, January 05, 2005 0
gt;
gt; =SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)
gt;
gt; and copy across and down
gt;
gt;
gt; gt; For the next request I would like a total count of the number of times the
gt; gt; quot;1quot; prompt was used on 1/3/05 if the quot;1quot; was used for store numbers 101,
gt; 107,
gt; gt; and 109.
gt; gt; G H I J K
gt; gt; Date 0 1 2 3
gt; gt; Sunday, January 02, 2005
gt; gt; Monday, January 03, 2005
gt; gt; Tuesday, January 04, 2005
gt; gt; Wednesday, January 05, 2005
gt;
gt; =SUMPRODUCT(--($A$1:$A$1000=$G2),--(ISNUMBER(MATCH($B$1:$B$1000,{101,107,109
gt; },0))),--($C$1:$C$1000=H$1),$D$1:$D$1000)
gt;
gt;
gt;

Yeah sure.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Jimquot; gt; wrote in message
...
gt; I am not receiving data from this formula. Can I get you a file to look
at?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; quot;Jimquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hello,
gt; gt; gt;
gt; gt; gt; I am using a pivot table to bring in the data below from Access into
gt; gt; Excel.
gt; gt; gt; The first column (A) is the date the second column (B) is the store
gt; gt; number,
gt; gt; gt; the third column (C) is the prompt that was used on our phone system
and
gt; gt; the
gt; gt; gt; fourth column (D) is the number of times the prompt was used with the
gt; gt; store
gt; gt; gt; listed on that line.
gt; gt; gt;
gt; gt; gt; A B C D
gt; gt; gt; 1/3/2005 101 0 2
gt; gt; gt; 1/3/2005 101 1 27
gt; gt; gt; 1/3/2005 101 2 40
gt; gt; gt; 1/3/2005 105 1 1
gt; gt; gt; 1/3/2005 105 2 7
gt; gt; gt; 1/3/2005 106 1 5
gt; gt; gt; 1/3/2005 106 2 4
gt; gt; gt; 1/3/2005 107 1 1
gt; gt; gt; 1/3/2005 107 2 5
gt; gt; gt; 1/3/2005 108 1 5
gt; gt; gt; 1/3/2005 108 2 7
gt; gt; gt; 1/3/2005 109 1 6
gt; gt; gt; 1/3/2005 109 2 13
gt; gt; gt; 1/3/2005 110 1 5
gt; gt; gt; 1/3/2005 110 2 3
gt; gt; gt; 1/3/2005 111 1 7
gt; gt; gt; 1/3/2005 111 2 2
gt; gt; gt;
gt; gt; gt; I have two requests: The first is a count/total of column D of all
the
gt; gt; gt; times the prompt in column C (for example 1) was used for a specific
date
gt; gt; gt; (1/3/2005 for this example).
gt; gt; gt;
gt; gt; gt; In other words, from the example above the 0 prompt (column C) appears
2
gt; gt; gt; times on the third. So in the sheet below the count (2 in this case)
gt; gt; appears
gt; gt; gt; on the same row as the date.
gt; gt; gt;
gt; gt; gt; I need to count column D only if A, B and C are met first.
gt; gt; gt;
gt; gt; gt; G H I J K
gt; gt; gt; Date 0 1 2 3
gt; gt; gt; Sunday, January 02, 2005 0
gt; gt; gt; Monday, January 03, 2005 2
gt; gt; gt; Tuesday, January 04, 2005 0
gt; gt; gt; Wednesday, January 05, 2005 0
gt; gt;
gt; gt; =SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)
gt; gt;
gt; gt; and copy across and down
gt; gt;
gt; gt;
gt; gt; gt; For the next request I would like a total count of the number of times
the
gt; gt; gt; quot;1quot; prompt was used on 1/3/05 if the quot;1quot; was used for store numbers
101,
gt; gt; 107,
gt; gt; gt; and 109.
gt; gt; gt; G H I J K
gt; gt; gt; Date 0 1 2 3
gt; gt; gt; Sunday, January 02, 2005
gt; gt; gt; Monday, January 03, 2005
gt; gt; gt; Tuesday, January 04, 2005
gt; gt; gt; Wednesday, January 05, 2005
gt; gt;
gt; gt;
=SUMPRODUCT(--($A$1:$A$1000=$G2),--(ISNUMBER(MATCH($B$1:$B$1000,{101,107,109
gt; gt; },0))),--($C$1:$C$1000=H$1),$D$1:$D$1000)
gt; gt;
gt; gt;
gt; gt;