Excel SUMIFS Sum With Multiple Criteria



In Excel, use the SUMIFS function to sum items using multiple criteria.

For example, sum the orders where an account is Active, and the quantity is greater than or equal to 10.

This video shows the steps for using the SUMIFS function, and using cell references instead of hard coded values.

Get ths sample file on my Contextures website:

Instructor: Debra Dalgleish, Contextures Inc.
Get Debra’s weekly Excel tips:
More Excel Tips and Tutorials:
Subscribe to Contextures YouTube:

Video Transcript

In Excel, you can use the SUMIFS function if you’d like to sum a column based on values in two or more of the adjacent columns.

Here we would like to find a total where the status is Active, and the number of visits is greater than 10.

This only works in Excel 2007 and later versions.

In this cell we’re going to create a formula, starting with an equal sign, and then SUMIFS, open bracket.

Then the first thing we want to do is indicate which cells will be summed once we set the criteria. The amounts are what I want to sum in C2 to C6

Then a comma, and we’ll do our first criteria range. So where should it check for the first criteria?

That’s going to be the status. I’ll select those cells, type a comma, and I’m going to type in the status that I’m looking for.

So inside a double quote, I’ll type Active and end with another double quote and then a comma.

The second criteria range is the number of visits. I’ll select that, and then a comma.

This time I’m going to use operators, as well as the number 10. Within quotes, greater than, equal to, and another quote. We’re going to find visits where it’s greater than or equal to, and an ampersand, and the number 10

Then close the bracket, and press Enter. We can see there were a total of $325 for Active accounts with 10 or more visits.

Instead of typing our values into this formula, we could use a cell reference.

Up here, I have a cell that says Active, and one that has our minimum value that we want to check for.

I’m going to select the double quotes and the word Active and just click on the cell that has that criterion.

The same thing where I have the 10 here. I’m going to click on the cell where I’ve typed a 10. When I press Enter, we get the same number as our results, but now it’s more flexible.

I can type 5 here instead of 10, and we might see a different result here. I can see there’s one there a 4, so if I type a 4, my value changes to include that $100 amount.

Nguồn: https://federicobellucci.net/

Xem thêm bài viết khác: https://federicobellucci.net/cong-nghe/


Article Categories:
Công Nghệ

Comments

  • Thank you!!!!!!

    Asif Memon July 27, 2020 6:17 pm Reply
  • rarest and fantastic example using logical operator with sum ifs, count ifs

    partha mukherjee July 27, 2020 6:17 pm Reply
  • No lie I still don’t get it lol

    Fullrusher July 27, 2020 6:17 pm Reply
  • Your videos are so helpful!! Thank you so much

    Bre Redmond July 27, 2020 6:17 pm Reply
  • This was really excellent. Great content. I got my formula working in 2 min 41 seconds counting watching your video for 2:25!

    Peter Weyandt July 27, 2020 6:17 pm Reply
  • Can you help? my formula is =IF(J27>=65,"100%",IF(J27<=64,"75%",IF(J27<=49,"50%",IF(J27<=48,"25%",IF(J27<=29,"0%"))))), but the function if is till 2nd if only

    Aries Aquino July 27, 2020 6:17 pm Reply
  • Exactly I was looking for this.. I want to thank u.. Thank u for sharing this video

    Arjun Singh Rana July 27, 2020 6:17 pm Reply
  • massive help, thanks!

    Davo Devigne July 27, 2020 6:17 pm Reply
  • Thank you, I’m struggling with that for a long time. 🤗 very helpful

    Havoc_c8 , July 27, 2020 6:17 pm Reply
  • Why do we use &? Why not ">=10"?

    J V July 27, 2020 6:17 pm Reply
  • Thank you, THANK YOU!!!!

    Ko Shunman July 27, 2020 6:17 pm Reply
  • thank you, you saved my life!!

    July 27, 2020 6:17 pm Reply
  • Thank you….very easily explained. Appreciate your video.

    Ankur Parekh July 27, 2020 6:17 pm Reply
  • Thank you!

    Samyak Jain July 27, 2020 6:17 pm Reply
  • I need a help.. Can we highlight only the cells which are the result of the formula

    Chinmaya Pradhan July 27, 2020 6:17 pm Reply
  • Love Love Love …

    Md Tofiqul Islam July 27, 2020 6:17 pm Reply
  • THANKS THIS HAS BEEN HELPFUL

    Michael Numo July 27, 2020 6:17 pm Reply
  • it is ; instead of , when separating criteria…

    realfan91 July 27, 2020 6:17 pm Reply
  • at last found your video much helpful with easy formulation!

    thanks a lot 🙂

    Abhay July 27, 2020 6:17 pm Reply
  • Thank you. Best explanation; helped me to understand the Sumifs function.,

    Hasit Mehta July 27, 2020 6:17 pm Reply
  • Quick, easy, straight to the point, thanks.

    Destroy_them_With_Lazers! July 27, 2020 6:17 pm Reply
  • nice

    Rashid Khan July 27, 2020 6:17 pm Reply
  • all tutorials should be quick and on the point like this one. gj

    Ruiwc25 July 27, 2020 6:17 pm Reply
  • Thank you. Easy and quick. Saved me a lot of time. Thanks again.

    Stephen Tremper July 27, 2020 6:17 pm Reply
  • thanks! great explanation

    olya p July 27, 2020 6:17 pm Reply
  • What if in the range named Status you had 20 different criteria rather than just "active" and "hold" and you wanted to include (let's say) twelve of them in the criteria. And what if you wanted to not have to type out each string each time. Is there some quicker way to do that?

    greatsea July 27, 2020 6:17 pm Reply
  • Hi,i dont understand why "&" is used? Sorry if im being dappy! Please explain. Great vid!

    zee Ali July 27, 2020 6:17 pm Reply
  • Straight and to the point. Thank you!

    Resz7 July 27, 2020 6:17 pm Reply
  • Wondering is there is a way to exclude a "criteria" data from the result of a SUM of a column of items. For example if Column B has it in it "parking"then skip it else add it in the range of fees for the meeting. In other worlds when Parking it there is the only thing paid for as the rest of the fields except mileage were free. but paid for parking then dont include is as we want to know the rest as general fees but want to know parking cost on its own figure. Not SUMIF ? = "public"

    Joan Cronson July 27, 2020 6:17 pm Reply

Leave a Comment

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