Subtract value if cell contains X term

Jue92 · #1 10-30-2016, 10:54 AM

Hi,

I seem to be having difficulties with a very simple formula!

ITEM A
Cost A = B6
Outstanding Balance = C6

ITEM B
Cost A = B7
Outstanding Balance = C7

So, if a payment was entered as below:

(A) (B) (C) (D)
Date Paid Amount Method
19/10/2016 ITEM A £350.00 Cash
19/10/2016 ITEM A £300.00 Card
28/10/2016 ITEM B £50.00 BACS

I thought it would be something like =IF(B13:B100="ITEM A",B6-C13:C100,"")

Any help is appreciated!

gebobs · #2 10-31-2016, 06:16 AM

It's hart to tell from your post what you are trying to accomplish. Please attach youor sheet.

Jue92 · #3 10-31-2016, 06:28 AM

Hi gebobs, attached as requested.

xor · #4 10-31-2016, 10:30 PM

You wrote:

I thought it would be something like =IF(B13:B100="ITEM A",B6-C13:C100,"")

What about: =$B$6-SUMIF($B$13:$B$100,"ITEM A",$C$13:$C$100)

Jue92 · #5 11-01-2016, 01:45 AM

That worked. Thank you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Subtract value from one cell into another	otuatail	Excel	3	02-01-2016 03:21 AM
Use a numerical merge field to subtract that number of months	MichaelSpedding	Mail Merge	8	11-11-2015 01:11 PM
How to Subtract value in table, based on two different conditions	nitemath2	Excel	1	06-19-2015 01:28 AM
Find what box in Find and replace limits the length of a search term	Hoxton118	Word VBA	7	06-10-2014 05:05 AM
Insert input box into macro to allow user to define search term	Hoxton118	Word VBA	3	05-19-2014 02:03 AM