Sum a range if a cell contains a text string

stuartw

Board Regular
Joined
Dec 5, 2007
Messages
237
Hi everyone

The title explains it pretty well and expect this is a walk in the park for most of you!

If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)

Hope that makes sense.

Thanks as usual
Stuart
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Something like

=IF(FIND("Navision",A1)>0,SUM(B1:B10),0)

Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array...

=SUMPRODUCT(--(FIND("Navision",A1:A100)>0),B1:B100) <---- this will not work, just some code to try to tweak
 
Upvote 0
"Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array..."

If you read the OP, thats exactly what he asked to do
 
Upvote 0
"Hmm... don't think this would work, as it would sum every value B1:B10 given the condition is met. I originally thought a SUMPRODUCT would work, but it gives me a #Value error when i try, even when I do it as array..."

If you read the OP, thats exactly what he asked to do

Doh... did read the OP... just didn't comprehend it fully. My apologies :(

Just so used to people needing to sum numbers right next to what they need the criteria ran on.
 
Upvote 0
put a helper column in :-

K1 =IF(ISNUMBER(FIND("Navision",a1,1),b1,0)

and then sum across column K

where a1 - your strings "Sage, Navision, Hardware" etc
b1 - your values that need summing.

Thanks

kaps
 
Upvote 0
As an Aside for MrKowz, You could tweak your formula like this..

=SUMPRODUCT(--(ISNUMBER(FIND("Navision",A1:A100))),B1:B100)

That sums column B for every row where column A contains "Navision"

But as Steve pointed out, the actual criteria was if a SINGLE cell (A1) contained Navision, then sum ALL of column B. If A1 does not contain Navision, then do nothing.

Also, the find will be case sensitive, you can change it to search to be NOT case sensitive
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top