How to use excel sumif function?

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/summing/summing-values-based-on-text-criteria.html">
SHARE




In this article we will learn how to use Sumif function for count the number text contains cells on the bases of criteria in Excel.

While working in MS Excel when we prepare reports and dashboards, we come across situations wherein we need to sum multiple criteria based. Here we can use  excel SUMIF function.

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.

Please follow and like us:


5 thoughts on “How to use excel sumif function?

Leave a Reply

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


× seven = 21

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>