+ Reply to Thread
Results 1 to 6 of 6

Excel Formula that excludes columns with specific words in the header

  1. #1
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Excel Formula that excludes columns with specific words in the header

    I am looking for the max value in the table's total row. I have the formula that worked before below, but then I added more columns and now it doesn't work. Now I need to only include those headers that do not have "Time" or "Price" in the header.

    How would I modify this formula to do that? I have included sample attached.

    Currently, my formula is:
    Please Login or Register  to view this content.
    I want it to exclude a header that has "Time" or "Price" in the header name.

    I saw SumProduct can do this when a word is included in the header, but the solution seemed to be different than what I am needing (because it sumed the whole column, and I am trying to exclude columns with words, etc)

    I appreciate the advice.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula that excludes columns with specific words in the header

    I do not understand why you think you need to use INDIRECT...

    =MAXIFS(Table1mTrend[#Totals],Table1mTrend[#Headers],"<>"&"*Time*",Table1mTrend[#Headers],"<>"&"*Price*")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Excel Formula that excludes columns with specific words in the header

    Thank you for helping.

    I am not saying I need Indirect. My goal is for the formulas to work; not for it to be the best or fastest solution. After my research, this solution worked. But now the situation changed, and I did not know what to do.

    One question about the formula you suggested. I need to limit the columns' range to be from Dynamic1_1mL2_10 to Dynamic4_TrdProt_10. How would I incorporate that into your suggested solution?

    I tried
    Please Login or Register  to view this content.
    But it gives a Value error.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula that excludes columns with specific words in the header

    try this, then:

    =MAXIFS(Table1mTrend[[#Totals],[Dynamic1_1mL2_10]:[Dynamic1_TrdProt_10]],Table1mTrend[[#Headers],[Dynamic1_1mL2_10]:[Dynamic1_TrdProt_10]],"<>"&"*Time*",Table1mTrend[[#Headers],[Dynamic1_1mL2_10]:[Dynamic1_TrdProt_10]],"<>"&"*Price*")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-19-2016
    Location
    Muscat, Oman
    MS-Off Ver
    365
    Posts
    56

    Re: Excel Formula that excludes columns with specific words in the header

    Thank you very much. Huge help! It works perfectly.

    Oh, now I remember why I had Indirect in that formula. Duh.

    Because the column names to limit the range (in this case are Dynamic1_1mL2_10 to Dynamic4_TrdProt_10) are actually achieved by a cell reference. So I was building the table reference with two cell references in the middle and then indirect to pulling it all together. It is clear to me now. haha.

    But I solved that problem now, so I don't need to do the reference, and don't need Indirect anymore.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula that excludes columns with specific words in the header

    Hahaha. As long as there was a reason!! Once.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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

+ 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] matching specific words in text string to column header
    By macrorookie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2020, 02:53 PM
  2. [SOLVED] help with formula that excludes two columns in formula
    By InvGrp2 in forum Excel General
    Replies: 6
    Last Post: 06-30-2018, 01:46 AM
  3. [SOLVED] VBA for printing specific header, specific columns and cells to one page.
    By Chiefsotos in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-02-2017, 11:42 AM
  4. [SOLVED] Average Formula that excludes specific "zeros"
    By The Man With No Name in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-23-2015, 01:34 PM
  5. [SOLVED] How to create a formula that excludes specific text.
    By eekbubble in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-08-2014, 08:52 AM
  6. [SOLVED] looking formula for Extract Specific WORDs in existing LONG Words
    By santosh226001 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2013, 08:21 AM
  7. Formula Calculation That Excludes Specific Hours of the Day
    By BuddL33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2013, 04:43 PM

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