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 The formula in G22 should look something like this:
=SUMIFS(NEST!$L:$L,NEST!$C:$C,$A22,NEST!$R:$R,"<>"&$G$21,NEST!$AN:$AN,$I$20,NEST!$AO:$AO,$K$20,NEST!$AO:$AO,$L$20,NEST!$AO:$AO,$M$20,NEST!$AO:$AO,$N$20,NEST!$AO:$AO,$O$20,NEST!$AO:$AO,$P$20,NEST!$AO:$AO,$Q$20)although it also returns zeroes in the MAS1 sheet.
Note that you should not wrap every criteria in quote marks and that SUMIFS returns the sum for item where ALL criteria are met.
Can't really judge if the above formula will give you the correct answers, tough. I haven't gone through the entire workbook to test it. Perhaps you need to go for another solution, alltogether.
Thanks for the tips. I have corrected the formula per your suggestions and I have updated it with range names to make it a little easier to read and follow but I do still get all zeroes. I definitely want all the criteria met to return an answer BUT my references to cells K20 - Q20 should be that it meets any of those criteria, not all. Is there an OR statement that I should be inserting around that part? The most difficult part after that will be column Q because that critieria needs to include anything NOT include in G through P. There will be lines where the correct answer is 0 but I do know that line 281, column Q should have and answer that is not zero. If you think I should be looking for an alternative to the sumifs, do you have a suggestion? I appreciate your help!
=SUMIFS(NESTQty,NESTItemNum,$A22,NESTCustomerNum,"<>"&$G$21,NESTCustomerNum,"<>"&$H$21,NESTCustomerNum,"<>"&$I$21,NESTCustomerNum,"<>"&$J$21,NESTCustomerNum,"<>"&$K$21,NESTCustomerNum,"<>"&$L$21,NESTCustomerNum,"<>"&$M$21,NESTCustomerNum,"<>"&$N$21,NESTCustomerNum,"<>"&$O$21,NESTCustomerNum,"<>"&$P$21,NESTCalendarYr,$I$20,NESTCalendarMo,$K$20,NESTCalendarMo,$L$20,NESTCalendarMo,$M$20,NESTCalendarMo,$N$20,NESTCalendarMo,$O$20,NESTCalendarMo,$P$20,NESTCalendarMo,$Q$20)
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.
