how can I correct a sumifs formula

[Suzanne25811]

I have a spreadsheet in which I have a number of tabs.  The main tab - Item Sales Analysis - is updated monthly and then it feeds separate sheets by territory (example NEST).  From there I want to create a sheet for each salesperson (example MAS1) to use for forecasting.  On it, I have all part numbers and a few other columns.  I also have a list of their top 10 customers.  I am trying to create a sumif formula that will sum the total of each item purchased by customer for a given period from their territory sheet (I have 11 columns - one for each of the top 10 and 1column labeled All Others).  The goal of this is so they can see how many of some thing were purchased so they can work to forecast their sales by month for each item.

I was considering naming my ranges to potentially makes my formulas easier to read and follow but I have to get the formula working first.  What am I missing??

Once I can figure this out, I will copy the formula down (from line 22 on the MAS1 tab) for each part number and then also copy the tab and make minor adjustments to create a tab for each salesperson.  I have attached a copy of the file.  Then when I create a new quarterly or semi-annual file for them, I can simply update the information in cells I20, K20:Q20 (depending on the number of months to be included), and G21:P21 to get updated information.  They can then plug their forecasts into columns R on.

Thanks in advance for your help!

[#2]

@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. 

[#3]

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)

[#4]

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

[#5 · Accepted solution]

@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.

[#6]

I think that did it!!! Thank you SO much for your help!