counta grater than 40

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
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?
 

Some videos you may like

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
Joined
Aug 5, 2003
Messages
12,251
Hi bmuddy2,

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

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

Denis
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279
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.
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279

ADVERTISEMENT

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
Joined
Aug 5, 2003
Messages
12,251
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
Joined
Nov 1, 2005
Messages
279
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top