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

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

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

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

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

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

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

I will send a file if you want to.

No thanks.

Imda14u wrote:
>
> 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.)

Imda14u wrote:
>
> I will send a file if you want to.

--

Dave Peterson