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.

Brett Wolfington · Accepted Answer · 2018-06-14 11:12:40Z

114

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

Result of Excel calculations

edited Jun 14, 2018 at 11:12

answered Jan 22, 2013 at 17:04

Brett Wolfington

6,6374 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yogesh Over a year ago

The second formula works for Google spreadsheet also. First one did not work for it somehow...

Jim O. Over a year ago

Stacks never let's me down.

RayInNoIL Over a year ago

One bit of clarification. That criteria can be an expression, function, wildcard, etc. , e.g. ">32", TODAY(), "*es", or even something like: =SUMIF(A2:A6,">" & D4,B2:B6)

StoriKnow · Accepted Answer · 2013-01-22 17:12:37Z

18

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:

Excel SUMIF Example

answered Jan 22, 2013 at 17:12

StoriKnow

5,8676 gold badges40 silver badges47 bronze badges

Comments

Brett Wolfington · Accepted Answer · 2016-06-15 11:43:07Z

2

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.

edited Jun 15, 2016 at 11:43

Brett Wolfington

6,6374 gold badges35 silver badges52 bronze badges

answered Jan 22, 2013 at 17:06

Jaloopa

7501 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related