Hello friends its Sachin here with another blog on Sum Ifs formula of excel. In this blog we will learn the following things about this formula

Sum Ifs function short and sweet – SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

1.) Syntax and insight of formula

2.) Uses with Practical example

3.) Things to be taken care of


Syntax and insight of formula

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)

Sum Range – its a range that will be summed up if the criteria meets

Range 1 – Its a range from which the formula will search the criteria

Criteria 1 – its a criteria that you want to specify

Range 2 – Range 2 is used when you want to meet the more than . 1 criteria

Criteria 2 – Criteria for 2nd Range 2


Uses with Practical example

When to use Sum Ifs formula – Sum ifs formula is used when you want to sum the range on the basis of certain criteria .

For better understanding lets understand it with the practical examples

Example -1 Calculate the sum of sales region wise from the data table

Explanation – the formula work in 2 steps

  1. it will search for criteria from the criteria range and will filter out the table
  2. then it will take the sum of corresponding values

Example -2 Sum ifs with more than 1 criteria

Explanation – In this example the first sum will take place only when both the criteria are satisfied.

  1. If the sale of northwest region
  2. If the sale is done by sales person – Sachin

Example -3 Sum ifs to calculate month wise sales using formulas

Explanation – here we have used two criteria to calculate sales month wise which means sum will take place only when sale is >= 01/04 2018 and <=30/04/2018 i:e for the month of April. 🙂


Things to be taken care of

1.) Always use text and operators inside “>text” semi colon. Only numbers alone can be entered without semi colon ” “

2.) Do not use and function inside Sum If function to make it work like Sum ifs function It will not work

3.) Instead of using this function you can also use pivot table but it will have to be refreshed every-time you update your data

4.) convert data table into table form using CTR+T shortcut to automatically update the Sum ifs table when you enter new data

10 thoughts on “SUM IFS Formula in Excel and its uses

  1. Hey There. I found your blog using msn. This is an extremely
    well written article. I’ll make sure to bookmark it
    and return to read more of your useful information. Thanks for the post.
    I will certainly return.

  2. Hey there excellent blog! Does running a blog similar to this
    require a massive amount work? I’ve very little understanding of computer programming but I was hoping to start my own blog in the near future.
    Anyway, should you have any recommendations or techniques for new blog
    owners please share. I know this is off subject however I just wanted to ask.
    Thank you!

  3. It’s hard to come by well-informed people in this particular topic,
    however, you seem like you know what you’re talking about!

    Thanks

Leave a Reply

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