Forum Discussion
need help with excel formula
Hi there. I am trying to move over info from one sheet to another using =sumif or =sumifs. This is for budgeting. I input the receipt into the first sheet and I want it to automatically add to the next sheet which breaks the expenses down into category, price, month etc. I was able to figure out this formula which works =SUMIF('Form Responses ''21'!E2:E125,"=Listing Expenses",'Form Responses ''21'!D2:D125) It is bringing over the $ amount that comes up when it sees Listing Expenses but now my issue is I need it to only bring over the info from a specific month. So for example - for this cell, I want only the $ amount from the Listing expenses in March from 3/1/21-3/31/21. I attached what my sheets look like. Thanks for the help.
Make sure that all the ranges that you refer to are from row 2 to row 125.
=SUMIFS('Form Responses ''21'!$D$2:$D$125, 'Form Responses ''21'!$E$2:$E$125, Q$1, 'Form Responses ''21'!$B$2:$B$125, ">="&$A2, 'Form Responses ''21'!$B$2:$B$125, "<="&EOMONTH($A2, 0))
4 Replies
In A2 on the Totals sheet, enter the date 01/01/2021, and in A3, enter 02/01/2021.
Apply the custom format mmmm to these cells, so that they display the full month name.
Select A2:A3, then fill down to A13.
Enter the following formula in Q2:
=SUMIFS('Form Responses ''21'!$D$2:$D$125,' Form Responses ''21'!$E$2:$E$125, Q$1, 'Form Responses ''21'!$B$2:$B$125, ">="&$A2, 'Form Responses ''21'!$B$2:$B$125, "<="&EOMONTH($A2, 0))
This can be filled down, but also to the left and right.
- Thank you for replying and helping. I was able to change the information on the totals sheet as you instructed... but when I inputted the formula in Q2 I received this - #VALUE! Array arguments to SUMIFS are of different size.
Make sure that all the ranges that you refer to are from row 2 to row 125.
=SUMIFS('Form Responses ''21'!$D$2:$D$125, 'Form Responses ''21'!$E$2:$E$125, Q$1, 'Form Responses ''21'!$B$2:$B$125, ">="&$A2, 'Form Responses ''21'!$B$2:$B$125, "<="&EOMONTH($A2, 0))
.png){kind=link}
.png){kind=link}