|
Even more Complex COUNTIF functionality
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 |
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 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 |
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 |
| All times are GMT +1. The time now is 08:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com