Sumif based on first 3 letters in criteria range

English
#1

I have a spreadsheet with a description in one column and an amount in another. If the description starts with the letters “SSA” then I like to sum the numbers in the next column. Thanks.

#9

  1. You asked a question specifically about SUMIF spreadsheet function. Its help explicitly tells, that the Criterion argument takes regular expressions or wildcards, if enabled in calculation options (wildcards are enabled by default). This allows you to use expressions like =SUMIF(A:A;"SSA*";B:B) (for wildcards), or =SUMIF(A:A;"SSA.*";B:B) (for regular expressions). Also note, that “Search criteria = and <> must apply to whole cells” option affects the function, so in order to make sure that it only finds the characters in the beginning of the cell, either enable that option, or use a regular expression that explicitly matches the characters at start.

  2. SUMIFS spreadsheet function, in addition to the features of SUMIF, also allows to pass calculated arrays as the RangeN arguments. This allows the solution suggested by @karolus.

  3. There is also SUMPRODUCT spreadsheet function, allowing formulas like =SUMPRODUCT(B1:B2;LEFT(A1:A2;3)="SSA").

  4. In your clarification request, you suddenly provided a sample that doesn’t require to extract N characters from the cells’ start, but needs a simple match of “def” to “def”. This simplifies everything, and allows to use the trivial =SUMIF(A:A;"def";B:B) (assuming, again, that “Search criteria = and <> must apply to whole cells” option is enabled).

3 Likes
#10

Thank you Mike. I appreciate the time you gave me and the thorough response. I will experiment with all 3 of these solutions. Very grateful for the help, and I apologize for my ignorance.

#2

Hallo

=SUMIFS( B:B ; LEFT( A:A ; 3 ) ; "SSA" )
#3

Thanks. I wasn’t sure I could use a range in the criteria.

#4

I don’t understand. Sumif(search range, criteria, sum range). Example below with columns A and B and rows 1 and 2.

____A____B
1__abc___5
2__def___6

What would the formula look like to search for “def” and sum that row?

#5 
SUMIF != SUMIFS
#6

Hi Karolus. I’m guessing that you think I should use SUMIFS instead of SUMIF. I’ll look into this and thank you for your time and attention.

#7

No need for »guessing« …

#8

I see. I’ll try SUMIFS. Thanks again.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.