Summing Values Based on Text Criteria





While working in MS Excel when we prepare reports and dashboards, we come across situations wherein we need to sum the values based on text criteria. Here we can use the SUMIF formula.

 

Let’s understand how to use the SUMIF formula in different ways.

 

Let’s take an excel file where each row of columns A & B contains text and any value respectively.
We want to sum all the numbers in column B corresponding with the text values in column A that meet certain criteria. These criteria have been listed in column F of each screenshot below. We will review one criteria a t time.

 

1. Text is “Excel”

The formula will be =SUMIF(A2:A6,”Excel”,B2:B6)

 

yl11

 

This will sum all the values in column B where the corresponding cell in column A is equal to “Excel”.

 

2. Text starts with “Excel”

=SUMIF(A2:A6,”Excel*”,B2:B6)

 

jl2

 

This will sum all the values in column B where the corresponding cell in column A has text starting with “Excel”.

 

3. Text ends with “Excel”

=SUMIF(A2:A6,”*Excel”,B2:B6)

 

IMAGE3

 

This will sum all the values in column B where the corresponding cell in column A has text ending with “Excel”.

 

4. Text contains “Excel”

=SUMIF(A2:A6,”*Excel*”,B2:B6)

 

IMAGE4

 

This will sum all the values in column B where the corresponding cell in column A has text containing “Excel”.

 

5. Text length is 3 characters

=SUMIF(A2:A6,”???”,B2:B6)

 

IMAGE5

 

This will sum all the values in column B where the corresponding cell in column A contains text of length 3 characters.



5 thoughts on “Summing Values Based on Text Criteria

Leave a Reply

Your email address will not be published. Required fields are marked *


× 6 = twelve

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>