+ Reply to Thread
Results 1 to 3 of 3

Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventions

  1. #1
    Registered User
    Join Date
    11-06-2017
    Location
    Australia
    MS-Off Ver
    EXCEL 365
    Posts
    4

    Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventions

    Hello! I'm new here and could use some assistance adapting my current formula to take into account multiple naming conventions for our models. I have an excel sheet that gets raw data for stock supplied/returned dropped into it from several hundred of our customers. Unfortunately, naming conventions have become a huge hindrance making what was an automated process manual.

    Feel free to open up the example provided and follow on:

    On the 'ALL SALES' sheet you will find in 'C2' my current formula: =SUMIF('WE 0601'!$A:$A,$A2,'WE 0601'!$E:$E)

    What this currently does is looks into the raw data we dump into one of our week ending sheets which in the case of 'C2' is the 'WE 0601' sheet as per the header. Firstly it looks up the contents of every item in column A, checks it against 'A2' back in the 'ALL SALES' and for every match it finds it reads the numerical data in column E back in the 'WE 0601' sheet. Then it adds all those numbers found and outputs a total back into 'C2'.

    As discussed earlier, the issue this formula has now is that i'm dealing with multiple naming variants for an individual model coming through in our raw data. So i'm wondering is there a way to update my formula to check against all possible naming conventions for an individual model, and in the event that multiple are found it still adds them all together.

    I created a 'NAMING CONVENTIONS' sheet that I would like to use as a glossary for the formula to check possible naming conventions.

    Basically, as an example if A2 in 'ALL SALES' was RED, I would like it to jump into 'NAMING CONVENTIONS', check all possible naming conventions next to red, then jump back into 'WE0601' check A:A for any and all of the naming conventions, and for everyone found it checks Column E and grabs the numerical value adding them all together and provides a total back in 'C2' where the formula is.

    Any help would be much appreciated!
    Attached Files Attached Files
    Last edited by Typirious; 11-07-2017 at 06:13 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventio

    Is "6-Jan-18" end date of week "WE 0601"? Using INDIRECT to refer to sheet "WE 0601" based on column header.
    Try in C2 the drag down and accross:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    11-06-2017
    Location
    Australia
    MS-Off Ver
    EXCEL 365
    Posts
    4

    Re: Sumif - Trying to figure out how to tell a sumif to look for multiple naming conventio

    Yes WE 0601 is in reference to the header "6-jan-18", being end of the week.

    Worked a treat and taught me a lot in the process thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 1D Array Naming Conventions
    By tom.hogan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2014, 10:51 AM
  2. [SOLVED] VBA naming conventions
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-23-2013, 09:36 AM
  3. Naming conventions and code maintenance
    By Kyle123 in forum The Water Cooler
    Replies: 1
    Last Post: 03-07-2012, 06:14 PM
  4. Using data validation to set naming conventions
    By DrEvilAces in forum Excel General
    Replies: 7
    Last Post: 12-15-2009, 09:25 AM
  5. Importing Naming Conventions
    By john432 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2008, 02:47 AM
  6. File naming conventions
    By gidget in forum Excel General
    Replies: 7
    Last Post: 11-15-2005, 10:20 AM
  7. Naming conventions of objects in VBA
    By whiteliyl_111 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2005, 10:59 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1