Menu
I'm wondering if there is an easy way to do what I'm looking for. Basically, I have a balance sheet in Excel 2011 with a bunch of data. One specific piece of information I always want visible is the amount that hasn't been reimbursed. In other words, I have a column for the amount paid and another for whether or not it has been reimbursed (Yes/No). I want to sum all of the amounts paid where the reimbursed field is equal to 'No'.
I recognize I can sum the entire column and filter out those that have been reimbursed, but I'd like it to display the full amount regardless of what filter is on (or if no filter is on).
I wasn't able to find good keywords to describe this to Google, so I'm asking here. I would like to accomplish this in Excel, not in an external program or script.
255
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
If you need to sum numbers based on multiple criteria, you can use the SUMIFS function. The first range (D4:D11) are the cells to sum, called the "sum range". Criteria are supplied in pairs... (range / criteria).
To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))
If column A contains the amounts to be reimbursed, and column B contains the "yes/no" indicating whether the reimbursement has been made, then either of the following will work, though the first option is recommended:
=SUMIF(B:B,"No",A:A) or
=SUMIFS(A:A,B:B,"No") Here is an example that will display the amounts paid and outstanding for a small set of sample data.
A B C D Amount Reimbursed? Total Paid: =SUMIF(B:B,"Yes",A:A) $100 Yes Total Outstanding: =SUMIF(B:B,"No",A:A) $200 No $300 No $400 Yes $500 No 
52
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
