calculation based on the range you indicate in another cell

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

I use Excel for basic project management. I use formula below to
calculate hours spent on a specific task of a project. The range is
B22 to B2058, A43 is the project name, BC15 is the task name. B column
is the hour, H column is the task.
=SUMPRODUCT((worksheetname!$B$22:$B$2058=$A43)*(worksheetname!$H$22:$H
$2058=$BC$15)

I noticed that there are recurring projects that I'd like to count
only the recent change so the range needs to be dynamic. I'm thinking
that I can have another cell on the same row that I indicate the date
after to track. The formula would use that date and find the starting
cell of that date (B22 would become something else) and use that as
the begin range and count the total hours spent?

Thank you so much for your help,
 
Hi

Assuming you had date in column I
Place the start date required in B1 and the end date in C1 then use
=SUMPRODUCT((worksheetname!$B$22:$B$2058=$A43)*
(worksheetname!$H$22:$H$2058=$BC$15)*
(worksheetname!$I$22:$I$2058>=$B$1)*
(worksheetname!$I$22:$I$2058<=$C$1)*
range containg the hours)

Better still, create a Pivot Table and then you have all the flexibilty you
want in reporting.

--
-------
Regards
Roger Govier

cpliu said:
I use Excel for basic project management. I use formula below to
calculate hours spent on a specific task of a project. The range is
B22 to B2058, A43 is the project name, BC15 is the task name. B column
is the hour, H column is the task.
=SUMPRODUCT((worksheetname!$B$22:$B$2058=$A43)*(worksheetname!$H$22:$H
$2058=$BC$15)

I noticed that there are recurring projects that I'd like to count
only the recent change so the range needs to be dynamic. I'm thinking
that I can have another cell on the same row that I indicate the date
after to track. The formula would use that date and find the starting
cell of that date (B22 would become something else) and use that as
the begin range and count the total hours spent?

Thank you so much for your help,

__________ Information from ESET Smart Security, version of virus
signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi

Assuming you had date in column I
Place the start date required in B1 and the end date in C1 then use
=SUMPRODUCT((worksheetname!$B$22:$B$2058=$A43)*
(worksheetname!$H$22:$H$2058=$BC$15)*
(worksheetname!$I$22:$I$2058>=$B$1)*
(worksheetname!$I$22:$I$2058<=$C$1)*
range containg the hours)

Better still, create a Pivot Table and then you have all the flexibilty you
want in reporting.
Click to expand...

Thanks for the help. I got numbers but they don't look right.

To dissect the whole formula in multiple lines:

=SUMPRODUCT((Tom!$B$2:$B$2000=$A8)* = identify the project name in B
column
(Tom!$I$2:$I$2000=$BC$20)* = identify the task name in I column
(Tom!$E$2:$E$2000)* = time spent on each task in E column
(Tom!$A$2:$A$2000>=AJ8)* = the start date in AJ8
(Tom!$A$2:$A$2000<=AK8)* = the end date in AK8
(Tom!$E$2:$E$2000) = the I column with time

What did I do wrong?

Thanks,
 
Back
Top