How to sum a row until text appears in an another row

[whitenk]

Time spent on each page appears in row 18.
Need to sum up all of these pagetimes for each site, to get the total time on site.
So in the example below,
D19 would reflect the total time spent on GreenElectricalSupply.com sum of cells D18-G18(1:23.0)
and H19 would display the total time spent on Rexel.com (00:45.0)

So I am trying to figure out how to do a conditional sum across row 18, until a new site begins (indicated by the appearance of text in the next cell of row 8).


thanks!

Excel Workbook
	C	D	E	F	G	H	I
8		greenelectricsupply.com				rexel.com	nextag.com
9	WW ONLY: Arrived at site via					URL (AddressBar)	URL (AddressBar)
15	Page Type (Home, category, content)	Home	Category	Category	Home	Home	Home
16	Page start time	02:34.0	02:50.0	03:00.0	03:23.0	04:00.0	04:46.0
17	Page leave time	02:50.0	02:59.0	03:22.0	03:59.0	04:45.0	05:02.0
18	Time on page	00:16.0	00:09.0	00:22.0	00:36.0	00:45.0	00:16.0
19	Time on site (seconds)
Data

Excel 2007

 

[deanst]

It's not the most elegant solution in the world, but I think this will work. It uses the iferror function so this will only work in XL07 and up.

Enter in Cell D19.

Enter as an array formula (control + shift + enter)

=IF(D8<>"", SUM(OFFSET(D18, 0, 0, 1, IFERROR(MATCH(TRUE, OFFSET(E8, 0, 0, 1, 100)<>"", 0), 1))), "")

It checks for another text in row 8 for the next 100 columns, so if the length of possible columns between websites is more than that it won't work.

Dean.

 

[whitenk]

Thanks dean. I am using excel 2007 but I get an error when I enter this formula. It looks like part of it is missing at the end (at least there are no closing parentheses). Maybe some got cut off?
(I tried just adding close parentheses but seems like there is still something missing.)

 

[Rick Rothstein]

Thanks dean. I am using excel 2007 but I get an error when I enter this formula. It looks like part of it is missing at the end (at least there are no closing parentheses). Maybe some got cut off?
(I tried just adding close parentheses but seems like there is still something missing.)

Slide the horizontal scroll bar underneath the formula to see all of the formula.

 

[whitenk]

thanks! that formula works perfectly.
(the horizontal scroll bar wasn't showing up on my mobile phone