Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Excel SumIfs with a named range

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,542
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,542
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default 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)
    Last edited by steve the fish; Feb 13th, 2018 at 06:54 AM.

  5. #5
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,542
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #7
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,542
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel SumIfs with a named range

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

  9. #9
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,542
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •