Multiple IF(ISNUMBER(SEARCH...), need sum

**Imtall62** · 02-10-2014, 02:00 PM

Hi - this is my first post into this forum & I'm hoping you all can help.

I'm trying to consolidate a formula with a lot of =IF(ISNUMBER(SEARCH...) components. It's probably best if I show my example.

If I'm creating a product bundle where each piece has a different price, I want the formula to add the prices depending on which products I choose to include in the bundle.

Column A is the product number.
Column B is the price.

For the example, cell B9, is the bundle I'm creating with 2 or more of the products.

Right now I can get the answer I need with =IF(ISNUMBER(SEARCH($A$2, B9)), $B$2,0)+IF(ISNUMBER(SEARCH($A$3, B9)), $B$3,0)+IF(ISNUMBER(SEARCH($A$4, B9)), $B$4,0)+IF(ISNUMBER(SEARCH($A$5, B9)), $B$5,0)+IF(ISNUMBER(SEARCH($A$6, B9)), $B$6,0), but this will be super time consuming to make if I have 20 products in a potential bundle.

Please see attached for specific spreadsheet.

Thank you!

**benishiryo** · 02-10-2014, 09:48 PM

welcome to the forum. try this array formula in D9:
=SUM(SUMIF($A$2:$A$6,IF(ISNUMBER(SEARCH(" "&$A$2:$A$6&","," "&B9&",")),$A$2:$A$6),$B$2:$B$6))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**martindwilson** · 02-10-2014, 09:56 PM

=SUMPRODUCT(ISNUMBER(SEARCH($A$2:$A$6,B10))*($B$2:$B$6)) seems to work

**benishiryo** · 02-10-2014, 10:02 PM

@martin:
i may be assuming wrongly, but i think OP simplified things as for the Product code. in reality, it may have more than 1 single alphabet. A6 might be BA. B9 might be B, BA

**martindwilson** · 02-10-2014, 10:40 PM

=sumproduct(isnumber(search(" "&$a$2:$a$6&" "," "&substitute(b9,","," ")&" "))*($b$2:$b$6))

**Imtall62** · 02-11-2014, 12:15 PM

@Benishiryo - THANK YOU! This worked and definitely accounts for the longer product numbers like you assumed. I really appreciate your help.

**Imtall62** · 02-11-2014, 12:22 PM

Quick follow up question - is there a way for it to count a value twice - what if I want a bundle with 2 of A & 1 of B, then I'd type in A, A, B in the B9 cell, but it only counts A once currently.

**benishiryo** · 02-11-2014, 10:03 PM

you're very welcome. the solution martin amended in post #5 would work too.

for your new requirement, probably:
=SUMPRODUCT(SUMIF($A$2:$A$6,TRIM(MID(SUBSTITUTE(B9,", ",REPT(" ",LEN(B9))),(ROW($1:$101)-1)*LEN(B9)+1,LEN(B9))),$B$2:$B$6))
don't have to press CTRL + SHIFT + ENTER this time.

do mark it as Solved if that does it. thanks

**Imtall62** · 02-12-2014, 11:41 AM

Thanks again @Benishiryo. I don't know how you figure this out, but I'm amazed & very grateful.

Multiple IF(ISNUMBER(SEARCH...), need sum

LinkBack

Thread Tools

Rate This Thread

Display

Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Re: Multiple IF(ISNUMBER(SEARCH...), need sum

Thread Information

Users Browsing this Thread

Similar Threads

Combined IF ISNUMBER SEARCH for multiple words

Help with multiple If(Isnumber(Search( function

[SOLVED] how to have multiple ISNUMBER search function in SUMPRODUCT

Multiple ISNUMBER(SEARCH()) functions

Multiple IF ISNUMBER SEARCH

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions