# Thread: sum data between two cell values Thanks: 0 Likes: 0

1. ## sum data between two cell values

Hi All,

I know this is a question like using Sumproduct to total data between dates but i just cant get this to work.

Each line in the report shows a status change with in a record there are multiple records in the report, also there are random combinations of status changes per record. Im trying to sum the minutes in duration between two of the status types.

example data in cols A B C. A is the record ID B is the Status value and C is the duration for each status.
INC0001, Start, 15
INC0001, Process1, 5
INC0001, Process2, 11
INC0001, Process4, 6
INC0001, Process1, 5
INC0001, End, 7
INC0001, Refresh, 5
INC0002, Start, 15
INC0002, Process1, 5
INC0002, Process2, 14
INC0002, Process1, 5
INC0002, End, 7

So I need to calc the duration for INC0001 between Start and End points giving a total of 49 and INC0002 total of 46. the sumproduct example i have is for date ranges which ive tried to modify by unsure how to combine the INC ref and the status and then add up between those points.

Cheers

2. ## Re: sum data between two cell values

Why does INC0001 not total to 54?

Why not use a Pivot Table?

http://www.howtogeek.com/howto/6416/...in-excel-2007/

3. ## Re: sum data between two cell values

 ID Status Duration INC0001 INC0001 Start 15 Start INC0001 Process1 5 End INC0001 Process2 11 49 INC0001 Process4 6 INC0001 Process1 5 INC0001 End 7 INC0001 Refresh 5 INC0002 Start 15 INC0002 Process1 5 INC0002 Process2 14 INC0002 Process1 5 INC0002 End 7

Not sure if this is possible/easy with a pivot table. What follows is a formula approach...

F4, control+shift+enter, not just enter:
Code:
```=SUM(
IF(ROW(\$B\$2:\$B\$13)-ROW(B\$2)+1>=MATCH(F2,IF(\$A\$2:\$A\$13=F1,\$B\$2:\$B\$13),0),
IF(ROW(\$B\$2:\$B\$13)-ROW(B\$2)+1<=MATCH(F3,IF(\$A\$2:\$A\$13=F1,\$B\$2:\$B\$13),0),
IF(\$A\$2:\$A\$13=F1,\$C\$2:\$C\$13))))
```

4. ## Re: sum data between two cell values

Many thnaks will test and return here with my results

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•