Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

SUMIF, *not* matching text string

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)

Posted on Jul 9, 2010 4:30 AM

Reply
18 replies

Jul 9, 2010 6:48 AM in response to hhk

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.

User uploaded file

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

Jul 9, 2010 8:19 AM in response to hhk

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.

Jul 9, 2010 9:41 PM in response to Badunit

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.

Jul 10, 2010 1:52 AM in response to hhk

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

Jul 10, 2010 1:20 PM in response to hhk

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.
User uploaded file

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

Jul 10, 2010 8:29 PM in response to Barry

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?

Jul 10, 2010 10:08 PM in response to Badunit

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!

User uploaded file

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

Jul 10, 2010 11:03 PM in response to Barry

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 !

User uploaded file

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

Yvan KOENIG (VALLAURIS, France) dimanche 11 juillet 2010 08:01:05

Jul 11, 2010 3:41 AM in response to KOENIG Yvan

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:
User uploaded file

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

He use formulas like :
User uploaded file
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.

User uploaded file

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.

User uploaded file

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

Jul 11, 2010 3:49 AM in response to hhk

hhk wrote:
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.


User uploaded file

Yvan KOENIG (VALLAURIS, France) dimanche 11 juillet 2010 12:49:31

SUMIF, *not* matching text string

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.