SUMPRODUCT (or other search/sum, i.e. SUMIF)

**ExcelJunkie** · 09-30-2006, 02:25 PM

Hi Folks:

I need assistance with a SUMPRODUCT or similar type of function, but to use it in a vertical fashion.

For example:

I need to search column E (E23:E753). In all rows where it finds a certain text value that I define "P.M. Well" I need to skip down 2 rows and SUM the total hours found (in the entire column).

So, if it finds "P.M. Well" in cell E41, I need the formula to do down two rows to cell E43 and start adding the total (let's say 6.50). If it also finds another "P.M. Well" in cell E300, I need it to add the total numberical value in E302 (let's say 8.00).

So, in this example it found two, so the total output would be 14.50.

How would I write this function?

Thank you!

**Flintstone** · 09-30-2006, 06:57 PM

Hello ExcelJunkie

In cell BB1 enter the ROW() function and copy down as far as needed, hide this column. This should work.

=SUM(INDIRECT("E"&INDEX($BB$23:$BB$753,MATCH("P.M. Well",$E$23:$E$753))+2))

Matt