complex SUMPRODUCT statements

This is my first post here and I was hoping to get some advice on solving a problem. I have in column A a series of short time intervals, then column B is simply a running total of those time intervals that adds up to 5min of data. Basically, I want to find the # of time intervals (#0f cells) in each minute. I wrote the following SUMPRODUCT functions to do this:

=SUMPRODUCT(N(B1:B400>0),N(B3:B400<60))
=SUMPRODUCT(N(B1:B400>60),N(B3:B400<120))
=SUMPRODUCT(N(B1:B400>120),N(B3:B400<180)) ...etc until 300 seconds

This function works well for the first 5min of data, but here is the problem. I have several 5min time chunks in the dataset which total 1 hour. What I would like to do is have the final 2 columns be a list of minutes (1-60) and the SUMPRODUCT statements I wrote above.


Columns C-X will be more 5 min pairs similar in format to above
Columns Y and Z will be the final columns and contain 1-60min and the SUMPRODUCT statements for each 5min block respectively.
For example, minutes 6-10 would look like this
=SUMPRODUCT(N(D1:D400>0),N(D1:D400<60))
=SUMPRODUCT(N(D1:D400>60),N(D1:D400<120))
=SUMPRODUCT(N(D1:D400>120),N(D1:D400<180)) ... etc until 300 sec

So my question: Is there anyway to change my SUMPRODUCT statement so that I don't have to manually change the column in the formula for each 5min block? Thanks for any help you guys can provide.

Can't say I necessarily understand all the data without an example workbook, but if indeed you want to change the column ref in the formula, then that immediately suggests to me that you need to incorporate an INDIRECT() function in the formula, and I guess a COLUMN(A1) style function to return a column number.

That's often the solution to column references that need to change automatically as you copy formula to other cells.

HTH

Create the name Tbl Refers to: your data table, e.g., =$A$1:$X$400, then

=SUMPRODUCT(
(INDEX(Tbl, 0, 2 * (INT((ROW() - ROW(Tbl) ) / 5) + 1)) >= 60 * MOD(ROW() - ROW(Tbl), 5) ) *
(INDEX(Tbl, 0, 2 * (INT((ROW() - ROW(Tbl) ) / 5) + 1)) < 60 * (MOD(ROW() - ROW(Tbl), 5) + 1) ) )

The linefeeds in the formula are for clarity; you can leave them or delete them.

The first instance of the formula must be in the same row as the first row of data in the table. Copy down from there.

This does the five 1-minute intervals in column B, then the five in column D, then ...

Thanks so much for the help. I can't say I quite understand how this formula is working. It seems to be getting different values than what I was getting with my original sumproduct statements. I've attached an example of the first 10min of data using the formula you suggested. I think whats stumping me is that the cumulative time columns (B and D) are not in 5 separate 0-60sec intervals; they actually go from 0-300sec (so am having to split it up some how). Thanks again for the suggestions.

Hmm. Define tblRow Refers to: =ROW($A$1), and change the formula to,

=SUMPRODUCT(
(INDEX(tbl, 0, 2 * (INT((ROW() - tblRow) / 5) + 1)) >= 60 * MOD(ROW() - tblRow, 5) ) *
(INDEX(tbl, 0, 2 * (INT((ROW() - tblRow) / 5) + 1)) < 60 * (MOD(ROW() - tblRow, 5) + 1) ) )

If you need to accommodate (and not count) blank cells as in column D, then

=SUMPRODUCT(
(INDEX(tbl, 0, 2 * (INT((ROW() - tblRow) / 5) + 1)) <> "") *
(INDEX(tbl, 0, 2 * (INT((ROW() - tblRow) / 5) + 1)) >= 60 * MOD(ROW() - tblRow, 5) ) *
(INDEX(tbl, 0, 2 * (INT((ROW() - tblRow) / 5) + 1)) < 60 * (MOD(ROW() - tblRow, 5) + 1) ) )

The total of each group of five should total the number of rows of data.

One more thing -- don't have a blank at the top of the even columns (B1, D1, ...). Enter a 0, and in B2 and copy down =B1 + A2/1000