+ Reply to Thread
Results 1 to 3 of 3

Maxifs ignoring text values

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Maxifs ignoring text values

    Hello Excel Wizards,

    In this scenario (see attached and below)the user will select his basic ice cream type and then his requested toppings. Based on those selections he can see which of his selected items is contributing the maximum calorie content. My formula '=IF(F4=1,MAXIFS($B$10:$B$180,$C$10:$C$180,1),"") is still returning a max calorie value even though there are "n/a" items selected. I am not familiar enough with array formulas to understand why the MAXIFS formula isn't returning an error when there is a text entry in the array. I thought about trying to multiply each value by 1 to determine whether a value is text or a number but I'm just not sure how to do this with the selected items only in an array. Thanks for your help!

    Ice Cream Store.png

    Marvelous (wishful thinking)

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Maxifs ignoring text values

    Maybe try

    =IF(F4=1,IF(COUNTIFS($C$10:$C$180,1,F$10:F$180,"n/a"),"n/a",MAXIFS($B$10:$B$180,$C$10:$C$180,1)),"")

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: Maxifs ignoring text values

    It worked! I had made a mistake in the ranges, all of the ranges should have been starting at row 6 but once I changed the range it worked like magic Thanks so much!
    Marvelous.

+ 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. Sum the values from a column text string with ignoring blank cells
    By Mohammad Munawar in forum Excel General
    Replies: 4
    Last Post: 08-22-2021, 11:28 AM
  2. Return list of text values from a column, ignoring #N/A's
    By PaulGMD in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2019, 11:23 PM
  3. Replies: 8
    Last Post: 08-20-2019, 06:48 AM
  4. [SOLVED] Count duplicate text values in columns whilst ignoring/excluding certain values
    By adamwestwell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2017, 05:34 AM
  5. Sumproduct with multiple criteria and ignoring text values
    By soapy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2014, 10:50 AM
  6. [SOLVED] Conditional formatting multiple number values but IGNORING text.
    By Dazmeister in forum Excel General
    Replies: 14
    Last Post: 09-06-2013, 09:02 AM
  7. Summing time values and ignoring text
    By Si902 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2011, 11:19 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