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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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
 

Forum statistics

Threads
1,141,916
Messages
5,709,327
Members
421,629
Latest member
RLRobinson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top