Hi Roger,
I agree, the cell references is a more robust solution. What I often find
though is that by trying to convince them to do things differently than they
suggest is that the question gets lost, and we lose it. Because of that, I
tend to keep to their method even though I may 'know' better, unless of
course it is just plain wrong ;-).
Regards
Bob
"Roger Govier" wrote in message
...
Hi Bob
Thanks for the "heads up"
I was hoping the OP would take on board the suggestion about using cell
references.
I was being lazy in entering the formula.
I normally don't trust anything other than DATE(2005,10,11) as the test.
Regards
Roger Govier
Bob Phillips wrote:
Hi Roger,
The first of these will not work if A contains real dates as you are
comparing strings to a date. You either need to use the DATE function, or
coerce it to a date as I do
=SUMPRODUCT(--($A$2:$A$100=--"10/11/2005"),--($C$2:$C$100="John"))
I also use an ISO standard data yyyy-mm-dd to overcome internalisation
issues.
Regards
Bob
"Roger Govier" wrote in message
. ..
Hi
One way
=SUMPRODUCT(--($A$2:$A$100="10/11/2005"),--($C$2:$C$100="John"))
Change ranges to suit, but ensure that they are of equal length.
It would be better to put the values bing looked up in cells e.g. put
Date required in G1 and Name in H1
=SUMPRODUCT(--($A$2:$A$100=G1),--($C$2:$C$100=H1))
then the formula will work for any combination of dates and names.
Regards
Roger Govier
SE wrote:
I am a novice with excel and I need assistance creating a formula that
will
count the number of people that appear by date. How can I ask excel to
complete the following calculation: If the date is "10/11/05" count
each
occurrence of "John"? Here is my data sample:
Completion Date Contact Type Person
10-11-2005 Letter John
10-12-2005 Mail Paul
10-11-2005 Mail Richard
10-11-2005 Letter Paul
10-12-2005 Letter Paul
10-12-2005 Letter Paul
10-12-2005 Letter Paul
10-12-2005 Mail Richard
10-12-2005 Letter Paul
10-11-2005 Mail Richard
10-12-2005 Mail Jim
10-12-2005 Letter Paul
10-11-2005 Letter John
10-11-2005 Letter John
10-11-2005 Letter David
10-12-2005 Mail Jim
10-12-2005 Mail David
10-11-2005 Letter Paul
10-11-2005 Mail John
10-11-2005 Mail Jim
Thanks for any assistance you can provide.
|