counta grater than 40

[bmuddy2]

hello

simple question.

in column V i have some cells with numbers ranging from 0 to 80.

i have two seperate things i need to do, first i need to look at column V and find how many cells contain a number grater than 40.

second i need to find the cells grater than 40 and add the part than is grater than 40.

for example 38 , 45 , 26 , 44 , 56 , 41

first result should be 4 cells contain a number grater than 40.
second result should be 16 , the result of 5 + 4 + 6 + 1

formula 1 in cell A1
formula 2 in cell A2

hope this explains it. any ideas?

 

[SydneyGeek]

Hi bmuddy2,

Question1:
=(COUNTIF(V:V,">40"))

Question2:
=SUMIF(V:V,">40")-40*COUNTIF(V:V,">40")

Denis

 

[sailepaty]

Let’s say that your data is in range V1:V100. So, you can try this.

A1= COUNTIF(V1:V100,”>40”)
A2 = SUMPRODUCT(--(V1:V100>40),--RIGHT(V1:V100,1))

I hope it helps.

 

[bmuddy2]

thanks Denis

works just fine.

 

[sailepaty]

second result should be 16 , the result of 5 + 4 + 6 + 1

You got 26 with Denis formula, is that what you was looking for?

 

[SydneyGeek]

You got 26 with Denis formula, is that what you was looking for?

I assumed that was the requirement, because the question mentioned the amount greater than 40.

If that was incorrect, sailepaty's post will do the job.

Denis

 

[sailepaty]

Hi Denis,

That one was my first thought but then the sentence I quoted in my first post changed my mind. So, that’s why I preferred to double check.

Thanks