Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2412698%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412698%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20finding%20a%20formula%26nbsp%3B%3CSPAN%3Ethat%20will%20take%20the%20current%20month%20out%20of%20others%20like%20May%20for%20example%20and%20then%20pull%20all%20of%20those%20dates%20from%20that%20month%20with%20the%20qty%20only%20if%20its%20not%200%20that%20is%20associated%20for%20that%20day%20and%20put%20it%20into%20another%20table%20with%20the%20date%20and%20qty%20and%20update%20the%20info%20if%20the%20month%20has%20changed.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20using%20the%202016%20version%20of%20excel%3CBR%20%2F%3E%3CSPAN%3Ethanks%20anything%20is%26nbsp%3B%3C%2FSPAN%3Eappreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412698%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2416682%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059221%22%20target%3D%22_blank%22%3E%40TMan5757%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20limited%20to%20having%20the%20column%20with%20month%20numbers%20as%20the%20month%20numbers%20or%20adding%20%22helper%22%20columns%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20okay%20with%20changing%20the%20month%20numbers%20to%20dates%2C%20you%20can%20use%20the%20SUMIFS%20function%20to%20accomplish%20your%20task.%3C%2FP%3E%3CP%3EIn%20your%20Sheet1%2C%20I%20replaced%20the%20values%20in%20%24E%245%3A%24E%2416%20with%20dates%20(e.g.%2C%201%2F31%2F2021%2C%202%2F28%2F2021%2C%20etc)%3C%2FP%3E%3CP%3EThe%20I%20used%20a%20combination%20of%20SUMIFS%20and%20the%20EOMONTH%20functions%20to%20total%20the%20values%20in%20column%20B%20if%20the%20date%20is%20column%20A%20is%20between%20the%20first%20day%20of%20the%20month%20and%20the%20date%20in%20column%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20in%20%24F%246%20on%20Sheet1%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(%24B%242%3A%24B%2423%2C%24A%242%3A%24A%2423%2C%22%26gt%3B%3D%22%26amp%3BEOMONTH(E5%2C-1)%2B1%2C%24A%242%3A%24A%2423%2C%22%26lt%3B%3D%22%26amp%3BE5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20if%20you%20can%20add%20a%20helper%20column%20to%20represent%20the%20month%20number%20(using%20the%20MONTH%20function)%2C%20then%20you%20can%20replace%20most%20of%20the%20SUMIFS%20with%20just%20one%20criteria%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(%24B%242%3A%24B%2423%2C%5BRange%20with%20the%20number%5D%2C%5BMonth%20Number%20to%20Match%5D)%0A%0A%3DSUMIFS(%24B%242%3A%24B%2423%2C%24C%242%3A%24C%2423%2CE5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I need help finding a formula that will take the current month out of others like May for example and then pull all of those dates from that month with the qty only if its not 0 that is associated for that day and put it into another table with the date and qty and update the info if the month has changed.

I am using the 2016 version of excel
thanks anything is appreciated

1 Reply

@TMan5757 

Are you limited to having the column with month numbers as the month numbers or adding "helper" columns?

 

If you're okay with changing the month numbers to dates, you can use the SUMIFS function to accomplish your task.

In your Sheet1, I replaced the values in $E$5:$E$16 with dates (e.g., 1/31/2021, 2/28/2021, etc)

The I used a combination of SUMIFS and the EOMONTH functions to total the values in column B if the date is column A is between the first day of the month and the date in column E.

 

This is the formula in $F$6 on Sheet1:

=SUMIFS($B$2:$B$23,$A$2:$A$23,">="&EOMONTH(E5,-1)+1,$A$2:$A$23,"<="&E5)

 

Of course, if you can add a helper column to represent the month number (using the MONTH function), then you can replace most of the SUMIFS with just one criteria

=SUMIFS($B$2:$B$23,[Range with the number],[Month Number to Match])

=SUMIFS($B$2:$B$23,$C$2:$C$23,E5)