stuck with problem

[suramyatyagi]

2754
2785
2742
2709
73
0
2636
2719
2696.66425
2712.02986
2662.09163
2595.82745
60.5020826
0
2500.75275
2652.48813
2709
73
0
2636
2719
2696.66425
2712.02986
2662.09163 But I only want to count the absolute numbers that are above 2000 not decimals not numbers below 1000 , so the count fro this problem must be 9 .Need help in solving this

<tbody>
</tbody><colgroup><col></colgroup>

 

[MARZIOTULLIO]

	a​	b​	c​
1​	2754​
2​	2785​		9​
3​	2742​
4​	2709​
5​	73​
6​	0​
7​	2636​
8​	2719​
9​	2696.664​
10​	2712.03​
11​	2662.092​
12​	2595.827​
13​	60.50208​
14​	0​
15​	2500.753​
16​	2652.488​
17​	2709​
18​	73​
19​	0​
20​	2636​
21​	2719​
22​	2696.664​
23​	2712.03​
24​	2662.092​

<tbody>
</tbody>

c2=SUMPRODUCT((A1:A24>200)*(A1:A24-INT(A1:A24)=0))

 

[gallen]

Hello and welcome. I think I've understand I've done this user defined function, tested it and it works.

Add a module to your VBA environment and paste this Function:

Function COUNTOVER2K(Target As Range) As Long
    Dim cell As Range
    Dim l As Long
    For Each cell In Target
        If Int(Val(cell)) = Val(cell) And Val(cell) > 2000 Then l = l + 1
    Next cell
    COUNTOVER2K = l
End Function

To use the function, assuming the data you provided is in cells A2:A24 then put this in any cell not in that range:

=COUNTOVER2K(A2:A24)

Hope this helps

 

[suramyatyagi]

	a​	b​	c​
1​	2754​
2​	2785​		9​
3​	2742​
4​	2709​
5​	73​
6​	0​
7​	2636​
8​	2719​
9​	2696.664​
10​	2712.03​
11​	2662.092​
12​	2595.827​
13​	60.50208​
14​	0​
15​	2500.753​
16​	2652.488​
17​	2709​
18​	73​
19​	0​
20​	2636​
21​	2719​
22​	2696.664​
23​	2712.03​
24​	2662.092​

<tbody>
</tbody>

c2=SUMPRODUCT((A1:A24>200)*(A1:A24-INT(A1:A24)=0))

Thanks a lot really appreciate it

 

[suramyatyagi]

Hello and welcome. I think I've understand I've done this user defined function, tested it and it works.

Add a module to your VBA environment and paste this Function:

Function COUNTOVER2K(Target As Range) As Long
    Dim cell As Range
    Dim l As Long
    For Each cell In Target
        If Int(Val(cell)) = Val(cell) And Val(cell) > 2000 Then l = l + 1
    Next cell
    COUNTOVER2K = l
End Function

To use the function, assuming the data you provided is in cells A2:A24 then put this in any cell not in that range:

=COUNTOVER2K(A2:A24)

Hope this helps

Thanks a lot really appreciate it

 

[suramyatyagi]

Thanks a lot really appreciate it

 

[MARZIOTULLIO]

post 2

i wrong formula sorry

c2=MATR.SOMMA.PRODOTTO((A1:A24>2000)*(A1:A24-INT(A1:A24)=0))

 

[suramyatyagi]

for me the earlier formula worked perfectly fine , thanks again . Can you help me out with the logic behind the formula which you used earlier . It will really help me out in future