1

I have a number of strings with corresponding values. Given a set of keywords I want to return the sum of all the values where the rows string matches one of the given keywords.

I have made available an example workbook here:

https://onedrive.live.com/redir?resid=73DBED9E450E4673!107&authkey=!ALLfOf0NyFxao-U&ithint=file%2cxlsx

I understand you can use INDEX and MATCH to match multiple values, however I have not got very far with this approach due to the fact i seem to be trying to compare one array with another array whereas the INDEX MATCH approach seems to take a finite, or fixed range of keywords for comparison.

Any help or pointers would be much appreciated.

Improve this question

2 Answers 2

Reset to default
1

I believe that what you may have missed is that you can use wildcards when attempting to match a string to a cell.

Using the formula =SUMIF(data!A:A;"*Alaska*";data!B:B) I think I get the results you want for that one keyword. A slightly more general form of this formula would be =SUMIF(Data!A:A;"*"&A3&"*";Data!B:B), assuming the state you want to test against is in cell A3.

If you want to test for several, I'd just add several sumifs on the form above together, unless you want to use a lot of criteria. If that is the case, you'll have to use an array formula, something on the form of =SUM(SUMIF(data!A:A;{"*Alaska*";"*Alabama*"};data!B:B)) etc. A bit more on this in the answer I copied that solution from ;)

I don't think there is any way to include both wildcards and a range in the searchterms, but I could of course be mistaken.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks eirikdaude, this is a lot closer to what i need. I have updated the example sheet to show your result: b). The issue with this formula is that i cannot seem to get it to work when referencing the keywords rather than specifying them as strings. See result c)
You will have to enter the state names, including wildcards, for the states you want to check against inside the curly-braces ({}). Or you could use the formula Tom Sharpe suggests, which I think'll also work, but which may not be quite as easy to read. @webjames
I get the same answer (testing for Alabama) using both formulae. Could use =SUMIF(Data!A:A,""&A3&"",Data!B:B) to make it slightly more general (should have "*" before and after & signs)?
@TomSharpe Yeah, that's a good idea. I'll incorporate it into my answer if you don't mind.
Of course. I hadn't remembered you could use wildcards like that!
1

Here's an array formula version (F1):-

=SUM(Data!B$2:B$99999*(LEFT(Data!A$2:A$99999,LEN(A3))=A3))

OR (F2)

=SUM(IF(ISNUMBER(Data!B:B),Data!B:B,0)*(LEFT(Data!A:A,LEN(A3))=A3))

Must be entered in D3 using Ctrl Shift Enter and pulled down as required.

For a single-cell formula, this seems to work fine (F3):-

=SUM(Data!B2:B99999*(LEFT(Data!A2:A99999,LEN(TRANSPOSE(A3:A7)))=TRANSPOSE(A3:A7)))

OR (F4)

=SUM(IF(ISNUMBER(Data!B:B),Data!B:B)*(LEFT(Data!A:A,LEN(TRANSPOSE(A3:A7)))=TRANSPOSE(A3:A7)))

But be aware that if there is a blank cell in A3:A7, all the data will be selected and added to the individual totals. The second and fourth versions are slow.

enter image description here

Improve this answer

3 Comments

Thanks - however that only sums the result for that single keyword 'A3' i need it to sum for keywords A3 to A7.
As mentioned by @erikdaude, you can sum individual totals, but if you really want to do it in a single formula you can try =SUM(Data!B2:B99999*(LEFT(Data!A2:A99999,LEN(TRANSPOSE(A3:A7)))=TRANSPOSE(A3:A7))) but I haven't time to test it properly at the moment.
Apologies - I missed the dollar signs out in my formula - have edited my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.