Formula to sum up the dates

[Kimti]

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.

 

[Per Jessen]

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

 

[Joe User]

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]

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

 

[Kimti]

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

 

[Joe User]

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

 

[Per Jessen]

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

 

[Kimti]

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]

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


Hopes this helps.
....
Per

 

[Kimti]

Hi Per,

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

 

[David Biddulph]

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.

 

[Kimti]

Hi Per,

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

Thank you very much for your help.

Kimti

 

[David Biddulph]

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.