How to sum columns only intregral numbers despite that have rows with text, decimals and percentual values?

[Lacan]

Hello Guys,
Hope you are OK!

Have 2 largest columns however in the example below that are only a sample
How to sum columns only intregral numbers despite that have rows with text, decimals and percentual values?
Thanks for all the help!!!

Painel FARMÁCIAS_21.JUL.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3				SALES A			SALES B
4				PYG MTH %			Var MS (p.p.) PY MTH
5				-3%			-0,18
6				OBJECTIVO			1 100
7				1 000
8							-0,55
9				10%			500
10				1 869			GOAL
11				498
12			TOTAL			TOTAL
13
14
15
Folha1

 

[felixstraube]

What would the expected result be? Or what are you trying to add up? 1000 in the first column? and 1100 and 500 in the second?
Because the other values have decimals.

			SALES A			SALES B
			PYG MTH %			Var MS (p.p.) PY MTH
			-0.031431276			-0.178044149
			OBJECTIVO			1100
			1000
						-0.55
			0.1			500
			1869.164			GOAL
			497.9523
		TOTAL			TOTAL

 

[Asbestos_Jen]

Is this what you're looking for?

Book1
	C	D	E	F	G
3		SALES A			SALES B
4		PYG MTH %			Var MS (p.p.) PY MTH
5		-3%			-18%
6		OBJECTIVO			1100
7		1000
8					-0.55
9		10%			500
10		1869.164			GOAL
11		497.9523
12	TOTAL	3367.116		TOTAL	1600
Sheet1

Cell Formulas
Range	Formula
D12,G12	D12	=SUMIF(D5:D11,">="&1)

 

[Lacan]

Dear @felixstraube and @Asbestos_Jen

Thank you very much for the help.
To clarify even better would like for each one, "Column D" Sum from range D3:D11 and in "Column G" Sum from range G3:G11.

@Asbestos_Jen thats correct the results expected of both sums, however after test the formula just notice that the total amount still changes when other values decimal and percentual changes.
All the help is appreciated.
Thank you very much!

Painel FARMÁCIAS_21.JUL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3				SALES A					SALES B
4				PYG MTH %					Var MS (p.p.) PY MTH
5				-3%					-0,18
6				OBJECTIVO					1 100
7				1 000
8									-0,55
9				10%					500
10				1 869					GOAL
11				498
12			TOTAL	3367	<= Expected Results			TOTAL	1600	<= Expected Results
13
14
15
16
Folha1

 

[Asbestos_Jen]

Are the green cells calculated by a formula, or are they entered manually?
If they are calculated, you could round them in those formulas. I've put helper columns in E and H to round the values before summing them in row 12.

2024-08-15.xlsm
	C	D	E	F	G	H
3		SALES A			SALES B
4		PYG MTH %			Var MS (p.p.) PY MTH
5		-3%	no		-18%	no
6		OBJECTIVO	#VALUE!		1100	1100
7		1000	1000			no
8			no		-0.55	no
9		10%	no		500	500
10		1869.164	1869		GOAL	#VALUE!
11		497.9523	498			no
12	TOTAL	3367.116	3367	TOTAL	1600	1600
Sheet1

Cell Formulas
Range	Formula
H5:H12,E5:E12	E5	=IF(D5>=1,ROUND(D5,0),"no")
D12,G12	D12	=SUMIF(D5:D11,">="&1)

 

[felixstraube]

So you want to sum all the integer values? without decimals?
You shared this in post #4 without showing the decimals:

SALES A
PYG MTH %
-0.031431276
OBJECTIVO
1000
0.1
1869.164
497.9523
3367.1163	<= Expected Results

What would be the condition to add a number? That is greater than 1? Or you want to sum the integer part of each number after truncating it?
Do you want the decimal part to be summed up and in the end result?

Here are some options, which one would be better?:

Book1
	A	B	C	D	E	F	G	H
1
2
3				SALES A	Greater than 1	Truncated	Rounddown	Round
4				PYG MTH %
5				-0.031431276	0	0	0	0
6				OBJECTIVO	0	0	0	0
7				1000	1000	1000	1000	1000
8					0	0	0	0
9				0.1	0	0	0	0
10				1869.164	1869.164	1869	1869	1869
11				497.9523	497.9523	497	497	498
12			TOTAL	3367.1163	3367.1163	3366	3366	3367
Sheet2

Cell Formulas
Range	Formula
E5:E11	E5	=(N(D5)>1)*N(D5)
F5:F11	F5	=TRUNC(N(D5), 0)
G5:G11	G5	=ROUNDDOWN(N(D5), 0)
H5:H12	H5	=ROUND(N(D5), 0)
E12:G12	E12	=SUM(E5:E11)

 

[Lacan]

So you want to sum all the integer values? without decimals?
You shared this in post #4 without showing the decimals:

SALES A
PYG MTH %
-0.031431276
OBJECTIVO
1000
0.1
1869.164
497.9523
3367.1163	<= Expected Results

What would be the condition to add a number? That is greater than 1? Or you want to sum the integer part of each number after truncating it?
Do you want the decimal part to be summed up and in the end result?

Here are some options, which one would be better?:

Book1
	A	B	C	D	E	F	G	H
1
2
3				SALES A	Greater than 1	Truncated	Rounddown	Round
4				PYG MTH %
5				-0.031431276	0	0	0	0
6				OBJECTIVO	0	0	0	0
7				1000	1000	1000	1000	1000
8					0	0	0	0
9				0.1	0	0	0	0
10				1869.164	1869.164	1869	1869	1869
11				497.9523	497.9523	497	497	498
12			TOTAL	3367.1163	3367.1163	3366	3366	3367
Sheet2

Cell Formulas
Range	Formula
E5:E11	E5	=(N(D5)>1)*N(D5)
F5:F11	F5	=TRUNC(N(D5), 0)
G5:G11	G5	=ROUNDDOWN(N(D5), 0)
H5:H12	H5	=ROUND(N(D5), 0)
E12:G12	E12	=SUM(E5:E11)

Dear @Asbestos_Jen & @felixstraube

Thank you very much Guys.
To clarify even better.
As said initially the table showned is just a simple sample. In my original table column has more then 400 rows.
Thus would like to have a formula that covers all this range through text cells to sum all the integer values without decimals values (negative or positive) and without percentual values (negative or positive).
Think it's even clearer now.
Hope you could help, Thank you very much!
*Sample attached

Painel FARMÁCIAS_21.JUL.xlsx
	A	B	C	D	E	F	G
1
2
3				SALES A
4				PYG MTH %
5				-3%
6				OBJECTIVO
7				1 000
8				-0,68
9				10%
10				1 869
11				498
12			TOTAL	3367	<= Expected Results
13
14
Folha1 (2)

 

[felixstraube]

I still have questions.
When you share your table in post #7, you show again only the integer part but the cells have this values stored:

Book1
	A	B	C	D	E
1
2
3				SALES A
4				PYG MTH %
5				-0.03143128
6				OBJECTIVO
7				1000
8				-0.6789
9				0.1
10				1869.164
11				497.9523
12			TOTAL	3367.1163	<= Expected Results
Sheet1

When you say you want to add the integer parts, what do you mean? For example for these 2 values:

Original Value	Trucated	Rounded
1869.164	1869	1869
497.9523	497	498

And also the expected result you share has decimals. From your post I understood that you don't want them.
Can you clarify?

 

[Lacan]

I still have questions.
When you share your table in post #7, you show again only the integer part but the cells have this values stored:

Book1
	A	B	C	D	E
1
2
3				SALES A
4				PYG MTH %
5				-0.03143128
6				OBJECTIVO
7				1000
8				-0.6789
9				0.1
10				1869.164
11				497.9523
12			TOTAL	3367.1163	<= Expected Results
Sheet1

When you say you want to add the integer parts, what do you mean? For example for these 2 values:

Original Value	Trucated	Rounded
1869.164	1869	1869
497.9523	497	498

And also the expected result you share has decimals. From your post I understood that you don't want them.
Can you clarify?

Sorry @felixstraube

For family reasons have been away from my PC.
Now back, just made a small changes in table and data to make it clear even more.
As said initially the table showned is just a simple sample. In my original table column has more then 400 rows.
Would like to have a formula that covers all this range through text cells to sum all only the integer values without decimals values (negative or positive) and without percentual values (negative or positive).
Hope you could help.
Again, thank you very much!

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G
1
2
3				SALES A
4				PYG MTH %
5				-3,143%
6				OBJECTIVO
7				1000
8				-0,6789
9				10%
10				1000
11				0,39
12				7000%
13				1000
14			TOTAL	3000	<= Expected Results
15
16
25-08-2024

 

[Peter_SSs]

I think that you would need vba to do that since the cell that shows as 7000% actually just contains the integral number 70 and the % sign only appears due to the cell formatting, not due to the underlying number. Is vba acceptable?