Forum Discussion
Solved
Help with a formula
I'm using Excel to track hours, costs and payments. I'm not very knowledgeable in excel and I'm doing the best I can.
In F2 to get the cost per day I've used =IF(ISBLANK(C2),"",(C2*E2)+D2)
In G2 for weekly cost I've used =IF(WEEKDAY(A2)=7, IF(SUMIFS(F:F,A:A, ">="&A2-6,A:A, "<="&A2)>0, SUMIFS(F:F,A:A, ">="&A2-6,A:A, "<="&A2), ""), "")
Column H is where I want to get a total for each month with the value showing only on the last day of that month, (somewhat similar to what I have for G2?) Each month is a different length and I have no idea how to do this. I'd like all other days of the month to show no value, only the total on the last day of each month.
I would greatly appreciate any assistance. Thanks in advance.
The formulas I've used in F2 and G2 work. However, if there is a less cumbersome way of doing it that would be cool too.
Thanks again.
Select File > Options > Formulas.
Scroll down to the section 'Error checking rules'.
Clear the check box 'Formulas which omit cells in a region'.
Click OK.
6 Replies
Thank you so much, I'm grateful for your knowledge. All of the formulas work in the way I want to display the data. In both G2 and H2 am getting an error "formula omits adjacent cells". When I choose to 'ignore error" it does remove the error, however I am not able to pull this down through the cells in the column. I have to go to each cell individually to ignore the error. Is there a way to fix this?
Select File > Options > Formulas.
Scroll down to the section 'Error checking rules'.
Clear the check box 'Formulas which omit cells in a region'.
Click OK.
This worked perfectly. Thank you so much!!!
Another approach would be to use a Pivot Table to get monthly totals.
When you suggested a pivot table I explored using one, but my sheet has many columns and it would make it more difficult to evaluate the data. I wanted to be able to review the data for the month with the total right there instead of having to check two tables. Thank you for the suggestion
If you're OK with zeros on days/weeks without hours, you could use
=C2*E2+D2
in F2, and
=IF(WEEKDAY(A2)=7, SUMIFS(F$2:F2, A$2:A2, ">"&A2-7, A$2:A2, "<="&A2), "")
in G2.
In H2:
=IF(A2=EOMONTH(A2, 0), SUMIFS(F$2:F2, A$2:A2, ">"&EOMONTH(A2, -1), A$2:A2, "<="&A2), "")
