+ Reply to Thread
Results 1 to 9 of 9

SUMIFS and text

  1. #1
    Registered User
    Join Date
    05-09-2014
    MS-Off Ver
    Excel 2010
    Posts
    28

    SUMIFS and text

    Hello, I'm wondering if SUMIFS or another function can be used to return text based on multiple variables. I have attached a file with example data to help explain what I am trying to achieve.

    In the file there is example data from columns A-F. A pivot table starting in H1. And lastly an area of cells where I am curious to know if there is a formula that can used to achieve the result I am looking for; cells highlighted in red.

    The result I am trying to achieve is; retrieve the top 5 spending groups based on a particular week in question. In this example I have chosen week 2, created a pivot table (H1) and used the results here to achieve the results in the area below. I can use SUMIFS to calculate the the numerical values without any problems (cells highlighted in green). However, is there a formula that can achieve the same results for the text? I have discovered the Index formula that can look for text within a set of data but I have had to specify the text I am looking for. In this example the text is likely to change week on week. I know I can achieve the results using pivot tables but am wondering if it can be done using good old formulas.

    Hope I have explained my problem well. Please ask for more details if needed. Really appreciate your help, thanks!
    example data.xlsx

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: SUMIFS and text

    [deleted]
    sorry, mistake
    Last edited by sandy666; 10-07-2015 at 06:04 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    05-09-2014
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIFS and text

    thanks for your help sandy. i'm not actually sure what this has returned, when inputting your formula i get the result 379.40. i'm not familiar with the large sum, not what i'm after but thanks again.

    i'd like a formula (if possible) to replicate what the pivot table is doing by returning the text; somehow to return the text (B:B) that has spent the most (C:C) for a particular week (F:F).

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: SUMIFS and text

    sorry jaredf, I made mistake and sum top five cost*sum top five orders*week. I lost condition B:B

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,192

    Re: SUMIFS and text

    Select any cell from H5:H13
    Then Right Click with Mouse or Shift+F10
    Then Select Filter
    Then Select Top 10.. Under Filter
    Then Change 10 to 5 or as per ur need and press ok
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    05-09-2014
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIFS and text

    Thanks Siva, this does result with what I am after but still wondering if it can be done without the use of pivot tables?

    Thank Sandy, still not sure how to use your method, could you write out your edited formula please using your method described above?

    Jared

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: SUMIFS and text

    it was mistake ignore me

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,192

    Re: SUMIFS and text

    see the attached file
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-09-2014
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: SUMIFS and text

    Wow Siva you got it! Thank you.

    I can understand the SUMIFS within your formula, but not the rest. I'm new to Index and have never used COUNTIF before. Could you kindly explain how you worked that out in layman terms, I'd really appreciate it. Would help me to understand more. If not, not to worry thanks again!

    Appreciate your input all

+ 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 ( one column which contains multiple *text* & * text*)
    By gkoow in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-22-2015, 08:39 AM
  2. [SOLVED] Finding a tag or text within text as part of SUMIFS formula
    By twaccess in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-17-2015, 08:54 AM
  3. [SOLVED] Sumifs exact text
    By Wheelie686 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2015, 08:08 AM
  4. Using SUMIFS When Text Is Involved
    By timsoftlayer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2014, 03:26 PM
  5. SUMIFs Formula that will return text
    By jamesjy90 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2013, 07:09 AM
  6. SUMIFS and return text?
    By ZeroGravity in forum Excel General
    Replies: 0
    Last Post: 12-13-2011, 11:35 AM
  7. Excel 2007 : SUMIFS on multiply text criertia
    By Jerseynjphillypa in forum Excel General
    Replies: 3
    Last Post: 12-06-2011, 08:53 AM

Tags for this Thread

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