SOLVED

need help with excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2338206%22%20slang%3D%22en-US%22%3Eneed%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338206%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%20I%20am%20trying%20to%20move%20over%20info%20from%20one%20sheet%20to%20another%20using%20%3Dsumif%20or%20%3Dsumifs.%20This%20is%20for%20budgeting.%20I%20input%20the%20receipt%20into%20the%20first%20sheet%20and%20I%20want%20it%20to%20automatically%20add%20to%20the%20next%20sheet%20which%20breaks%20the%20expenses%20down%20into%20category%2C%20price%2C%20month%20etc.%20I%20was%20able%20to%20figure%20out%20this%20formula%20which%20works%26nbsp%3B%3CSTRONG%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESUMIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E'Form%20Responses%20''21'!E2%3AE125%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22%3DListing%20Expenses%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E'Form%20Responses%20''21'!D2%3AD125%3C%2FSTRONG%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3CSTRONG%3E)%3C%2FSTRONG%3E%20It%20is%20bringing%20over%20the%20%24%20amount%20that%20comes%20up%20when%20it%20sees%20Listing%20Expenses%20but%20now%20my%20issue%20is%20I%20need%20it%20to%20only%20bring%20over%20the%20info%20from%20a%20specific%20month.%20So%20for%20example%20-%20for%20this%20cell%2C%20I%20want%20only%20the%20%24%20amount%20from%20the%20Listing%20expenses%20in%20March%20from%203%2F1%2F21-3%2F31%2F21.%20I%20attached%20what%20my%20sheets%20look%20like.%20Thanks%20for%20the%20help.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2338206%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338228%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1048738%22%20target%3D%22_blank%22%3E%40Lindsmae%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20A2%20on%20the%20Totals%20sheet%2C%20enter%20the%20date%2001%2F01%2F2021%2C%20and%20in%20A3%2C%20enter%2002%2F01%2F2021.%3C%2FP%3E%0A%3CP%3EApply%20the%20custom%20format%20mmmm%20to%20these%20cells%2C%20so%20that%20they%20display%20the%20full%20month%20name.%3C%2FP%3E%0A%3CP%3ESelect%20A2%3AA3%2C%20then%20fill%20down%20to%20A13.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20Q2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS('Form%20Responses%20''21'!%24D%242%3A%24D%24125%2C'%20Form%20Responses%20''21'!%24E%242%3A%24E%24125%2C%20Q%241%2C%20'Form%20Responses%20''21'!%24B%242%3A%24B%24125%2C%20%22%26gt%3B%3D%22%26amp%3B%24A2%2C%20'Form%20Responses%20''21'!%24B%242%3A%24B%24125%2C%20%22%26lt%3B%3D%22%26amp%3BEOMONTH(%24A2%2C%200))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down%2C%20but%20also%20to%20the%20left%20and%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338242%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338242%22%20slang%3D%22en-US%22%3EThank%20you%20for%20replying%20and%20helping.%20I%20was%20able%20to%20change%20the%20information%20on%20the%20totals%20sheet%20as%20you%20instructed...%20but%20when%20I%20inputted%20the%20formula%20in%20Q2%20I%20received%20this%20-%20%23VALUE!%20Array%20arguments%20to%20SUMIFS%20are%20of%20different%20size.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338246%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1048738%22%20target%3D%22_blank%22%3E%40Lindsmae%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20all%20the%20ranges%20that%20you%20refer%20to%20are%20from%20row%202%20to%20row%20125.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0387.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279310i0BED29959346C744%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0387.png%22%20alt%3D%22S0387.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3E%3DSUMIFS('Form%20Responses%20''21'!%24D%242%3A%24D%24125%2C%20'Form%20Responses%20''21'!%24E%242%3A%24E%24125%2C%20Q%241%2C%20'Form%20Responses%20''21'!%24B%242%3A%24B%24125%2C%20%22%26gt%3B%3D%22%26amp%3B%24A2%2C%20'Form%20Responses%20''21'!%24B%242%3A%24B%24125%2C%20%22%26lt%3B%3D%22%26amp%3BEOMONTH(%24A2%2C%200))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338286%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338286%22%20slang%3D%22en-US%22%3EIt%20worked!!%20And%20I%20was%20able%20to%20apply%20it%20to%20all%20the%20cells!!%20Thank%20you%20so%20much%20for%20your%20help.%20I%20greatly%20appreciate%20it.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

4 Replies

@Lindsmae 

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.
best response confirmed by Lindsmae (New Contributor)
Solution

@Lindsmae 

Make sure that all the ranges that you refer to are from row 2 to row 125.

 

S0387.png

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

It worked!! And I was able to apply it to all the cells!! Thank you so much for your help. I greatly appreciate it.