Hi All,
This is possibly a popular question, but I have a problem. I have a
list of data coming from an external data query. I want to use this
data:

1 A 2005/09/15 11:17:40 AM
1 B 2005/09/16 11:08:09 AM
2 A 2005/09/18 12:22:18 PM
1 A 2005/09/16 12:44:56 PM
1 B 2005/09/17 12:56:22 PM
2 A 2005/09/03 01:24:10 PM
3 C 2005/09/16 03:07:15 PM
2 A 2005/09/19 11:40:56 AM

And count the number of 1's, 2's and 3's there are for each date, but
only if column b is equal to A or B, to end up with something like the
following:

1 2 3
---------------------------------
3/09/2005 0 1 0
15/09/2005 1 0 0
16/09/2005 3 0 0
18/09/2005 0 1 0
19/09/2005 0 1 0

I hope this makes sense. Any help would be HUGELY appreciated

Regards
Rob Manger

Assuming the data is in A2:D20

In K2:Kn add the dates to test against
In L1:N1 put 1,2,3

In L2:
=SUMPRODUCT(--($A$2:$A$20=L$1)*($B$2:$B$20={"A","B"})*($C$2:$C$2 0=$K2))

copy down and across

BTW I get 2 0 0 not 3 0 0

And this is probably better served by a pivot table.

--
HTH

Bob Phillips

wrote in message
oups.com...
Hi All,
This is possibly a popular question, but I have a problem. I have a
list of data coming from an external data query. I want to use this
data:

1 A 2005/09/15 11:17:40 AM
1 B 2005/09/16 11:08:09 AM
2 A 2005/09/18 12:22:18 PM
1 A 2005/09/16 12:44:56 PM
1 B 2005/09/17 12:56:22 PM
2 A 2005/09/03 01:24:10 PM
3 C 2005/09/16 03:07:15 PM
2 A 2005/09/19 11:40:56 AM

And count the number of 1's, 2's and 3's there are for each date, but
only if column b is equal to A or B, to end up with something like the
following:

1 2 3
---------------------------------
3/09/2005 0 1 0
15/09/2005 1 0 0
16/09/2005 3 0 0
18/09/2005 0 1 0
19/09/2005 0 1 0

I hope this makes sense. Any help would be HUGELY appreciated

Regards
Rob Manger

Hi Leo and Bob,
Thanx both for your speedy replies, however there seems to still be an
issue. Both solutions offer the same data, but it the only figures I
am getting are a single '1' for the 15th and the 16th
Rob

Hi Rob

It may be a problem with the format of your date. If you have a date and
time, ie "2005/09/15 11:17:40 AM" is all in one cell, the sumproduct
will not find a match to a date eg "2005/09/15". You may have to add
another column where you convert the date/time values into just dates.
You could do this with a formula:
=DATE(YEAR(C1),MONTH(C1),DAY(C1))
copied down.
Then reference this new column in the sumproduct.

Hope this helps
Rowan

wrote:
Hi Leo and Bob,
Thanx both for your speedy replies, however there seems to still be an
issue. Both solutions offer the same data, but it the only figures I
am getting are a single '1' for the 15th and the 16th
Rob

skrev i en meddelelse
oups.com...
Hi All,
This is possibly a popular question, but I have a problem. I have a
list of data coming from an external data query. I want to use this
data:

1 A 2005/09/15 11:17:40 AM
1 B 2005/09/16 11:08:09 AM
2 A 2005/09/18 12:22:18 PM
1 A 2005/09/16 12:44:56 PM
1 B 2005/09/17 12:56:22 PM
2 A 2005/09/03 01:24:10 PM
3 C 2005/09/16 03:07:15 PM
2 A 2005/09/19 11:40:56 AM

And count the number of 1's, 2's and 3's there are for each date, but
only if column b is equal to A or B, to end up with something like the
following:

1 2 3
---------------------------------
3/09/2005 0 1 0
15/09/2005 1 0 0
16/09/2005 3 0 0
18/09/2005 0 1 0
19/09/2005 0 1 0

I hope this makes sense. Any help would be HUGELY appreciated

Regards
Rob Manger


Hi Rob

Here´s one way to do it.

Assuming your first list in A2:C9 and your second list in E1:H6
with headings 1, 2, 3 in F1:H1.

Enter this formula in F2:

=SUMPRODUCT(($C$2:$C$9=$E2)+0,(($B$2:$B$9="A")+($B $2:$B$9="B")),($A$2:$A$9=F$1)+0)

Copy the formula to F2:H2 with the fill handle (the little square in
the lower right corner of the cell).
Copy F2:H2 to F2:H6 with the fill handle.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hi Rob

You may want to try this...

Data Range A2:C9
1,2,3 in G1:I1
Date in F2:F7

In G2,

=SUMPRODUCT(--(DATE(YEAR($C$2:$C$9),MONTH($C$2:$C$9),DAY($C$2:$C $9))=$F2)*--($B$2:$B$9={"A","B"})*--($A$2:$A$9=G$1))

or

=SUMPRODUCT(--(ROUNDDOWN($C$2:$C$9,0)=$F2)*--($B$2:$B$9={"A","B"})*--($A$2:$A$9=G$1))

Drag across to I2 and drag down to I7

Sample file: http://www.savefile.com/files/9438237


==
wrote in message
oups.com...
Hi All,
This is possibly a popular question, but I have a problem. I have a
list of data coming from an external data query. I want to use this
data:

1 A 2005/09/15 11:17:40 AM
1 B 2005/09/16 11:08:09 AM
2 A 2005/09/18 12:22:18 PM
1 A 2005/09/16 12:44:56 PM
1 B 2005/09/17 12:56:22 PM
2 A 2005/09/03 01:24:10 PM
3 C 2005/09/16 03:07:15 PM
2 A 2005/09/19 11:40:56 AM

And count the number of 1's, 2's and 3's there are for each date, but
only if column b is equal to A or B, to end up with something like the
following:

1 2 3
---------------------------------
3/09/2005 0 1 0
15/09/2005 1 0 0
16/09/2005 3 0 0
18/09/2005 0 1 0
19/09/2005 0 1 0

I hope this makes sense. Any help would be HUGELY appreciated

Regards
Rob Manger

Yay!!
Thanx KK. I was having some issues on the second option you supplied
(due to the nature of the data I am using), but the first works like a
treat.
Many thanx to all involved :)
Rob