Even more Complex COUNTIF functionality

  • Thread starter Thread starter rob.manger
  • Start date Start date
R

rob.manger

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
(e-mail address removed)
 
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.
 
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
(e-mail address removed)
Click to expand...
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.
 
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
 
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


==
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
(e-mail address removed)
 
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
 
Back
Top