Need to setup 3 different counters

[vicstone]

I need to setup 3 different counters:
1. Count all nonblank cells in range I2:I494 with values > 10/28/2009
2. Count all nonblank cells in range AA2:AA494 with values > 10/28/2009 and corresponding cell K = blank
3. Count all nonblank cells in range I2:CA494 with values > 4/30/2010
Thank you.

[vicstone]

I have figured out the first counter =COUNTIF(I10:I494,">10/28/2009")

[lazyme]

Your 3rd counter should be similar to the 1st counter. As for your 2nd counter, you can use an array forumla: (enter forumla below then press CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

*assuming your k values are in column AB and the corresponding value is in the same rows as AA

[vicstone]

K is the cell name and not a value. I have changed your formula to this =SUM((AA2:AA494>10/28/2009)*(K2:K494<>"")) but it did not work. I have tried both with just enter and with ctl+shft+ent. I have come up with another formula =SUMPRODUCT((AA2:AA494>DATEVALUE("10/28/2009"))*(K2:K494<>""))-1. I have to subtract 1 since the result if higher by 1 for some reason. Is there a way to fix it?

[6StringJazzer]

K is the cell name and not a value. I have changed your formula to this =SUM((AA2:AA494>10/28/2009)*(K2:K494<>"")) but it did not work. I have tried both with just enter and with ctl+shft+ent. I have come up with another formula =SUMPRODUCT((AA2:AA494>DATEVALUE("10/28/2009"))*(K2:K494<>""))-1. I have to subtract 1 since the result if higher by 1 for some reason. Is there a way to fix it?

I think in the first formula 10/28/2009 has to be in quotes otherwise Excel treats it as two divisions. I haven't tested it but that appears to be why your revision works.

As far as the -1 problem, I would like to see your data to see if there is an issue in the logic of the formula, some other problem with the formula, or an error with how you have counted (no offense but we have to consider it). Can you attach your workbook?