Let's say we have this inventory list of items.
If we wish to calculate the amount of dresses we have in stock, we'll need to separate the amount of dresses from the rest of the amounts. To do this we add another column, with a formula like so:
=IF(A2="Dress", C2, 0)
Then we can sum the whole column to get the total amount of dresses, which is 18:
=SUM(D2:D17)
There is a shortcut, though. We can perform both operations (the IF and the SUM) with a single operation using the function called SUMIF, like so:
=SUMIF(A2:A17, "=Dress", C2:C17)
This effectively sums column C, but only the dresses. But what if we need to know the count of green dresses?
Again, the easiest solution is to use another column to separate the green dresses from the rest. In this column we will write a formula like...
=IF(AND(A2="Dress", B2="Green"), C2, 0)
And a sum of this column would give us the amount of green dresses, which is 12.
=SUM(E2:E17)
But there is a shortcut for this as well. It's a function called SUMIFS, and it was added in Excel 2007 so it is not available in earlier versions. In our case it would look like this:
=SUMIFS(C2:C17, A2:A17, "=Dress",B2:B17, "=Green")
This function again sums column C. But this time it only sums the rows that say "Dress" on column A and "Green" on column B - so we end up with the amount of green dresses that we wanted with a simpler instruction.
Summary
While we do end up with the same answer, we arrive at the answer in a far simpler way and without using up more columns.
With a bit of imagination you can use more complex criteria and use this function to sum up different parts of the data easily.
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
0 comments:
Post a Comment