SUMIF? and RIGHT()?

Question

Dearest Wizards,

A10-A1000 has city names followed by an underscore then the capitalized two-letter state designation (San Diego_CA).

Col B has numeric values associated with each city in Col A.

I want to put a two-letter state designator in cell C9 and have the sum total of the values in Col B that are associated with the cities that are in the states shown in Col A displayed in D9.

What formula do I use in D9?

TIA, Sam

Answer 1

Since the S_ is at the beginning, you should use

=SUMIFS(B10:B1000, A10:A1000, "S_*")

Answer 2

Hi,

In cell D2, enter this formula

=GROUPBY(TEXTAFTER(A2:A6,"_"),B2:B6,SUM)

Hope this helps.

Answer 3

Hi Hans,

This solution isn't working for me, BUT part of that may be on me and my original presentation. I'll try to come clean...

Col A is actually a list of parts that may or may not have the prefix "S_" before the part name. Examples: S_wrench, S_Vise, S_Hammer, S_Screwdriver. My goal is to sum ONLY the values in Col B that are associated with a part name that is proceeded by "S_".

I tried to modify your solution to: =SUMIFS(B10:B1000, A10:A1000, "*S_") which didn't work, then =SUMIFS(B10:B1000, A10:A1000, "*"&"S_"). Neither of these worked.

FWIW Col A&B are results from an array -AND- Col A's format is "TEXT"

Any other suggestions will be appreciated.

Thanks,

Sam

Answer 4

Just a suggestion: For future reference you might consider using separate columns for City names & the state abbreviations.

Answer 5

You can use SUMIF or SUMIFS:

=SUMIFS(B10:B1000, A10:A1000, "*"&C9)