+ Reply to Thread
Results 1 to 6 of 6

Usine sumifs to change criteria range column based on dynamic criteria

  1. #1
    Registered User
    Join Date
    03-20-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    8

    Usine sumifs to change criteria range column based on dynamic criteria

    Hello,

    My apologies if I'm using the wrong thread to ask for help, I'm not used to using this forum.

    I'm having two separate issues, in the attached sheet, on the tab called 'campaign level' I have highlighted a box in yellow.
    this yellow table references the table above, what I want it to do is to retrieve each of the individual columns in the above table and then provide a sum up for the clicks, impressions etc. for those individual assets.
    so for example, if I change box k19 to say headline 2 from the drop down, I want it to pull all the values under the headline 2 header in the above table. and then in the clicks column I want it to sum-up the clicks and impressions from table 'raw data' which corresponds to that specific value from that specific header i.e headline 2.

    So that leaves me with two problems. firstly I have used an Index/Match formula to pull down the values in the above table based on the criteria in the dropdown box k19, currently this retrieves all values including duplicates, is there away for me to make this only return one of each?

    And secondly for summing up clicks and impressions etc. how do I get a sumifs formula to change the criteria column in 'raw data' based on the value in box K19?

    Sorry if none of this makes sense. I've been playing with using the index/match formula all morning and just cant make it work

    I massively appreciate any help that can be given! thank you!


    Also, apologies for the incredibly random values, this is a work document I had to anonymize.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Usine sumifs to change criteria range column based on dynamic criteria

    Can polar bear, cat, horse, etc appear in MORE than one column?
    Glenn



  3. #3
    Registered User
    Join Date
    03-20-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    8

    Re: Usine sumifs to change criteria range column based on dynamic criteria

    Hi Glenn,

    Yes it can, for this specific example I tried to make each column contain unique value, but in the real sheet the same value can at time be found in different columns.

    Thank you

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Usine sumifs to change criteria range column based on dynamic criteria

    OK. Three non-array formulae (different colour, different formula)

    K20:
    =IFERROR(INDEX(INDEX('Raw Data'!A:K,,MATCH($K$19,'Raw Data'!$A$3:$K$3,0)),AGGREGATE(15,6,ROW('Raw Data'!$A$4:$A$1000)/((INDEX('Raw Data'!$A$4:$K$1000,,MATCH($K$19,'Raw Data'!$A$3:$K$3,0))<>"")*(COUNTIF(K$19:K19,INDEX('Raw Data'!$A$4:$K$1000,,MATCH($K$19,'Raw Data'!$A$3:$K$3,0)))=0)),1)),"")

    L20, copied across to M20:
    =IF($K20="","",SUMIF(INDEX('Raw Data'!$A$4:$K$1000,,MATCH($K$19,'Raw Data'!$A$3:$K$3,0)),$K20,'Raw Data'!S$4:S$1000))

    N20 is similar to L20.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-20-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    8

    Re: Usine sumifs to change criteria range column based on dynamic criteria

    this is amazing!

    I cannot thank you enough! this has been raking my brain in all day!. I love this forum.

    Thank you again Glenn!

    All the best

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Usine sumifs to change criteria range column based on dynamic criteria

    Hahaha. You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Sumifs criteria range with row and column
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-01-2017, 04:32 PM
  2. Sumifs criteria range with row and column
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2017, 02:05 PM
  3. [SOLVED] SUMIFS with multiple criteria WITH specified dynamic range criteria
    By dluhut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-20-2017, 11:03 AM
  4. Replies: 3
    Last Post: 12-09-2015, 03:10 AM
  5. [SOLVED] SUMIFS with a dynamic, offset criteria range
    By adelcap in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2014, 05:35 AM
  6. SUMIFS: Cannot use the same criteria range (A column)
    By cssst5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-23-2013, 04:05 PM
  7. [SOLVED] SUMIFS based on dynamic criteria
    By AaronB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 08:19 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