**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula to combine D2 to D100 to add together a column of numbers, then take away the same amount on the same row when column E is filled.
i.e. column D is a price of an item, so the formula must calculate the total, then when the item is sold an 'a' is marked next to the item in column E, the formula then must deduct this amount from the total

Solved by X. W. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
27/07/2018 - 07:53

Hello, welcome to excel gotit pro

User
27/07/2018 - 07:53

Can you help?

Excelchat Expert
27/07/2018 - 07:54

I believe I can help.

Excelchat Expert
27/07/2018 - 07:55

But I need to see some sample data and then also the result you are expecting.

User
27/07/2018 - 07:55

Okay, do I type on the spreadsheet?

Excelchat Expert
27/07/2018 - 07:56

You can type in the sheet or you can send me a file too.

User
27/07/2018 - 07:58

A's marked in column B, the amounts next to them in column A need to be deducted from the total in A12

User
27/07/2018 - 07:58

Get it?

Excelchat Expert
27/07/2018 - 07:58

In another cell?

User
27/07/2018 - 07:58

No

User
27/07/2018 - 07:58

in A12

User
27/07/2018 - 07:59

So basically its a list of prices for an item in stock

User
27/07/2018 - 07:59

A12 is the total amount of money I have in stock

User
27/07/2018 - 07:59

when column B has an 'a' entered in it means that item is sold

User
27/07/2018 - 08:00

so the amount needs to be taken away from the total so I know how much money I have in stock

Excelchat Expert
27/07/2018 - 08:01

So, A12 is SUM of A2:A11-SUM of those have a in column B?

User
27/07/2018 - 08:01

Yes

Excelchat Expert
27/07/2018 - 08:01

=SUM(A2:A11)-SUMIF(B2:B11,"a",A2:A11)

Excelchat Expert
27/07/2018 - 08:01

Here is the formula.

Excelchat Expert
27/07/2018 - 08:02

Does that make sense?

Excelchat Expert
27/07/2018 - 08:03

If you have any question, you can ask me.

Excelchat Expert
27/07/2018 - 08:08

If you have any question you can ask.

Excelchat Expert
27/07/2018 - 08:10

There is an option for extending time if required.

Excelchat Expert
27/07/2018 - 08:10

Select Yes to extend otherwise session will end in 2 minutes and we would get disconnected.

Excelchat Expert
27/07/2018 - 08:11

You can rate my service after the end of this chat. Your feedback would be much appreciated.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*