# Sum a range if a cell contains a text string

This is a discussion on Sum a range if a cell contains a text string within the Excel Questions forums, part of the Question Forums category; Hi everyone The title explains it pretty well and expect this is a walk in the park for most of ...

1. ## Sum a range if a cell contains a text string

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

2. ## Re: Sum a range if a cell contains a text string

Something like

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

3. ## Re: Sum a range if a cell contains a text string

Originally Posted by SteveO59L
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

4. ## Re: Sum a range if a cell contains a text string

"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

?

6. ## Re: Sum a range if a cell contains a text string

Thanks Steve, that seems to do the trick nicely.

Stuart

7. ## Re: Sum a range if a cell contains a text string

Originally Posted by SteveO59L
"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.

8. ## Re: Sum a range if a cell contains a text string

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

9. ## Re: Sum a range if a cell contains a text string

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

10. ## Re: Sum a range if a cell contains a text string

Understood - thanks for all your help as usual

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•