counta grater than 40

bmuddy2

Active Member
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?

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

SydneyGeek

MrExcel MVP
Hi bmuddy2,

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

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

Denis

sailepaty

Active Member
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.

thanks Denis

works just fine.

sailepaty

Active Member

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

MrExcel MVP
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

Active Member
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

Replies
17
Views
122
Replies
2
Views
49
Replies
3
Views
47
Replies
3
Views
153
Replies
5
Views
95