Count With Criteria
 

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

Count With Criteria
 

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

Roger wrote:

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

Count With Criteria
 

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

IE if I wanted to store the answer in a variable


--
RMPitcher
------------------------------------------------------------------------
RMPitcher's Profile: http://www.thecodecage.com/forumz/member.php?userid=499
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115086

Count With Criteria
 

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 wrote:

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

IE if I wanted to store the answer in a variable

--
RMPitcher
------------------------------------------------------------------------
RMPitcher's Profile: http://www.thecodecage.com/forumz/member.php?userid=499
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115086

--

Dave Peterson

Count With Criteria
 

Dave - Thanks a lot - worked a treat - Rog


--
RMPitcher
------------------------------------------------------------------------
RMPitcher's Profile: http://www.thecodecage.com/forumz/member.php?userid=499
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115086

Count With Criteria
 

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

Count With Criteria
 

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

Count With Criteria
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Imda14u wrote:

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

--

Dave Peterson

Count With Criteria
 

I've never seen this problem.

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

Imda14u wrote:

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

Count With Criteria
 

I will send a file if you want to.

Count With Criteria
 

No thanks.

Imda14u wrote:

I will send a file if you want to.

--

Dave Peterson

Count With Criteria
 

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

Imda14u wrote:

I will send a file if you want to.

--

Dave Peterson