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

 

[Peter_SSs]

Cheers. glad it is sorted.

 

[Lacan]

Cheers. glad it is sorted.

Dear @Peter_SSs

After test it in my workfile just notice a simple gap.
The sum only occurs when cells have numeric values and not in the other ones wich contains formulas that return numeric values.
Can you please give a hand?

Thanks again!!!

 

[Peter_SSs]

.. not in the other ones wich contains formulas that return numeric values.

Are you sure the formulas are returning numeric values?
Works for me with formulas.

Lacan.xlsm
	C	D	E
1
2
3		SALES A	SALES A
4		PYG MTH %	PYG MTH %
5		-3.143%	-3.143%
6		OBJECTIVO	OBJECTIVO
7		1000	1000
8		-0.6789	-0.6789
9		10%	10%
10		1000	1000
11		0.39	0.39
12		7000%	7000%
13		1000	1000
14	TOTAL	3000	3000
Sheet2

Cell Formulas
Range	Formula
D3:D13	D3	=E3
D14:E14	D14	=SomaINT(D5:D13)

Perhaps you could provide some sample data that demonstrates the issue for you?

 

[Lacan]

Are you sure the formulas are returning numeric values?
Works for me with formulas.

Lacan.xlsm
	C	D	E
1
2
3		SALES A	SALES A
4		PYG MTH %	PYG MTH %
5		-3.143%	-3.143%
6		OBJECTIVO	OBJECTIVO
7		1000	1000
8		-0.6789	-0.6789
9		10%	10%
10		1000	1000
11		0.39	0.39
12		7000%	7000%
13		1000	1000
14	TOTAL	3000	3000
Sheet2

Cell Formulas
Range	Formula
D3:D13	D3	=E3
D14:E14	D14	=SomaINT(D5:D13)

Perhaps you could provide some sample data that demonstrates the issue for you?

Dear @Peter_SSs

After test it would like to make a simple change.
Have to turn back, in your sum through VBA Code can you please add also decimal values. Have also to sum them.
All the rest are OK not "%" and not "text"!
Sorry! Thank you very much!

 

[Peter_SSs]

What happened to the issue you raised in post #22?

Is this what you want for the new request?

Function SomaNotPercent(rng As Range) As Double
  Dim c As Range

  For Each c In rng
    If IsNumeric(c.Value) And InStr(1, c.Text, "%") = 0 Then SomaNotPercent = SomaNotPercent + c.Value
  Next c
End Function

Lacan.xlsm
	C	D	E
1
2
3		SALES A	SALES A
4		PYG MTH %	PYG MTH %
5		-3.143%	-3.143%
6		OBJECTIVO	OBJECTIVO
7		1000	1000
8		-0.6789	-0.6789
9		10%	10%
10		1000	1000
11		0.39	0.39
12		7000%	7000%
13		1000	1000
14	TOTAL	2999.7111	2999.7111
Sheet2

Cell Formulas
Range	Formula
D3:D13	D3	=E3
D14:E14	D14	=SomaNotPercent(D5:D13)

 

[Lacan]

Brilliant @Peter_SSs
Will use both code´s each one for each column.
Thank you very much!!!

 

[Peter_SSs]

You're welcome, but did this issue just go away?

The sum only occurs when cells have numeric values and not in the other ones wich contains formulas that return numeric values.

 

[Lacan]

You're welcome, but did this issue just go away?

After send the question just notice that the numeric values returned by the formulas were really decimal values.
Because of the cells formatt they were "hiding" the decimal places.
Thanks again @Peter_SSs
All the best!

 

[Peter_SSs]

After send the question just notice that the numeric values returned by the formulas were really decimal values.
Because of the cells formatt they were "hiding" the decimal places.

OK, thanks for explaining.