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]

Is vba acceptable?

If "yes" then see if this user-defined function would do what you want.

Function SumInt(rng As Range) As Long
  Dim c As Range
  Dim v As Variant
  
  For Each c In rng
    v = c.Value
    If IsNumeric(v) Then
      If Int(v) = v And InStr(1, c.Text, "%") = 0 Then
        SumInt = SumInt + v
      End If
    End If
  Next c
End Function

In my sample below col D is as post #9.
In col E, cell E7 appears as an integer but actually contains 100.32 so is excluded from the sum.
In col F, cell F7 is again 100.32 and cell F10 appears as an integer but is actually a text value of 100 so is also excluded from the sum.

Lacan.xlsm
	D	E	F
3	SALES A	SALES A	SALES A
4	PYG MTH %	PYG MTH %	PYG MTH %
5	-0.031431276	-0.031431276	-0.031431276
6	OBJECTIVO	OBJECTIVO	OBJECTIVO
7	1000	1000	1000
8	-0.6789	-0.6789	-0.6789
9	0.1	0.1	0.1
10	1000	1000	1000
11	0.39	0.39	0.39
12	7000%	7000%	7000%
13	1000	1000	1000
14	3000	2000	1000
Sheet1

Cell Formulas
Range	Formula
D14:F14	D14	=SUMINT(D5:D13)

 

[felixstraube]

Here is another option with formula:

You still didn't responde how you want to handle the values that are for example 1000.54. Truncate them? Round them? Ignore them altogether?

Anyway, here are all three options:
CELL if volatile, and it behaves a little weird, so you may need to force calculate (by pressing F9 or editing a cell with formula and hitting enter) if you change data before the calculation reflects the change.

Book6.xlsx
	A	B	C	D	E	F	G	H	I
1
2						Truncate	Round	Only Integers ignoring values that have decimals
3				SALES A		3000	3001	2000
4				PYG MTH %
5				-0.031431276
6				OBJECTIVO
7				1000
8				-67.9%
9				10%
10				1000
11				0.39
12				7000%
13				1000.65
14
Sheet8

Cell Formulas
Range	Formula
F3	F3	=LET(d, D3:D1000, format, BYROW(d, LAMBDA(x, CELL("format", x))), notPercentage, NOT(ISNUMBER(SEARCH("p", format))), v, IFERROR(VALUE(d), 0)*notPercentage, SUM(TRUNC(v, 0)) )
G3	G3	=LET(d, D3:D1000, format, BYROW(d, LAMBDA(x, CELL("format", x))), notPercentage, NOT(ISNUMBER(SEARCH("p", format))), v, IFERROR(VALUE(d), 0)*notPercentage, SUM(ROUND(v, 0)) )
H3	H3	=LET(d, D3:D1000, format, BYROW(d, LAMBDA(x, CELL("format", x))), notPercentage, NOT(ISNUMBER(SEARCH("p", format))), v, IFERROR(VALUE(d), 0)*notPercentage, isInt, v=INT(v), SUM(ROUND(v, 0)*isInt) )

 

[Lacan]

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?

Dear @Peter_SSs

Yes please, vba is acceptable.
Thank you very much!

 

[Peter_SSs]

Yes please, vba is acceptable.
Thank you very much!

So, did you try the vba suggestion in post 11?