Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count With Criteria
I will send a file if you want to.
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count With Criteria
No thanks.
Imda14u wrote: I will send a file if you want to. -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if 2 criteria are met | Excel Worksheet Functions | |||
Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
count using criteria | Excel Discussion (Misc queries) | |||
How to count nos. on 3 criteria | Excel Worksheet Functions |