sumproduct help

[Jim]

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.

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

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.

[Aladin Akyurek]

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.
>
> I need to count column D only if A, B and C are met first.
>
> 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
>
>

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

> 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
>

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

[Bob Phillips]

"Jim" <Jim@discussions.microsoft.com> wrote in message
news:63DD2012-0A8A-40FA-BDAF-AE17744B991C@microsoft.com...
> 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.
>
> I need to count column D only if A, B and C are met first.
>
> 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

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

and copy across and down


> 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

=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)

[Jim]

I am not receiving data from this formula. Can I get you a file to look at?

"Bob Phillips" wrote:

> "Jim" <Jim@discussions.microsoft.com> wrote in message
> news:63DD2012-0A8A-40FA-BDAF-AE17744B991C@microsoft.com...
> > 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.
> >
> > I need to count column D only if A, B and C are met first.
> >
> > 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
>
> =SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)
>
> and copy across and down
>
>
> > 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
>
> =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)
>
>
>

[Bob Phillips]

Yeah sure.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jim" <Jim@discussions.microsoft.com> wrote in message
news:E24C1D7F-FCA6-423D-90A8-1F5EE2C931EB@microsoft.com...
> I am not receiving data from this formula. Can I get you a file to look
at?
>
> "Bob Phillips" wrote:
>
> > "Jim" <Jim@discussions.microsoft.com> wrote in message
> > news:63DD2012-0A8A-40FA-BDAF-AE17744B991C@microsoft.com...
> > > 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.
> > >
> > > I need to count column D only if A, B and C are met first.
> > >
> > > 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
> >
> > =SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)
> >
> > and copy across and down
> >
> >
> > > 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
> >
> >
=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)
> >
> >
> >

[Bob Phillips]

Yeah sure.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jim" <Jim@discussions.microsoft.com> wrote in message
news:E24C1D7F-FCA6-423D-90A8-1F5EE2C931EB@microsoft.com...
> I am not receiving data from this formula. Can I get you a file to look
at?
>
> "Bob Phillips" wrote:
>
> > "Jim" <Jim@discussions.microsoft.com> wrote in message
> > news:63DD2012-0A8A-40FA-BDAF-AE17744B991C@microsoft.com...
> > > 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.
> > >
> > > I need to count column D only if A, B and C are met first.
> > >
> > > 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
> >
> > =SUMPRODUCT(--($A$1:$A$1000=$G2),--($C$1:$C$1000=H$1),$D$1:$D$1000)
> >
> > and copy across and down
> >
> >
> > > 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
> >
> >
=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)
> >
> >
> >