kojak43
Active Member
- Joined
- Feb 23, 2002
- Messages
- 270
I am just guessing what to do here.
I have job entry worksheets. One is where I enter all the data. I also have a sheet that shows rudimentary "statistics" That is, how many jobs on the list, how many are active, how many are done, how long they have been on the list, etc.
Right now there is only two symbols representing status, A=active and D=done. That is the "R" column. The "E" column represents the date the job was placed on the list. What I want to do is count all the A's that fall within a certain time frame. What I am tring to determine is those active jobs that have been on the list for at least 30 days.
This is my first attempted formula:
=AND(COUNTIF(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTIF(DataEntry!R3:R2000,"<>D"))
It advises there are 1996 active jobs.
I know this is incorrect, as the model I am using only has 48 total rows. Of that 46 are "A"'s and 2 are "D"'s. So this counts the empty cells right?
This is my second attempted formula
=AND(COUNTA(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTA(DataEntry!R3:R2000,"<>D"))
It yields 49. As there are only 46 cells that are true to this formula, where did the extra 3 come from?
What am I doing wrong?
I have job entry worksheets. One is where I enter all the data. I also have a sheet that shows rudimentary "statistics" That is, how many jobs on the list, how many are active, how many are done, how long they have been on the list, etc.
Right now there is only two symbols representing status, A=active and D=done. That is the "R" column. The "E" column represents the date the job was placed on the list. What I want to do is count all the A's that fall within a certain time frame. What I am tring to determine is those active jobs that have been on the list for at least 30 days.
This is my first attempted formula:
=AND(COUNTIF(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTIF(DataEntry!R3:R2000,"<>D"))
It advises there are 1996 active jobs.
I know this is incorrect, as the model I am using only has 48 total rows. Of that 46 are "A"'s and 2 are "D"'s. So this counts the empty cells right?
This is my second attempted formula
=AND(COUNTA(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTA(DataEntry!R3:R2000,"<>D"))
It yields 49. As there are only 46 cells that are true to this formula, where did the extra 3 come from?
What am I doing wrong?