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]

Aside from MacOs or WindowsOs, do you think of any alternative to this formula? Thank you

 

[Peter_SSs]

What about

78459430.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	2565
12	2020	2000
13	2021	50588
14	2022	189860
15	2023	0
Sheet1

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

 

[78459430]

What about

78459430.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	2565
12	2020	2000
13	2021	50588
14	2022	189860
15	2023	0
Sheet1

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

YES!!! Amazing, thank you so much! Life saver

 

[78459430]

One last issue... this formula also is getting the same error #VALUE!
See example here below.
I need to sum ONLY cells containing the word INCOMING (the result on CELL B11 should be $1,000)

Book1
	A	B
1	Extras	8000
2		$500 incoming
3		$1,585 (check 8/1/2019)
4		$2,000 (check 12/1/2020)
5		$500 incoming
6	Extras	2000
7		$189,845 (check 2/1/2022)
8		$15 (check 2/11/2022)
9
10		Result
11	2019	0
12	2020	2000
13	2021	0
14	2022	189860
15	2023	0
Sheet1

Cell Formulas
Range	Formula
B11	B11	=LET(f,FILTER(B1:B8,ISNUMBER(SEARCH(incoming,B1:B8)),"0 "),SUM(LEFT(f,FIND(" ",f))+0))
B12:B15	B12	=LET(f,FILTER(SUBSTITUTE(B2:B9,"$",""),RIGHT(B2:B9,6)="/"&A12&")","0 "),SUM(LEFT(f,FIND(" ",f))+0))

 

[Peter_SSs]

the result on CELL B11 should be $1,000

I can't see what, if anything, 2019 in A11 has to do with that result, but the first thing to try would be to put double quote marks around "incoming"

=LET(f,FILTER(B1:B8,ISNUMBER(SEARCH("incoming",B1:B8)),"0 "),SUM(LEFT(f,FIND(" ",f))+0))

 

[78459430]

Hi @Peter_Simone sorry I missed the " but the issue still exists.
Any other way around?

Book2
	A	B
1	Extras	8000
2		$15,500 incoming
3		$1,585 (check 8/1/2019)
4		$2,000 (check 12/1/2020)
5		$500 incoming
6	Extras	2000
7		$189,845 (check 2/1/2022)
8		$15 (check 2/11/2022)
9
10		Result
11	2019	#VALUE!
12	2020	2000
13	2021	0
14	2022	#VALUE!
15	2023	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B11	B11	=LET(f,FILTER(B1:B8,ISNUMBER(SEARCH("incoming",B1:B8)),"0 "),SUM(LEFT(f,FIND(" ",f))+0))
B12:B15	B12	=LET(f,FILTER(SUBSTITUTE(B2:B9,"$",""),RIGHT(B2:B9,6)="/"&A12&")","0 "),SUM(LEFT(f,FIND(" ",f))+0))

 

[Peter_SSs]

I assume that the $ sign is the issue as it appeared to be with the other formulas so let's try removing it just as we did with them.

=LET(f,FILTER(SUBSTITUTE(B1:B8,"$",""),ISNUMBER(SEARCH("incoming",B1:B8)),"0 "),SUM(LEFT(f,FIND(" ",f))+0))