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:
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.
Xem thêm bài viết khác: https://federicobellucci.net/cong-nghe/