I'm sure this is simple but....

[FatalLordes]

... I'm having a brain fart moment and for the life of me can't figure it out.

I have the following:

What I want to do is look at cell B23 and if it is LESS than B10 (and would be coloured red if it was), do nothing as I'm not interested in negative numbers. But if it MORE than B10 (and would be coloured black), I want to do B23 - B10 and assign the value to K23 with the formula K23 = K23 + whatever B23 - B10 is. Then I want to do the same for C and then D, etc, but all adding to K23. So essentially K23 will be the value to the other cells added together but only if row 23 is MORE than row 10 (but looking at individual cells). I hope I explained that correct. So for row 23, the value of K23 at the end would be $520.82.

If it helps, I did do conditional formatting which when the value is less than that in row 10 it is coloured red.

I'm just hitting brick walls when I try to do this so would truly appreciate some help

 

[AhoyNC]

Try:

Book1
	B	C	D	E	F	G	H	I	J	K
10	$50,000.00	$50,000.00	$100,000.00	$100,000.00	$100,000.00	$25,000.00
11
22
23	$49,362.81	$50,146.17	$100,336.52	98,680.98	$99,235.66	$25,038.13				520.82
24	$49.378.63	$50,166.27	$100,379.92	$98,715.11	$99,295.84	$25,049.42
25	$49,279.43	$50,045.78	$100,110.40	598,524.87	$98,994.68	$24,977.42
Sheet2

Cell Formulas
Range	Formula
K23	K23	=SUM(IF(B23:G23>$B$10:$G$10,B23:G23-$B$10:$G$10))

 

[FatalLordes]

=SUM(IF(B23:G23>$B$10:$G$10,B23:G23-$B$10:$G$10))

Thanks. Tried that but I get an error in K23 saying "A value used in the formula is of the wrong data type". I confirmed that all referenced cells are formatted as currency, including K23. What have I done wrong?

So this only adds the difference up when cell in row 23 is larger than cell in row 10 (same column)? I didn't realise it could be done on one line - I assumed VBA would be necessary.

 

[FatalLordes]

Strangely even if I change all the cells referenced to numbers, I still get the same error??

 

[AhoyNC]

Check with the ISNUMBER function to make sure all cells are numeric.

Book1
	B	C	D	E	F	G
10	$50,000.00	$50,000.00	$100,000.00	$100,000.00	$100,000.00	$25,000.00
11	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
22
23	$49,362.81	$50,146.17	$100,336.52	98,680.98	$99,235.66	$25,038.13
24	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
Sheet2

Cell Formulas
Range	Formula
B11:G11,B24:G24	B11	=ISNUMBER(B10)

 

[Alex Blakenburg]

What version of Excel are you using? It is quite possible that @AhoyNC's formula will only work if you have MS 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This is Ahoy's formula converted to sumproduct, see if this works for you in K23.

=SUMPRODUCT(($B23:$G23>$B$10:$G$10) * ($B23:$G23-$B$10:$G$10))

 

[FatalLordes]

Ahh, I'm Office Pro Plus 2019....

And YES, your SUMPRODUCT works. Thank you BOTH. I honest thought it would be a big VBA script but this is so much more elegant and simple. I think I was overcomplicating this - I seriously need more coffee!!

Thank YOU so much!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
@AhoyNC was kind enough to create and share his XL2BB.