SUM numbers on cells containing specific text.

[78459430]

Hello,

I am trying to SUM only the value (in USD) of a cell that also contains a specific text. Please help, I ran out of ideas
Here below a screen-shot of my cells.

 

[78459430]

Yesss! Amazing, thank you sooooo much

 

[Peter_SSs]

Cheers. No problem.

 

[78459430]

Hello @Peter_SSs I have just noticed (after the last updated on MacOs, Excel version 16.67 - 22111300) that the formula results in a #VALUE error, see attachment. Not sure what happened. Please help

Thank you!

 

[Peter_SSs]

I don't recognise that formula from above and we cannot see the row/column labels in that picture or if anything is actually (hidden) in the cells that appear blank. What happened to XL2BB??
However, from what I can actually see, the formula appears to be working for me. Is this what it should be doing?

22 11 25.xlsm
	U
84	$5,000 (wire on 10/25/2022)
85
86
87
88	17709.14
89	12709.14
Test

Cell Formulas
Range	Formula
U89	U89	=U88-(SUM(--TRIM(IFERROR(LEFT(U84:U87,FIND("(",U84:U87)-1),0))))

 

[78459430]

I don't recognise that formula from above and we cannot see the row/column labels in that picture or if anything is actually (hidden) in the cells that appear blank. What happened to XL2BB??
However, from what I can actually see, the formula appears to be working for me. Is this what it should be doing?

22 11 25.xlsm
	U
84	$5,000 (wire on 10/25/2022)
85
86
87
88	17709.14
89	12709.14
Test

Cell Formulas
Range	Formula
U89	U89	=U88-(SUM(--TRIM(IFERROR(LEFT(U84:U87,FIND("(",U84:U87)-1),0))))

Thank you. But it looks like my Excel does not accept it... see screen-shot... very strange!

But without the $ symbol, it works

 

[Peter_SSs]

Can't investigate from a picture. What happened to XL2BB?

What happens with something like this?

=U88-(SUM(--TRIM(IFERROR(SUBSTITUTE(LEFT(U84:U87,FIND("(",U84:U87)-1),"$",""),0))))

 

[78459430]

Can't investigate from a picture. What happened to XL2BB?

What happens with something like this?

=U88-(SUM(--TRIM(IFERROR(SUBSTITUTE(LEFT(U84:U87,FIND("(",U84:U87)-1),"$",""),0))))

This works!
I have also noted that the original formula results in the #VALUE! error only on a Mac with the latest MacOS Ventura. With MacOS Big Sur the formula works.

 

[Peter_SSs]

Glad you got it working. Thanks for letting us know.

 

[78459430]

Thanks for he XL2BB sample. See if this could work for you.

22 04 02.xlsm
	A	B
1	Extras	8000
2		$980 (check 5/1/2019)
3		$1,585 (check 8/1/2019)
4		$2,000 (check 12/1/2020)
5		$50,588 (check 4/1/2021)
6	Extras	2000
7		$189,845 (check 2/1/2022)
8		$15 (check 2/11/2022)
9
10		Result
11	2019	2,565
12	2020	2,000
13	2021	50,588
14	2022	189,860
15	2023	0
Sum

Cell Formulas
Range	Formula
B11:B15	B11	=LET(f,FILTER(B1:B8,RIGHT(B1:B8,6)="/"&A11&")","0 "),SUM(LEFT(f,FIND(" ",f))+0))

Hello @Peter_SSs, this formula also does not work... in MacOs Ventura the result is #VALUE!... do you thing there is an alternative?

 

[Peter_SSs]

this formula also does not work... in MacOs Ventura the result is #VALUE!

I don't know what Mac OS Ventura is and I do not use a Mac so I'm not sure that I can offer anything.