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.
Ok, I have done a little more research and maybe I need to use SUMPRODUCT. Cell H137 has my attempt at the formula but I am still getting zero as a result. I picked this cell for the formula because I know the answer should be 150. Any help that can be offered is appreciated!!
My goal is to sum the qty of the part number shown in column A if purchased by the customer shown in cell H21 during the calendar year in cell I20 and any/all of the months shown in cells K20 - Q20. All of the answers for sheet MAS should pull from sheet NEST. Thanks again!!
=SUMPRODUCT(NESTQty)*((((NESTItemNum='MAS1'!$A137)*(NESTCustomerNum='MAS1'!$H$21)*(NESTCalendarYr='MAS1'!$I$20)*(NESTCalendarMo='MAS1'!$K$20)+(NESTCalendarMo='MAS1'!$L$20)+(NESTCalendarMo='MAS1'!$M$20)+(NESTCalendarMo='MAS1'!$N$20)+(NESTCalendarMo='MAS1'!$O$20)+(NESTCalendarMo='MAS1'!$P$20)+(NESTCalendarMo='MAS1'!$Q$20))))
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.
- I think that did it!!! Thank you SO much for your help!