CODEWITHSUNDEEP
excelexcel-formula
DevinFalgoust
DevinFalgoust

Reputation: 134

How to subtract numbers from total when descriptions match

So let's say I have one section with totals and their descriptions, and another of things taking off these totals. How would I do that? Example:

    --------------------------------------------------------
    |  A  |  B  |        C        |  D  |        E         |
    --------------------------------------------------------
 L1 |  20 |  14 |     Diamonds    |  4  |     Diamonds     |
 L2 |  10 |  8  |     Emeralds    |  2  |     Emeralds     |
 L3 |     |     |                 |  2  |     Diamonds     |
 L4 |     |     |                 |     |                  |
    --------------------------------------------------------

In this example, A is the total number you have, and B is the number you have remaining, and C is the description of those. In D you have the number taken off by people with description E.

So as we can see, we start off with 20 Diamonds. Square B1 Looks for all places in E that Match C1 and subtract their D value from A1. It finds the 4 and 2, subtracting them and leaving 14.

Also, we start with 10 Emeralds. B2 searches column E and finds 1 match, E2, and subtracts it from A2, leaving it with 8.

What would be the formula for each cell in column B to make this happen?

(I'm doing this in Numbers on Mac OSX if that makes any difference)

Upvotes: 0

Views: 147

Answers (1)

Jhon Doe
Jhon Doe

Reputation: 26

Formula in b1: 

=A1-SUMIF($E$1:$E$3,C1,$D$1:$D$3)

copy down, so b2:

=A2-SUMIF($E$1:$E$3,C2,$D$1:$D$3)

Upvotes: 1

Related Questions