IF(SUMPRODUCT(−−ISNUMBER(SEARCH Doesn't work on Numbers

Question

teganowen1 Author

Level 1

0 points

IF(SUMPRODUCT(−−ISNUMBER(SEARCH Doesn't work on Numbers

I have used this:

In cell B1

IF(SUMPRODUCT(−−ISNUMBER(SEARCH(A1,Table1::$C$2:$C$9)),"Used",""))

It gives me the error:

IF requires between 2 and 3 arguments, but was given 1.

The formula uses a Boolean in place of a number.

Example of text in cells C2-9

shiitake mushrooms, bean sprouts, chilli, lime, spring onion,

2 can creamed sweetcorn, spring onion, 100g oyster mushrooms

spring onions, 2 x 280g Baby Spinach 300g Silken Tofu, 1/3 cup (25g) panko breadcrumbs, 10g dill, 1 lemon, 15 sheets filo pastry, sesame seeds, 250g vine-ripened cherry tomatoes

Example of word in A1, Lemon

MacBook Pro 13″, macOS 13.4

Posted on Jun 26, 2023 9:38 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,384 points

Jun 27, 2023 4:17 AM in response to teganowen1

IF(SUMPRODUCT(−−ISNUMBER(SEARCH(A1,Table1::$C$2:$C$9)),"Used",""))

It appears to be looking in C2:C9 for words that contain the search term in A1. The words may be longer than the search term but they must contain the search term within them. If A1 has "abc" then "abc123", "123abc", "12abc34", and "abc" are all matches for the search. If any have it then the IF returns "used". Not that it matters here but I think the closing parenthesis is out of place for SUMPRODUCT; I think the formula should end with only one closing parenthesis.

I think this does it:

=IF(COUNTIF(Table1::$C$2:$C$9,"*"&A1&"*")>0,"Used","")

If the words in C2:C9 need to be exactly the search term (which is not what the posted formula suggests),

=IF(COUNTIF(Table1::$C$2:$C$9,A1)>0,"Used","")

Reply

Answer 2

SGIII

Level 8

37,550 points

Jun 27, 2023 12:14 AM in response to teganowen1

teganowen1 wrote:

IF(SUMPRODUCT(−−ISNUMBER(SEARCH(A1,Table1::$C$2:$C$9)),"Used",""))

That's an old "array function" form of SUMPRODUCT sometime used in Excel. It won't work in Numbers because Numbers doesn't support (most) array functions.

From your description it's a little hard to know exactly what you are trying to do. Most likely solutions will involve the use of SUMIFS, or perhaps TEXTJOIN and COUNTMATCHES. But more specifics will help.

SG

Reply

Answer 3

Barry

Level 9

60,023 points

Jun 26, 2023 11:30 PM in response to teganowen1

Hi owen,

Can you describe, in words, what calculation you want the formula to do?

A screen shot of the table, showing the data to be used in the calculations would also be useful.

To take a screen show of part of the screen:

Select the cell that is to contain the formula. This activates the table, and shows the column and row reference tabs above and left of the table.

I think SUMPRODUCT might be an appropriate function to do the job you want, but it requires all values used must be numbers, not text.

Here is an example of what SUMPRODUCT does. Note that only numbers are entered in the cells referenced in the formulas

There are three formulas on this table.

B2: SUMPRODUCT(C2:D7)

E2: PRODUCT(C2,B2) (Filled down to E7)

E8: SUM(E2:E7)

The first in this list does the same job as the set of six copies (one for each row) of the second and (one copy of) the third.

What is the data being searched in your formula? Please include that in your description.

Regards,

Barry

Reply