In Excel sum all values in one column in each row where another column is a specific value

Question

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

User · Answer

You could do this using SUMIF  This allows you to SUM a value in a cell IF a value in another cell meets the specified criteria  Here s an example    -   A         B  1   100       YES  2   100       YES  3   100       NO   Using the formula   SUMIF B1 B3   YES   A1 A3   you will get the result of 200    Here s a screenshot of a working example I just did in Excel

User · Answer

You should be able to use the IF function for that  the syntax is  IF condition  value if true  value if false   To add an extra column with only the non-reimbursed amounts  you would use something like    IF B1  No   A1  0    and sum that  There s probably a way to include it in a single cell below the column as well  but off the top of my head I can t think of anything simple

User · Answer

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

[excel] In Excel, sum all values in one column in each row where another column is a specific value

Examples related to excel

Examples related to excel-formula