Sum if ends with

The SUMIF function can add all values in a range of cells based on the criteria to sum if ends with specific text, e.g. sum only those values which end with “Children” or “children”.

Formula:

How the formula works

The formula for the function is

=SUMIF(A4:A8,”*children”,B4:B8)

where,

range (A4:A8) – The range of cells to be tested against the criteria,

criteria (“*children”) – The criteria to sum only those text strings which end with “children” text and asterisk (*) is used as a wildcard, means “sum one or more characters”,

[sum_range] (B4:B8) – The range of cells to sum against the criteria.

Note: The SUMIF function is not case sensitive, you may either use “children” or “Children” as criteria.

Result:

 

Alternative way

You may also use SUMIFS function to sum values against the criteria “if ends with the specific text”. The SUMIFS formula is mentioned below where sum range always comes first.

=SUMIFS(amount,product,”*children”)

 

Copyright © 2016 - 2017 Explainry.com | All Rights Reserved