PDA

View Full Version : Solved: Counting based on 2 criteria



itipu
11-03-2009, 07:28 AM
Seemingly a very easy matter, but I can't seem to be able to resolve it...



All I need is to have a count of items that are "Column A - Target Date" 1 month overdue (today()-31) and have "Column B - Closed" as No....



I attached the spreadsheet to make it clear....



I tried a few things... closest I think is using SUM...



=SUM(IF(Tracker!A2:A10000<=TODAY(),0,1))*BLABLABLA



Trouble is.. if I specify range as A2:A10000 it counts all the blank cells and only works for greater than today (or today -31).. it does not work for less than as it assigns 1 to each blank cell to0 and of course.. A:A does not work either...

xld
11-03-2009, 10:09 AM
Try

=SUMPRODUCT(--(Tracker!C2:C1000="Yes"),--(Tracker!A2:A1000<=TODAY()-31))

itipu
11-04-2009, 01:12 AM
Thank You!!!