Subtracting column values based on another column having a date in it

**laptopgraveyard** · 02-09-2012, 03:05 PM

DISCLAIMER: I am a complete noob. 24 hours into Excel and I have a decent inventory, accounts payable sheet going on. I want to find out what my outstanding balance to my Vendor is.

I have a column for Date Paid To Vendor (Q), the data that goes in here is a date.
I have a column for Individual Payment to Vendor (N)
I have a column for Total Payout to Vendor (AC), which is the sum of all the money in column N

What I want to do is this:
Every time any cell from Q2-Q100 has a date in it, I want the payment in the corresponding column (N) to be subtracted from the Total Payout to Vendor (cell AC):

Example: Payment to vendor happened on 2/9/12 (column Q) for 10.00 (column N). If the total balance was $100 (cell AC), when I enter the date into (Column Q) I want it to subtract the number in Column N from AC.

I want this to happen anytime I enter a date into Column Q. So it should subtract any value in the corresponding N column and row.

I tried to do this with the IF function, but it got me nowhere.

I basically want to say IF Q is equal to a date, then minus the corresponding row in column N from the amount in column AC3.

Any help would be greatly appreciated.

**NBVC** · 02-09-2012, 04:40 PM

Try, in your new column:

=IF(Q2<>"",AC2-N2,"")

copied down to row 100

**laptopgraveyard** · 02-09-2012, 05:17 PM

Thanks for the quick response. I will definitely use this as an example.. But I only want the sum of the equation happening in one cell. For instance the result should end up in AC5. How would I go about that?

So to be more clear, I took your example and did this:
=IF(Q:Q<>"DATE",AC3-N2,"SUM")..While it gave me the result, it only gave me the result for N2.

That only gives me the result for one cell (N2) minus the Total I owe the vendor (AC3). I want a universal formula so that anytime I enter a date in column Q, it subtracts the corresponding value of that row from AC3..but as a total.

So in other words if I have

excel.jpg

Because I have entered a date in Q3, it subtracts N3 from the total in AC3. And I want it to do that everytime I enter a date in Column Q. I also tried =IF(Q:Q<>"DATE",AC3-N:N,"SUM"), but that cancels itself out. Is what I am trying to do even possible?

**Pete_UK** · 02-09-2012, 09:05 PM

Put this array* formula in cell AC5:

=AC3 - SUM(IF(P2:P100<>"",N2:N100))

* An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to amend the formula subsequently you will need to use CSE again.

Hope this helps.

Pete

**laptopgraveyard** · 02-09-2012, 10:01 PM

Originally Posted by Pete_UK

Put this array* formula in cell AC5:

=AC3 - SUM(IF(P2:P100<>"",N2:N100))

* An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to amend the formula subsequently you will need to use CSE again.

Hope this helps.

Pete

It worked! Thanks so much, you guys are geniuses! Now why did it work lol... So as I understand anytime you are doing anything that requires a range of cells to be computed it is an array formula? So this formula breaks down to:

Subtract any value in N2 through N100 if P has any value in it from AC3? I would hate to just walk away with the right answer without understanding it.

Thanks so much for the help guys.

**Pete_UK** · 02-10-2012, 04:44 AM

Yeah, you seem to have got it.

Please mark the thread as Solved, and you can also express your thanks by clicking the "star" icon in the bottom left corner of any post that has helped you.

Pete