|
count on 3 criteria
I need to count data based on 3 criteria.. I have this working for 2
but now need a third.. ColA is just an identifier, ColB is the date some work was done, ColC is if it was completed. The results section is the count area. ColAA is the month/year to check, ColBB is the count of how many dates from range ColBB match Col4 (i.e. 5 dates fall in October 08). The function is the following for that count: =SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)). This works without any problem. I now need to do the same with the data in ColC. For example ColCC should have 3 for October (5 started in October but only 3 completed as marked by the letter P) take data from ColB check against ColAA if true then check ColC if P then count ... This is my data ColA ColB ColC A1 10/31/08 P A2 09/07/08 A3 10/31/08 A4 10/31/08 P A5 11/19/08 P A6 10/16/08 P A7 09/05/08 P A8 12/09/08 P A9 12/09/08 P A10 12/09/08 P A11 12/09/08 P A42 12/09/08 P A43 07/01/08 P A44 12/02/08 A45 08/02/08 A46 12/09/08 P A47 11/28/08 P A48 11/13/08 P A49 10/31/08 P Results: ColAA ColBB ColCC Month Done in month: P Result 7-08 1 XX 8-08 1 XX 9-08 2 XX 10-08 5 XX 11-08 3 XX 12-08 7 XX |
count on 3 criteria
1. there are 4 "P"s in OCt. 2008
2. one way: =SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C $1:$C$19="P"),1,)) array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array formula then copy down On 11 Gru, 19:54, Chris Salcedo wrote: I need to count data based on 3 criteria.. I have this working for 2 but now need a third.. ColA is just an identifier, ColB is the date some work was done, ColC is if it was completed. The results section is the count area. ColAA is the month/year to check, ColBB is the count of how many dates from range ColBB match Col4 (i.e. 5 dates fall in October 08). The function is the following for that count: =SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)). This works without any problem. I now need to do the same with the data in ColC. For example ColCC should have 3 for October (5 started in October but only 3 completed as marked by the letter P) take data from ColB check against ColAA if true then check ColC if P then count ... This is my data ColA * *ColB * * *ColC A1 * * *10/31/08 * * * *P A2 * * *09/07/08 A3 * * *10/31/08 A4 * * *10/31/08 * * * *P A5 * * *11/19/08 * * * *P A6 * * *10/16/08 * * * *P A7 * * *09/05/08 * * * *P A8 * * *12/09/08 * * * *P A9 * * *12/09/08 * * * *P A10 * * 12/09/08 * * * *P A11 * * 12/09/08 * * * *P A42 * * 12/09/08 * * * *P A43 * * 07/01/08 * * * *P A44 * * 12/02/08 A45 * * 08/02/08 A46 * * 12/09/08 * * * *P A47 * * 11/28/08 * * * *P A48 * * 11/13/08 * * * *P A49 * * 10/31/08 * * * *P Results: ColAA * * * * *ColBB * * * * * * * * *ColCC Month * *Done in month: P Result 7-08 * * * * * * * 1 * * * * * * * * * * * XX 8-08 * * * * * * * 1 * * * * * * * * * * * XX 9-08 * * * * * * * 2 * * * * * * * * * * * XX 10-08 * * * * * 5 * * * * * * * * * * * * *XX 11-08 * * * * * 3 * * * * * * * * * * * * *XX 12-08 * * * * * 7 * * * * * * * * * * * * *XX |
count on 3 criteria
On Dec 11, 11:18*am, Jarek Kujawa wrote:
1. there are 4 "P"s in OCt. 2008 2. one way: =SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C $1:$C$19="P"),1,)) array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array formula then copy down On 11 Gru, 19:54, Chris *Salcedo wrote: I need to count data based on 3 criteria.. I have this working for 2 but now need a third.. ColA is just an identifier, ColB is the date some work was done, ColC is if it was completed. The results section is the count area. ColAA is the month/year to check, ColBB is the count of how many dates from range ColBB match Col4 (i.e. 5 dates fall in October 08). The function is the following for that count: =SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)). This works without any problem. I now need to do the same with the data in ColC. For example ColCC should have 3 for October (5 started in October but only 3 completed as marked by the letter P) take data from ColB check against ColAA if true then check ColC if P then count ... This is my data ColA * *ColB * * *ColC A1 * * *10/31/08 * * * *P A2 * * *09/07/08 A3 * * *10/31/08 A4 * * *10/31/08 * * * *P A5 * * *11/19/08 * * * *P A6 * * *10/16/08 * * * *P A7 * * *09/05/08 * * * *P A8 * * *12/09/08 * * * *P A9 * * *12/09/08 * * * *P A10 * * 12/09/08 * * * *P A11 * * 12/09/08 * * * *P A42 * * 12/09/08 * * * *P A43 * * 07/01/08 * * * *P A44 * * 12/02/08 A45 * * 08/02/08 A46 * * 12/09/08 * * * *P A47 * * 11/28/08 * * * *P A48 * * 11/13/08 * * * *P A49 * * 10/31/08 * * * *P Results: ColAA * * * * *ColBB * * * * * * * * *ColCC Month * *Done in month: P Result 7-08 * * * * * * * 1 * * * * * * * * * * * XX 8-08 * * * * * * * 1 * * * * * * * * * * * XX 9-08 * * * * * * * 2 * * * * * * * * * * * XX 10-08 * * * * * 5 * * * * * * * * * * * * *XX 11-08 * * * * * 3 * * * * * * * * * * * * *XX 12-08 * * * * * 7 * * * * * * * * * * * * *XX Fantastic... Works great.. I assume that the logic is if year match =true (=1) * if month match =true (=1) * cell ="P" =true=1 =false=0 soooo 1*1*1=1 if all criteria is true or 0 if not then sum... Briliant..... Thanks |
count on 3 criteria
exactly...
pls click YES if my post was helpful ;-))) On 11 Gru, 22:55, Chris Salcedo wrote: On Dec 11, 11:18*am, Jarek Kujawa wrote: 1. there are 4 "P"s in OCt. 2008 2. one way: =SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C $1:$C$19="P"),1,)) array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array formula then copy down On 11 Gru, 19:54, Chris *Salcedo wrote: I need to count data based on 3 criteria.. I have this working for 2 but now need a third.. ColA is just an identifier, ColB is the date some work was done, ColC is if it was completed. The results section is the count area. ColAA is the month/year to check, ColBB is the count of how many dates from range ColBB match Col4 (i.e. 5 dates fall in October 08). The function is the following for that count: =SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)). This works without any problem. I now need to do the same with the data in ColC. For example ColCC should have 3 for October (5 started in October but only 3 completed as marked by the letter P) take data from ColB check against ColAA if true then check ColC if P then count ... This is my data ColA * *ColB * * *ColC A1 * * *10/31/08 * * * *P A2 * * *09/07/08 A3 * * *10/31/08 A4 * * *10/31/08 * * * *P A5 * * *11/19/08 * * * *P A6 * * *10/16/08 * * * *P A7 * * *09/05/08 * * * *P A8 * * *12/09/08 * * * *P A9 * * *12/09/08 * * * *P A10 * * 12/09/08 * * * *P A11 * * 12/09/08 * * * *P A42 * * 12/09/08 * * * *P A43 * * 07/01/08 * * * *P A44 * * 12/02/08 A45 * * 08/02/08 A46 * * 12/09/08 * * * *P A47 * * 11/28/08 * * * *P A48 * * 11/13/08 * * * *P A49 * * 10/31/08 * * * *P Results: ColAA * * * * *ColBB * * * * * * * * *ColCC Month * *Done in month: P Result 7-08 * * * * * * * 1 * * * * * * * * * * * XX 8-08 * * * * * * * 1 * * * * * * * * * * * XX 9-08 * * * * * * * 2 * * * * * * * * * * * XX 10-08 * * * * * 5 * * * * * * * * * * * * *XX 11-08 * * * * * 3 * * * * * * * * * * * * *XX 12-08 * * * * * 7 * * * * * * * * * * * * *XX Fantastic... Works great.. I assume that the logic is if year match =true (=1) ** if month match =true (=1) * cell ="P" =true=1 =false=0 soooo 1*1*1=1 if all criteria is true *or 0 if not then sum... Briliant..... Thanks- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| All times are GMT +1. The time now is 08:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com