Use the formula SUMPRODUCT(--(DATE(YEAR($B$2:$B$19),MONTH($B$2:$B$19),1)=AA2)*(($cc$2:$cc$19)="P")).
On Fri, Dec 12, 2008 at 4:13 PM, lohith <lohithsri...@gmail.com> wrote: > > Hi Chris, > > Try this array Formula and let me know if it work out for you.. > > =SUM((MONTH($B$1:$B$19)=MONTH(AA1)) * ($C$1:$C$19="P")) > > Remember to add this formula using Ctrl+Shift+Enter(Array Formula). > > Cheers, > Lohith > > > On Dec 12, 2:33 am, Chris Salcedo <csalc...@gmail.com> 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 > > ColBB (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 > > > > --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
