SUMPRODUCT: Having a dynamic range of cells to search for

[samgil]

Hi Gang,

I want to change this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"A1","A2","A3"},C1)))>0

to be a range like A1:A10 instead of "a1", "a2", "a3". However, if none of the cells in the range A1:A10 match C1, yet are empty, it will still return TRUE for some reason.

 

[Max1616]

There might be a better solution, but try this as an array formula:

{=IF(SUM(IF(--ISNUMBER(SEARCH("SEARCHVALUE",A1:A10))<>0,A1:A10,0))=0,FALSE,TRUE)}

To make this an array formula follow these steps:
1.Paste this into the cell: =IF(SUM(IF(--ISNUMBER(SEARCH("SEARCHVALUE",A1:A10))<>0,A1:A10,0))=0,FALSE,TRUE)
2.Double click into the cell and press cnrl+shift+enter
3.You will see brackets appear around the function indicating that you created an array formula.

 

[samgil]

There might be a better solution, but try this as an array formula:

{=IF(SUM(IF(--ISNUMBER(SEARCH("SEARCHVALUE",A1:A10))<>0,A1:A10,0))=0,FALSE,TRUE)}

To make this an array formula follow these steps:
1.Paste this into the cell: =IF(SUM(IF(--ISNUMBER(SEARCH("SEARCHVALUE",A1:A10))<>0,A1:A10,0))=0,FALSE,TRUE)
2.Double click into the cell and press cnrl+shift+enter
3.You will see brackets appear around the function indicating that you created an array formula.

Sorry I don't quite follow. I don't believe that's checking if my value in B1 is in A1:A10?

For more context, this is for a sumproduct formula to search a dataset, and add all the rows where B1 is found. So for this example I made it A1:A10 as the range to look for B1.

 

[XOR LX]

Hi.

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0

Regards

 

[samgil]

Hi.

=SUMPRODUCT(--ISNUMBER(SEARCH(A1:A10,C1)),LEN(A1:A10))>0

Regards

Awesome, many thanks! Final Question - Is there a way to change it from being a 'contains' search, to 'exact match'?

 

[Aladin Akyurek]

Do you mean to have something like this?

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0

 

[samgil]

Do you mean to have something like this?

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0

Yep that also works and seems simpler than the above.

It also is a 'contains' search - any alternative version for an exact match search?

 

[XOR LX]

Awesome, many thanks! Final Question - Is there a way to change it from being a 'contains' search, to 'exact match'?

Sure, and then you don't even need the additional condition to test for emptiness:

=SUMPRODUCT(--(A1:A10=C1))>0

Regards

 

[XOR LX]

Do you mean to have something like this?

=SUMPRODUCT(--ISNUMBER(FIND(A1:A10,C1)))>0

Won't that also return TRUE if any entry in A1:A10 is empty?

Regards

 

[Aladin Akyurek]

Yep that also works and seems simpler than the above.

It also is a 'contains' search - any alternative version for an exact match search?

Care to post a few rows from a1:a10 and the value in c1?