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/

Thank you!!!!!!

rarest and fantastic example using logical operator with sum ifs, count ifs

No lie I still don’t get it lol

Your videos are so helpful!! Thank you so much

This was really excellent. Great content. I got my formula working in 2 min 41 seconds counting watching your video for 2:25!

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

Exactly I was looking for this.. I want to thank u.. Thank u for sharing this video

massive help, thanks!

Thank you, I’m struggling with that for a long time. 🤗 very helpful

Why do we use &? Why not ">=10"?

Thank you, THANK YOU!!!!

thank you, you saved my life!!

Thank you….very easily explained. Appreciate your video.

Thank you!

I need a help.. Can we highlight only the cells which are the result of the formula

Love Love Love …

THANKS THIS HAS BEEN HELPFUL

it is ; instead of , when separating criteria…

at last found your video much helpful with easy formulation!

thanks a lot 🙂

Thank you. Best explanation; helped me to understand the Sumifs function.,

Quick, easy, straight to the point, thanks.

nice

all tutorials should be quick and on the point like this one. gj

Thank you. Easy and quick. Saved me a lot of time. Thanks again.

thanks! great explanation

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?

Hi,i dont understand why "&" is used? Sorry if im being dappy! Please explain. Great vid!

Straight and to the point. Thank you!

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"