Excel SumIfs with a named range

Groovy Chick

Board Regular
Joined
Oct 10, 2017
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Ok so what does the named range refer to? It will show in the name manager dialog box.
 
Upvote 0
Hi, it refers to <='Weekly QN Details'!$S$3:$S$54>. Is that what I should use? Many thanks.
 
Upvote 0
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
Im confused. What is in the cells of the named range then? That is testing if they equal what is in T2.
 
Upvote 0
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.
 
Upvote 0
It may be easier to provide a sample of the data removing anything sensitive.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top