Forum Discussion
Formula needed for tricky criteria
I hadn't taken empty cells into account. Change the formula to
=SUM(IF(E2:E32=0,0,IF(INT(E2:E32/3)<1,1,INT(E2:E32/3))))
and confirm it with Ctrl+Shift+Enter.
Let's say the daily entries are in E2:E32.
The monthly total is given by the following array formula confirmed with Ctrl+Shift+Enter:
=SUM(IF(INT(E2:E32/3)<1,1,INT(E2:E32/3)))
Thank you for your reply! Unfortunately, the formula you provided didn't work. For example: when I entered the formula in the "monthly totals" box, edited the cell ranges, then did ctrl+shift+enter, it would automatically give me a monthly total of 31, even if there weren't any entries in the cell range. And when I would enter data in the cell range, the monthly total wouldn't change unless the data entered was in the column for day 5.
On the plus side: any data entered in the Day 5 column would increase the monthly total accordingly (1-5 would make it 32, 6-8 would make it 33, etc.).
I hadn't taken empty cells into account. Change the formula to
=SUM(IF(E2:E32=0,0,IF(INT(E2:E32/3)<1,1,INT(E2:E32/3))))
and confirm it with Ctrl+Shift+Enter.
- Yes, that did the trick! Thank you, Hans!!!