Count With Criteria

[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

 

[Dave Peterson]

=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

 

[RMPitcher]

how would I write this in code instead of using it in the worksheet.

IE if I wanted to store the answer in a variabl

 

[Dave Peterson]

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

 

[RMPitcher]

Dave - Thanks a lot - worked a treat - Ro

 

[Imda14u]

Dave Peterson schreef:

=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))

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

 

[Imda14u]

Dave Peterson schreef:

=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))

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

 

[Dave Peterson]

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

I've never seen this problem.

Maybe you're seeing the correct results because your data isn't what you expect.

 

[Imda14u]

I will send a file if you want to.

 

[Dave Peterson]

No thanks.

I will send a file if you want to.

 

[Dave Peterson]

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.)