Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very complex IF function if not an alternative function of crossselection | Excel Worksheet Functions | |||
Complex Function | Excel Discussion (Misc queries) | |||
complex function----Help! | Excel Worksheet Functions | |||
Complex sum function help - possible? | Excel Programming | |||
complex function | Excel Worksheet Functions |