Count With Criteria

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

If in range A1 to A10 there were a list of different peoples names - "John"
"Dave" etc
How do I count the number of numeric entries in range B1 to B10 for say
"John" only

I can do it with DCount and set the criteria accordingly but is there
another way please

Thankyou in anticipation
 
=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))
If in range A1 to A10 there were a list of different peoples names - "John"
"Dave" etc
How do I count the number of numeric entries in range B1 to B10 for say
"John" only

I can do it with DCount and set the criteria accordingly but is there
another way please

Thankyou in anticipation
Click to expand...
 
You could write a procedure that would loop through the rows and examine the
cells and increment a counter.

Or you could use:

Option Explicit
Sub testme()
Dim myCount As Long
myCount = Worksheets("SheetNameHere") _
.Evaluate("sumproduct(--(a1:a10=""john""),--isnumber(b1:b10))")
MsgBox myCount
End Sub
 
Dave Peterson schreef:
=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))
Click to expand...

It works great, what I don't understand are the double dashes.
I've tried without them and the formula fails.

Also, when instead of text "john" you refer to a cell with the text
"john" it seems that you must confirm the formula with ctrl+shift+enter.
When you fill the formula down to refer to another cell with name "dave"
you get the same result as for "john", not so however when confirmed
as array.

puzzles and questions....


greets,

Sybolt
 
Dave Peterson schreef:
=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))
Click to expand...

Another remark to your formula
It doen't seem to recalculate when key F9 is hit, where all other
formulas do.
Is this maybe caused by the dashes?

sybolt
 
I've never seen this problem.

Maybe you're seeing the correct results because your data isn't what you expect.
 
I have read some posts that say that some formulas won't recalculate--but those
posts don't limit the offending formulas to array/sumproduct formulas.

One suggested fix is to select all the cells
Edit|replace
what: =
with: =
replace all

It forces excel to see a change to each formula and re-evaluate it. And
sometimes can wake up excel's calculation engine.

(I've never experienced this in any of my work.)
 
Back
Top