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.

=SUMPRODUCT(--(A1:A100=--"2005-10-11"),--(B1:B100="John"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SE" wrote in message
...
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.

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.

SE,
let us assume that you want to count the number of John's on
10-11-2005. Consider that a separate cell contains the Person (e.g. F1
contains John) and another separate cell contains the date (e.g. G1
contains 10-11-2005). Now, if we want to put this formula in H1,

H1: =SUM(IF($A$1:$A$20=G1, 1, 0)*IF($C$1:$C$20=F1, 1, 0))

This is an *array* formula. To work you must use Shift+Ctrl+Enter. If
you do not do the key combination correctly, select the formula cell,
click on the formula bar and repeat the key combination. When finished
you will see brackets around the formula in the formula bar.

HTH
Kostis Vezerides

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.

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.

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.