Previous 1 2 Next 18 Replies Latest reply: Jul 12, 2010 1:41 AM by KOENIG Yvan
hhk Level 1 Level 1 (0 points)
I have a row of numbers that I would like to sum only if the header row does not contain a certain text string. I need to use wildcards in the text string.

I tried the following:

=SUMIF($F$1:$BK$1,"SHIP",F2:BK2)

Didn't work. I tried the inside and outside the quotes. Neither worked. Is there a way to do this?

MacBook Pro, Mac OS X (10.6.4)
  • Level 8 Level 8 (41,780 points)
    Apply the correct syntax, the one described in *_iWork Formulas and Functions User Guide_* and, what a surprise, you will get the correct behaviour !



    =SUMIF($F$1:$BK$1,"=SHIP",F2:BK2)

    Yvan KOENIG (VALLAURIS, France) vendredi 9 juillet 2010 13:40:31
  • hhk Level 1 Level 1 (0 points)
    Sorry, I mis-typed the formula in my original post. I'm trying to test that a certain text string is not contained in the range. What I tried was:

    =SUMIF($F$1:$BK$1,"SHIP",F2:BK2)

    I did refer the the User Guide before I posted and there is no mention of a not-equals operator.
  • Level 8 Level 8 (41,780 points)
    Are you assuming that I built a fake copy ?

    If you don't see the operators in page 186 of the 1st edition or page 198 in the late one, it's time to get new glasses.



    The unique case where an operator is not required is when we test tast the cell content is equal to a numerical value.

    Yvan KOENIG (VALLAURIS, France) vendredi 9 juillet 2010 15:48:05
  • Badunit Level 6 Level 6 (11,380 points)
    You say you want to sum only if the header row does not contain a certain text string. Assuming that text string could be anywhere in the word, the condition should be

    "< > SHIP"

    Do not copy/paste the above condition into your formula. This forum uses the < and > symbols for other things so a trick is required to get them to show up in a post. They have an option-space between them and another option-space after them which need to be removed for your formula. Remove the spaces.
  • Level 8 Level 8 (41,780 points)
    May I add that the use of the wildcards is described in page 361 of IFFUG?
    In the original edition, it's in page 343.

    Yvan KOENIG (VALLAURIS, France) vendredi 9 juillet 2010 19:20:52
  • hhk Level 1 Level 1 (0 points)
    My glasses work fine KOENIG and your helpful arrows point to a "less than" and an "equal to" operator.
  • hhk Level 1 Level 1 (0 points)
    Thanks for that. I see what's going on now. In both my original post and my follow up, my greater than and less than symbols disappeared from the formula I posted. Gotta admit that had me scratching my head.

    I tried your suggestion and it simply did not work for me. I worked around it by summing the range and subtracting the result of a SUMIF formula that matches the text string.
  • Level 8 Level 8 (41,780 points)
    Of course, you needed an example dedicated to your exact case :
    When a feature is described, it's assumed that users are able to understand that it's impossible to describe every case.
    An operator is an operator, the way to use them is the same for all of them.

    But, since I saw a question about the way to change a negative number into a positive one, I'm ready to read a lot of foolish things and so, the Lester Young's CD is already inserted in the reader

    Yvan KOENIG (VALLAURIS, France) samedi 10 juillet 2010 10:52:18
  • Barry Level 7 Level 7 (29,210 points)
    hhk wrote:
    Thanks for that. I see what's going on now. In both my original post and my follow up, my greater than and less than symbols disappeared from the formula I posted. Gotta admit that had me scratching my head.

    I tried your suggestion and it simply did not work for me. I worked around it by summing the range and subtracting the result of a SUMIF formula that matches the text string.


    The Discussions software parses the < and  > symbols as containers for HTML tags or as markers for indented text (as above). It can be forced to display the characters by preceding each occurrence with a 'sticky space' (option-space).
     
     

    Your workaround is likely the solution in this case. The 'NOT EQUAL TO' operator does not play well with the wild card operator, as can be seen in the table below.

    Comparisons in the left column are with the text in the header of each column.
    with wild cards

    Highlighted cells in row 2 are the amounts that should be included in the SUMIF per your original request. You can see from the results in row 5 that had the formula been interpreted as having a syntax error, the actual sum would have included the three unhighlighted cells as well.

    BTW (to Yvan), after posting the picture, I added an "=B$1="ship" comparison in row 6 to round out the set, and got a "FALSE" result in each column. That's consistent with the rest of the results shown, but not with the results from the SUMIF function using "ship" as the condition argument. Bug, or within the expected behavior? I've sent you the test file, should you want to take a look.

    Regards,
    Barry
  • hhk Level 1 Level 1 (0 points)
    Barry, you mean you actually tried it instead of jumping all over my "foolish" post? Imagine that! Thanks for your reasoned response.

    Yvan, I guess operators aren't always operators. You learn something new every day.
  • Badunit Level 6 Level 6 (11,380 points)
    Barry, I had no problem when I tested it. I used a similar bunch of words and it added up correctly. I tried it with no wildcards, with a wildcard on either end and with wildcard on both ends and got the correct result each time.

    Did you have the less than and greater than symbols inside the quotes or outside?
  • Barry Level 7 Level 7 (29,210 points)
    Badunit wrote:
    Did you have the less than and greater than symbols inside the quotes or outside?


    Always good to have a second pair of eyes!

    SUMIF NOT &amp; Wildcard (revised)

    Result in selected cell. Formula (with quotes now in 'right' place) in Formula box.
     
     
    Now as to the four intervening rows;

    Each contains a simple boolean statement comparing the text in the column header using the operator(s) in the Row header.

    Example: in B6:   =B$1="ship"

    Moving (or removing) either or both quotation marks returns a syntax error message. Leaving them in gives the results shown, which do not distinguish between strings containing and strings not containing "ship".

    Moving away from the OP's question, I was able to return a zero value for items with header labels containing "ship" and the value in row 2 for those not containing "ship" with a formula using IFERROR and FIND:

    =IFERROR(FIND("ship",B$1)*0,B$2)

    and to return either the empty string or the value in row 2 with this variation:

    =IFERROR(IF(FIND("ship",B$1)>0,"",B$2),B$2)

    Which also works with the third argument of the IF statement omitted:

    =IFERROR(IF(FIND("ship",B$1)>0,""),B$2)

    Interesting, and possibly useful, but not necessary in this case.

    Regards,
    Barry
  • Level 8 Level 8 (41,780 points)
    Barry wrote:


    BTW (to Yvan), after posting the picture, I added an "=B$1="ship" comparison in row 6 to round out the set, and got a "FALSE" result in each column. That's consistent with the rest of the results shown, but not with the results from the SUMIF function using "ship" as the condition argument. Bug, or within the expected behavior? I've sent you the test file, should you want to take a look.


    Hi Barry.

    I received nothing from you.

    For the OP :

    I don't know what allow you to wrote that I didn't tested.
    It's easy to break the thermometer but it doesn't change the temperature !



    As you may see, I did and it works flawlessly.

    Yvan KOENIG (VALLAURIS, France) dimanche 11 juillet 2010 08:01:05
  • Level 8 Level 8 (41,780 points)
    Hi Barry.

    I received nothing from you.


    I just received Barry's sample file.

    Opening it, I had a huge surprise.
    When I got the User Guides, I saw:


    So, I never tried to use wildcards as he did … and I was right.

    He use formulas like :

    which doesn't match the way conditions must be coded.

    The used formulas aren't using wildcards, they just compare to the string "ship".

    With functions like COUNTIF, SUMIF, …

    the entire condition must be enclosed in quotes.



    We must remember that when the condition is "ship",
    it is not here to check if the string is smaller or greater
    it is not here to check if the string is smaller and greater which would always be false

    The given condition means :
    check if the string is different than ship.
    I always wondered why Numbers designers used this old-fashioned way to embed this operator.
    The logical one would be

    From my point of view < or > applied to a string with wildcards means nothing .
    When there is only < or > in the comparison, the asterisks aren't wildcards but standard chars.
    space +ship,!ship, "ship, #ship, %ship, &ship, 'ship, (ship and )ship will be treated as smaller than ship
    because, space, !, ", #, %, &, ' ,(, ) are smaller than *.

    As +, comma, ., / and letters are 'greater' than asterisks, strings beginning with on of these characters before ship are always greater than ship.



    It's only when we pass the operator different or the operator equal that wildcards are at work.
    And when they are, they do their job correctly.

    Yvan KOENIG (VALLAURIS, France) dimanche 11 juillet 2010 12:31:55
Previous 1 2 Next