Something like
=IF(FIND("Navision",A1)>0,SUM(B1:B10),0)
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
Never underestimate the power of stupid people in large groups.
Something like
=IF(FIND("Navision",A1)>0,SUM(B1:B10),0)
Yesterday I felt on top of the world. Today its falling in on me.
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
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
- Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
- Please back up your file before using any macros suggested!
"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
Yesterday I felt on top of the world. Today its falling in on me.
?
Yesterday I felt on top of the world. Today its falling in on me.
Thanks Steve, that seems to do the trick nicely.
Stuart
Never underestimate the power of stupid people in large groups.
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
- Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
- Please back up your file before using any macros suggested!
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
Read my Excel blog on
http://simplyspreadsheets.wordpress.com/
For more ways I can help you with Excel :-
www.simplyspreadsheets.co.uk
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 by Jonmo1; Sep 10th, 2008 at 11:33 AM.
Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
Find a link in post number 31
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Understood - thanks for all your help as usual
Never underestimate the power of stupid people in large groups.
Like this thread? Share it with others