stuck with problem

[suramyatyagi]

[TABLE="width: 87"]
<tbody>[TR]
[TD]2754[/TD]
[/TR]
[TR]
[TD]2785[/TD]
[/TR]
[TR]
[TD]2742[/TD]
[/TR]
[TR]
[TD]2709[/TD]
[/TR]
[TR]
[TD]73[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2636[/TD]
[/TR]
[TR]
[TD]2719[/TD]
[/TR]
[TR]
[TD]2696.66425[/TD]
[/TR]
[TR]
[TD]2712.02986[/TD]
[/TR]
[TR]
[TD]2662.09163[/TD]
[/TR]
[TR]
[TD]2595.82745[/TD]
[/TR]
[TR]
[TD]60.5020826[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2500.75275[/TD]
[/TR]
[TR]
[TD]2652.48813[/TD]
[/TR]
[TR]
[TD]2709[/TD]
[/TR]
[TR]
[TD]73[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2636[/TD]
[/TR]
[TR]
[TD]2719[/TD]
[/TR]
[TR]
[TD]2696.66425[/TD]
[/TR]
[TR]
[TD]2712.02986[/TD]
[/TR]
[TR]
[TD]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 [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[TD]

c​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

2754​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2785​

[/TD]
[TD][/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2742​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2709​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2636​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

2719​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2696.664​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

2712.03​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

2662.092​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

2595.827​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

60.50208​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

2500.753​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

2652.488​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

2709​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

2636​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

2719​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

2696.664​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

2712.03​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

2662.092​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[TD]

c​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

2754​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2785​

[/TD]
[TD][/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2742​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2709​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2636​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

2719​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2696.664​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

2712.03​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

2662.092​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

2595.827​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

60.50208​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

2500.753​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

2652.488​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

2709​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

2636​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

2719​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

2696.664​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

2712.03​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

2662.092​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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