Formula to sum up the dates
 

I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

Formula to sum up the dates
 

Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A * * * * * * * * * * * * * * * * * B
Jan 10, 2010 * * * * * * * *John C
Jan 12, 2010 * * * * * * * *Jason B
Jan 13, 2010 * * * * * * * *John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

Formula to sum up the dates
 

"Kimti" wrote:
I have column A with the date and column B
with the name of person assigned for the task.
I would like to count how many tasks assigned
to any particular person for whole month based
on column A dates.

Suppose that is in rows 1:1000 in Sheet1, and in Sheet2, you have the list
of employees in column A starting in A2 and the 12 months in B1:M1. The
months should be entered as the first date of each month
(1/1/2010,2/1/2010,etc) formatted with the Custom format "mmm" without quotes
or any other format of your choice.

Put the following formula into B2 and copy across through M2, then copy
B2:M2 down for all employess:

=SUMPRODUCT((MONTH(Sheet1!$A$1:$A$1000)=MONTH(B$1) )
*(Sheet1!$B$1:$B$1000=$A2))

The "*" acts as AND in this context. We cannot use AND() directly.


----- original message -----


"Kimti" wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

Formula to sum up the dates
 

Thank you for the quick response. I have the dates in column A and names in
column B. Employees are entering their names and dates as they are assigned
the tasks. Ther are other formulas in place, so I don't want to change the
date columns to the rows. Please provide further help to work with columns
not the rows.

Thanks,

Kimti

"Joe User" wrote:

"Kimti" wrote:
I have column A with the date and column B
with the name of person assigned for the task.
I would like to count how many tasks assigned
to any particular person for whole month based
on column A dates.

Suppose that is in rows 1:1000 in Sheet1, and in Sheet2, you have the list
of employees in column A starting in A2 and the 12 months in B1:M1. The
months should be entered as the first date of each month
(1/1/2010,2/1/2010,etc) formatted with the Custom format "mmm" without quotes
or any other format of your choice.

Put the following formula into B2 and copy across through M2, then copy
B2:M2 down for all employess:

=SUMPRODUCT((MONTH(Sheet1!$A$1:$A$1000)=MONTH(B$1) )
*(Sheet1!$B$1:$B$1000=$A2))

The "*" acts as AND in this context. We cannot use AND() directly.


----- original message -----


"Kimti" wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

Formula to sum up the dates
 

Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti

"Per Jessen" wrote:

Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.

Formula to sum up the dates
 

"Kimti" wrote:
I have the dates in column A and names in
column B. Employees are entering their names
and dates as they are assigned the tasks. Ther
are other formulas in place, so I don't want to
change the date columns to the rows.
Please provide further help to work with columns
not the rows.

It is difficult to make specific suggestions about spreadsheet design at
arm's-length. If you wish, you can send me your Excel file. Send it as an
attachment to joeu2004 "at" hotmail.com.

However, I think my original suggestion should meet your needs. Employees
can modify the columnar data in Sheet1. You can put the summary formulas
into Sheet2.

Regarding Sheet2, I had suggested that the employee names be in a column,
and the tasks per month go along the row.

If you are saying that you would prefer to put the employee names along the
row and tasks per month in the columns, that should be a relatively simple
change, to wit:

=SUMPRODUCT((MONTH(Sheet1!$A$1:$A$1000)=MONTH($A2) )
*(Sheet1!$B$1:$B$1000=B$1))

assuming that the months (dates) are in A2:A13, and the employee names (11)
are in B1:L1.

If you are concerned about employees inadvertently altering Sheet2, that can
be addressed in any one of several ways, depending on your requirements.

Hope this helps. If not, but you think I'm on the right track, again, feel
free to send me your Excel file in email.


----- original message -----

"Kimti" wrote:
Thank you for the quick response. I have the dates in column A and names in
column B. Employees are entering their names and dates as they are assigned
the tasks. Ther are other formulas in place, so I don't want to change the
date columns to the rows. Please provide further help to work with columns
not the rows.

Thanks,

Kimti

"Joe User" wrote:

"Kimti" wrote:
I have column A with the date and column B
with the name of person assigned for the task.
I would like to count how many tasks assigned
to any particular person for whole month based
on column A dates.

Suppose that is in rows 1:1000 in Sheet1, and in Sheet2, you have the list
of employees in column A starting in A2 and the 12 months in B1:M1. The
months should be entered as the first date of each month
(1/1/2010,2/1/2010,etc) formatted with the Custom format "mmm" without quotes
or any other format of your choice.

Put the following formula into B2 and copy across through M2, then copy
B2:M2 down for all employess:

=SUMPRODUCT((MONTH(Sheet1!$A$1:$A$1000)=MONTH(B$1) )
*(Sheet1!$B$1:$B$1000=$A2))

The "*" acts as AND in this context. We cannot use AND() directly.


----- original message -----


"Kimti" wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

Formula to sum up the dates
 

Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti



"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You *can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A * * * * * * * * * * * * * * * * * B
Jan 10, 2010 * * * * * * * *John C
Jan 12, 2010 * * * * * * * *Jason B
Jan 13, 2010 * * * * * * * *John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Formula to sum up the dates
 

It gave me "#value" error. Please let me know what set up do I need to do to
clear this error.

Thanks,
Kimti

"Per Jessen" wrote:

Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti



"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.

Formula to sum up the dates
 

Well, just a guess, if you changed the range, verify that both ranges
are the same size.


Hopes this helps.
....
Per

On 8 Mar., 13:44, Kimti wrote:
It gave me "#value" error. Please let me know what set up do I need to do to
clear this error.

Thanks,
Kimti



"Per Jessen" wrote:
Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti

"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You *can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A * * * * * * * * * * * * * * * * * B
Jan 10, 2010 * * * * * * * *John C
Jan 12, 2010 * * * * * * * *Jason B
Jan 13, 2010 * * * * * * * *John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Formula to sum up the dates
 

Hi Per,

I checked the range for both sides and they are same.

"Per Jessen" wrote:

Well, just a guess, if you changed the range, verify that both ranges
are the same size.


Hopes this helps.
....
Per

On 8 Mar., 13:44, Kimti wrote:
It gave me "#value" error. Please let me know what set up do I need to do to
clear this error.

Thanks,
Kimti



"Per Jessen" wrote:
Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti

"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.

Formula to sum up the dates
 

You've got text somewhere in the range Sheet1!$A$2:$A$100
If you're struggling to find it, try =ISTEXT(Sheet1!A2) and copy down.
--
David Biddulph


"Kimti" wrote in message
...
It gave me "#value" error. Please let me know what set up do I need to do
to
clear this error.

Thanks,
Kimti

"Per Jessen" wrote:

Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would
like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti



"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person
assigned
for the task. I would like to count how many tasks assigned to any
particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about
80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.

Formula to sum up the dates
 

Hi Per,

It worked ok now, there was wrong format for the date in one cell.

Thank you very much for your help.

Kimti

"Kimti" wrote:

Hi Per,

I checked the range for both sides and they are same.

"Per Jessen" wrote:

Well, just a guess, if you changed the range, verify that both ranges
are the same size.


Hopes this helps.
....
Per

On 8 Mar., 13:44, Kimti wrote:
It gave me "#value" error. Please let me know what set up do I need to do to
clear this error.

Thanks,
Kimti



"Per Jessen" wrote:
Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN C"))
But this did not work, please provide further help.

Thanks,

Kimti

"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti wrote:
I have column A with the date and column B with the name of person assigned
for the task. I would like to count how many tasks assigned to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.

Formula to sum up the dates
 

To be more accurate, it wasn't that you had a date displayed in the wrong
format, as Excel doesn't care which format is used for display. The problem
was that the cell had TEXT instead of an Excel date.
--
David Biddulph


"Kimti" wrote in message
...
Hi Per,

It worked ok now, there was wrong format for the date in one cell.

Thank you very much for your help.

Kimti

"Kimti" wrote:

Hi Per,

I checked the range for both sides and they are same.

"Per Jessen" wrote:

Well, just a guess, if you changed the range, verify that both ranges
are the same size.


Hopes this helps.
....
Per

On 8 Mar., 13:44, Kimti wrote:
It gave me "#value" error. Please let me know what set up do I need
to do to
clear this error.

Thanks,
Kimti



"Per Jessen" wrote:
Thanks for your reply.

The sheet reference need to be just before the cell reference:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=1),--(Sheet1!$B$2:$B
$100="John C"))

---
Per

On 7 Mar., 23:15, Kimti wrote:
Thank you Per, this worked on the same sheet in different cell. I
would like
to have this summary on different sheet. I tried following:

=SUMPRODUCT(SHEET1!(MONTH($A$2:$A$100)=1),SHEET1!( $B$2:$B100="JOHN
C"))
But this did not work, please provide further help.

Thanks,

Kimti

"Per Jessen" wrote:
Hi

Look at this:

=SUMPRODUCT(--(MONTH($A$2:$A$100)=1),--($B$2:$B$100="John C"))

You can change the second criteria ("John C") to a cell
reference if
you have list with your 11 employees which you want to use.

Regards,
Per

On 7 Mar., 21:59, Kimti
wrote:
I have column A with the date and column B with the name of
person assigned
for the task. I would like to count how many tasks assigned
to any particular
person for whole month based on column A dates.

A B
Jan 10, 2010 John C
Jan 12, 2010 Jason B
Jan 13, 2010 John C

John C was assigned two tasks in Month of Jan. Usally I have
about 80 tasks
for 11 employees per month.

Thank you in advance for your help.

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

.