Forum Discussion
Formula needed for tricky criteria
I'd appreciate any help I can get with creating a formula (that I'm sure is WAY more simple than it seems). Here's the situation: I've got to create a monthly spreadsheet that will add a number to the total for that month, but the number added is based on the daily number entered.
For example: 1-5 would add "1" to the monthly total, 6-8 would add "2" to the monthly total, 9-11 would add "3" to the monthly total, and so on. So it the daily entries for February are "1, 4, 9, 2, 15, 1, 5", then the monthly total should be 13 (1=1, 4=1, 9=3, 2=1, 15=5, 1=1, and 5=1).
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.
4 Replies
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.
