Forum Discussion
Count odd or even cells that have a date value
Hello,
I would like to count the dates in a column but only in the odd or even cells.
More details (see also image) : it's a column having predicted and actual dates. Only the cells that have a completed actual date should be counted. However some dates can be NA or cancelled, so it's not possible to just count all "actual date" cells with content. In the image below the actuals reached is manually counted for (it's also a small example) but in a bigger file would be useful to have it counted once a date is added "under actual" for that specific task. Ideally the formula doesn't reference to "Actual" as in column B below since those labels are not always present.
I tried options with MOD(ROW) and COUNTIFS but didn't get it to work.
All help is really appreciated!
As variant
=SUMPRODUCT( ISEVEN(ROW($B$2:$B$13 ))*ISNUMBER($B$2:$B$13) ) =SUMPRODUCT( ISODD(ROW($B$2:$B$13 ))*ISNUMBER($B$2:$B$13) )
As variant
=SUMPRODUCT( ISEVEN(ROW($B$2:$B$13 ))*ISNUMBER($B$2:$B$13) ) =SUMPRODUCT( ISODD(ROW($B$2:$B$13 ))*ISNUMBER($B$2:$B$13) )- Brilliant! This works also for the full file in my reply above.
Thanks!Bourrinho , you are welcome
Hi rachel
that could work if the file was small like above. However I picked a simple example to not make my explanation sound to difficult and just search for the formula. The actual file looks like below. E.g. Students having to complete a certain task within a certain window based upon their start date. To count the number of subjects starting, I could easily do this by counting the cells that have a start date (COUNTA). For the "Tasks", I would like to have a count each time a completion date was added. So in the example below, I would like to have that Task 1 shows 2 completions on top as 2 completion dates were added for the participating students.
