Excel Count if

[SteveQL]

Hi Everyone
OK, I want to do a count of numbers in a column that meet a certain criteria. The criteria needs to be an inequality.
(for instance count the number of values in the column whose value is greater than or equal to 25.) The threshold value of the inequality needs to be based on a cell reference. The COUNTIF worksheet function will only work with an inequality if you place a value in the formula (i.e =COUNTIF(B1:B21,">=25)") rather than a cell reference. (It works fine if you want to count values that are equal to the value of the cell reference.)

I've been able to code a counter in VBA that works OK, but is there an Excel function that will do this for me? I've been through every one of my Excel Book and the helpfiles to no avail.

Any Thoughts??

Cheers!
Steve

 

[vjh]

Here's a 'cheating' answer... but it works - assuming that E1 is your cell reference with the value you're looking for.

=COUNTIF(B1:B21,CONCATENATE(">=",E1))

... vjh

 

[TonyJollans]

Hi Steve,

The criteria for COUNTIF is a string, made up how you like. vjh's solution is not a cheat, but it is more complex than it needs to be. All that's needed is ..

[blue][tt] =SUMIF(B1:B21,">="&E1)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

Or =COUNTIF(B1:B21,">="&E1) even

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[01Patrik]

Or...

=sumproduct((B1:B21>=E1)*1)

// Patrik

 

[TonyJollans]

Thank you, Ken. Don't know where that SUM came from.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[SteveQL]

You guys rock. THree people in the office, myself included spent hours trying to figure this one out. I feel silly after seeing your solutions.

Cheers!
Steve

 

[mmohawk]

I'm trying to have a formula check (column A) for a name and if it has the name it will give me the average of the numbers in (Column B). I'm very new to excel formulas. Thanks in advance.

Thanks, Mic

 

[SteveQL]

Mohawk,
I think what you want to do is easily handled with some nested formulas, however, I'm not exactly sure what you want. Do you want the average of the numbers in columnB where ColumnA has a certain value or do want to take the average of all the values in columnB if a certain value exists in ColumnA, or is the value you are looking for in columnA only in one cell like cell A1 for instance?

Cheers!
Steve

 

[01Patrik]

Something like


=if(countif(A:A,LOOKUPVALUE)>0,average(B:B),ELSE)

where LOOKUPVALUE is the value you're searching for, and ELSE is what's suppose to happen if the condition isn't true. If you want the cell to be bland, then enter "".

For future reference, please start a new thread when you have a new question. That way more people will see your question, and people who asked / answered the original question won't have to look in the already 'finished' thread.

// Patrik

 

[xlbo]

In this case, you need to do a conditional sum divided by a conditional count so

=SUMIF(LookupRange,LookupValue,SumRange)/Countif(LookupRange,LookupValue)

This would look something like:

=SUMIF($A$2:$A$1000,$A2,$B$2:$B$1000)/COUNTIF($A$2:$A$1000,$A2)

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[01Patrik]

Oh, I believe I misunderstood the question... Geoff is right (as usual)

// Patrik