Forum Discussion
how can I correct a sumifs formula
Suzanne25811 Your schedule hung up on me several times and I found it difficult to follow. So I stripped it down to the bare minimum needed (copy/paste values and kept only the two relevant sheets) and amended your formula to this:
=SUMIFS(NESTQty,NESTItemNum,$A22,NESTCustomerNum,G$21,NESTCalendarYr,$I$20,NESTCalendarMo,">="&$K$20,NESTCalendarMo,"<="&$Q$20)Note that you don't really need the month numbers in K20:Q20, as long as you have a first and a last month somewhere. Or just define the first month and the last one will be first+6.
This formula sums the qty where the product AND customer match AND where the sale was made in a particular year AND between the first and the last month. I believe that's what you have in mind. Correct? Stripped file attached.
As said, SUMIF returns a value if ALL criteria are met. Your formula was checking for product AND customer AND year AND month 4 AND month 5 etc.
Obviously, no individual sale is made in all 6 months at the same time. Hence, zero for all.
Suzanne25811 Your schedule hung up on me several times and I found it difficult to follow. So I stripped it down to the bare minimum needed (copy/paste values and kept only the two relevant sheets) and amended your formula to this:
=SUMIFS(NESTQty,NESTItemNum,$A22,NESTCustomerNum,G$21,NESTCalendarYr,$I$20,NESTCalendarMo,">="&$K$20,NESTCalendarMo,"<="&$Q$20)Note that you don't really need the month numbers in K20:Q20, as long as you have a first and a last month somewhere. Or just define the first month and the last one will be first+6.
This formula sums the qty where the product AND customer match AND where the sale was made in a particular year AND between the first and the last month. I believe that's what you have in mind. Correct? Stripped file attached.
As said, SUMIF returns a value if ALL criteria are met. Your formula was checking for product AND customer AND year AND month 4 AND month 5 etc.
Obviously, no individual sale is made in all 6 months at the same time. Hence, zero for all.
