Even more Complex COUNTIF functionality

[[email protected]]

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
[email protected]

[Bob Phillips]

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$20=$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

<[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
>

[Leo Heuser]

<[email protected]> skrev i en meddelelse
news:[email protected]...
> 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
> [email protected]
>
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.

[[email protected]]

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

[Rowan]

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

[email protected] 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
>

[kk]

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


==
<[email protected]> wrote in message
news:[email protected]...
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
[email protected]

[[email protected]]

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