Thanks:  0
Likes:  0

# Thread: Excel SumIfs with a named range

1. ## Excel SumIfs with a named range

Hi, I am trying to sum a figure for graphs using a named range so that I can change the week no and it automatically updates the formula and, therefore, the graph. The workings out on the Graph Worksheet are looking at Weekly QN Details worksheet and totalling column J if days in column M is greater than 75 and then use the value in the look up in T2 - week 1, week 2 and so on. I have formula <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!T2,WeekNos)> which I know is wrong as returning value! Should I be using a sumif?

2. ## Re: Excel SumIfs with a named range

Ok so what does the named range refer to? It will show in the name manager dialog box.

3. ## Re: Excel SumIfs with a named range

Hi, it refers to <='Weekly QN Details'!\$S\$3:\$S\$54>. Is that what I should use? Many thanks.

4. ## Re: Excel SumIfs with a named range

Your problem is twofold. Firstly you have your criteria and named range mixed the wrong way round. Secondly your ranges have to be the same length so if the named range refers to S3:S54 then you must use J3:J54 for the sum range and M3:M54 for the other criteria range. Other than that change the named range to be a full column reference as well. So:

=SUMIFS('Weekly QN Details'!J3:J54,'Weekly QN Details'!M3:M54,">75",WeekNos,'Weekly QN Details'!T2)

5. ## Re: Excel SumIfs with a named range

Many thanks, Steve. That works but just realised I've been a total plank and not thought this through properly. I have a list of data with the week no in column N of the Weekly QN details worksheet. So far I have weeks 1-6. I wanted the formula to only look at the current week to total those entries, hence I was using data validation but just realised that there's no correlation between column N and the data validation so no way the formula will work. Can I put anything in the formula that allows column N to look at the value in T2 and only return values for that week that match? So if on week 6 and the data validation shows that week, is there a way to make the formula look in T2 and if column N matches, sum only those rows? Sorry for being a pain.

6. ## Re: Excel SumIfs with a named range

Im confused. What is in the cells of the named range then? That is testing if they equal what is in T2.

7. ## Re: Excel SumIfs with a named range

Sorry for being a nightmare. So, I have a long list of data that I download from SAP for quality issues. The issue may be ongoing so duplicates from week to week. What I am trying to do is create a graph with the total cost held week on week. Because of the duplicates I add a column at the end with week 1, week 2, week 3 and so on. I created a named range, Weeks 1 to 52 (that is what T2 looks at) thinking I could use that to only return the individual week totals but of course column N where I have the week no and T2 don't speak to each other. I was naively trying to create a cell with the week number for the formula to look at. I did have the formula looking at column N with the week number in speech marks but, obviously, as the week changes I have to change the week number in the formula and that's what I was trying to get away from.

8. ## Re: Excel SumIfs with a named range

It may be easier to provide a sample of the data removing anything sensitive.

9. ## Re: Excel SumIfs with a named range

Hi Steve, I have two worksheets, Graphs and Weekly QN Details. On Graph sheet I want to have a formula that, on a weekly basis, charts the total value of stock. Formula I currently have is <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!N:N,"Week 6")>. However, rather than have to keep changing the week number I wanted the formula to look up the current week, which is why I thought I could use data validation to define the week. So, value is in column J to total, based on column M being more than 75 days and column N being the current week.Thank you.

10. ## Re: Excel SumIfs with a named range

Sure that simple enough. Replace "Week 6" with the cell reference that contains "Week 6". So say type Week 6 in cell A1 then put A1 in the formula. Change A1 to Week 5 and you will get week 5s results instead.