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.

 

[jtakw]

Hi,

Welcome to MrExcel.

That is a bad way to set up your data, it makes calculation way more complicated than it needs to be.
Also, please consider showing samples using XL2BB, see my signature, or at least in Table format, so helpers don't have to manually type up your data.

Book3.xlsx
	A	B
1
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		$189,845 (check 2/1/2022)
7		$15 (check 2/11/2022)
8
9		Result
10	2019	2565
11	2020	2000
12	2021	50588
13	2022	189860
Sheet1074

Cell Formulas
Range	Formula
B10:B13	B10	=SUMPRODUCT(ISNUMBER((FIND("/"&A10,B$2:B$7)))*(LEFT(B$2:B$7,FIND("(",B$2:B$7)-1)))

 

[78459430]

Hi,

Thank you so much for the reply and the the tips, sorry it is my first post here.
I know I should use multiple columns but that's what I need to do.
Also, I get an error because I added some cells with just a number value w/out text (those "Extras" rows) and this creates an error I think... and I don't know how to solve it.
Perhaps you know

 

[78459430]

Oops, here is the table format.

extras	$8,000
	$980 (check 5/1/2019)
	$1,585 (check 8/1/2019)
	$2,000 (check 12/1/2020)
	$50,588 (check 4/1/2021)
Extras	$2,000
	$189,845 (check 2/1/2022)
	$15 (check 2/11/2022)
	Result:
2019	=SUMPRODUCT(ISNUMBER((FIND("/"&A10,B$2:B$7)))*(LEFT(B$2:B$7,FIND("(",B$2:B$7)-1)))
2020	=SUMPRODUCT(ISNUMBER((FIND("/"&A11,B$2:B$7)))*(LEFT(B$2:B$7,FIND("(",B$2:B$7)-1)))
2021	=SUMPRODUCT(ISNUMBER((FIND("/"&A12,B$2:B$7)))*(LEFT(B$2:B$7,FIND("(",B$2:B$7)-1)))
2022	=SUMPRODUCT(ISNUMBER((FIND("/"&A13,B$2:B$7)))*(LEFT(B$2:B$7,FIND("(",B$2:B$7)-1)))

 

[78459430]

Here is also the Mini-sheet. Thanks.

Book6
	A	B
1	Extras	$8,000.00
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	$2,000.00
7		$189,845 (check 2/1/2022)
8		$15 (check 2/11/2022)
9
10		Result
11	2019	#VALUE!
12	2020	#VALUE!
13	2021	#VALUE!
14	2022	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B11:B14	B11	=SUMPRODUCT(ISNUMBER((FIND("/"&A11,B$2:B$8)))*(LEFT(B$2:B$8,FIND("(",B$2:B$8)-1)))

 

[Peter_SSs]

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))

 

[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))

GENIUS! Love Excel and its endless possibilities, I wish I would know how to use it fully
Thank you thank you!!!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[78459430]

One more question... what if I need to SUM only cells (on column B) that contains a specific text (for example CHECK).

Thanks.

 

[Peter_SSs]

what if I need to SUM only cells (on column B) that contains a specific text (for example CHECK).

Like this? (If this is not it, please provide sample data and expected results with XL2BB.)

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		245,013
Sum

Cell Formulas
Range	Formula
B11	B11	=LET(f,FILTER(B1:B8,ISNUMBER(SEARCH("check",B1:B8)),"0 "),SUM(LEFT(f,FIND(" ",f))+0))