SOLVED

Formula needed for tricky criteria

Copper Contributor

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).

4 Replies

@Spliced_Mainbrace 

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)))

@Hans Vogelaar 

 

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.). 

best response confirmed by Spliced_Mainbrace (Copper Contributor)
Solution

@Spliced_Mainbrace 

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!!!
1 best response

Accepted Solutions
best response confirmed by Spliced_Mainbrace (Copper Contributor)
Solution

@Spliced_Mainbrace 

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.

View solution in original post