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?

 

[Lacan]

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)

Dear @Peter_SSs

Hope you are ok.
Sorry for the delay.
Tested your suggestion however still not working for me. Wonder if is something about language translator...

What you think?
Thanks again my Friend.

 

[Peter_SSs]

still not working for me.

In what way is it not working?

Are any of the results in post #11 incorrect for the sample data given?

Can you give some new sample data with XL2BB and tell us what result you got with the post #11 function and what result(s) you would want and why?

 

[Lacan]

In what way is it not working?

Are any of the results in post #11 incorrect for the sample data given?

Can you give some new sample data with XL2BB and tell us what result you got with the post #11 function and what result(s) you would want and why?

Dear @Peter_SSs

Just to show have the VBA code accordingly and the sample of data.
Wonder what is missing?
Im listen carefully.
Thank you very much.


Function SomaInt(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
SomaInt = SomaInt + v
End If
End If
Next c
End Function

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	#NOME?	<= Expected Results
15
16
25-08-2024

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

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

I get a different result.
Try re-entering the formula in cell D14.

Function SomaInt(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
        SomaInt = SomaInt + v
      End If
    End If
  Next c
End Function

Lacan.xlsm
	A	B	C	D	E
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
Sheet2

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

 

[Peter_SSs]

Could be where you have the function code. Put it in a standard module.

 

[Lacan]

Could be where you have the function code. Put it in a standard module.

Perfect @Peter_SSs !!!
Only worked after add it in a standard module.
Thank you very much!!!!