complex function
 

Hi,

I have a workbook excel in which i have 2 sheets "overview" and "data".
"Overview" should display some results calculated from "data" sheet.
in "data" sheet i have a list of tasks done included time needed.

I want to sum all times spent (available on "data") to 1 task and to
display this result in "overview".

e.g :

Overview sheet :

Col A Col B
Planning
Analysis "myformula"
Development
Testing
....


Data sheet :

Col C Col E
Planning 00:12:00
Planning 01:34:21
Analysis 01:45:00
Planning 00:17:36
Analysis 20:40:00
....

in this case, "myformula" should return me : 01:45:00 + 20:40:00, so
22:25:00 as time spent on this task.

How can i do that (because i must scan all records) ?

thanks a lot,
Alain

complex function
 

Use SUMIF

Formula you need to write into cell B1 in Overview is
=SUMIF(Data!A:A,A1,Data!C:C)

then copy this down however far you need it

http://www.excel-ant.co.uk 'under construction




Alain R. wrote:
Hi,

I have a workbook excel in which i have 2 sheets "overview" and "data".
"Overview" should display some results calculated from "data" sheet.
in "data" sheet i have a list of tasks done included time needed.

I want to sum all times spent (available on "data") to 1 task and to
display this result in "overview".

e.g :

Overview sheet :

Col A Col B
Planning
Analysis "myformula"
Development
Testing
...


Data sheet :

Col C Col E
Planning 00:12:00
Planning 01:34:21
Analysis 01:45:00
Planning 00:17:36
Analysis 20:40:00
...

in this case, "myformula" should return me : 01:45:00 + 20:40:00, so
22:25:00 as time spent on this task.

How can i do that (because i must scan all records) ?

thanks a lot,
Alain

complex function
 

SUMPRODUCT will work VERY nicely here. Just adjust the ranges to suit
your data but make sure they are of equal rows! The first part of the
formula (Data!$A$1:$A$7=A1) will check column A in the Data sheet for
your desired value which is in cell A1 on the overview sheet. If it
finds it it will sum the amount in the data sheet in column be next to
it.

=SUMPRODUCT((Data!$A$1:$A$7=A1)*(Data!$B$1:$B$7))


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=566254

complex function
 

SUMIF will suffice here. In overview sheet B2

=SUMIF(data!C:C,A2,data!E:E)

copied down colum

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56625

complex function
 

...oh and I should add, as you're summing time ensure that your formul
cells are formatted as [h]:mm so that totals over 24 hours ar
displaye

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56625