Sum a list of values starting at a variable record

[bctyner]

I have a table of records, and need to sum every value in column I that occurs AFTER the cell in column A of the same row = "ABCD Pretest". The record for ABCD Pretest is not always in the same row. Every value in column I needs to be summed after that row.


Is there a way to use subtotal or vlookup to sum every value in one column starting after a specific value in the same row but a different column?

 

[fred]

There are many ways to do this.


=sumif(), filter+subtotal(9,list), pivot table, etc.

 

[bctyner]

Because there are so many different values in the A column, a sumif statement becomes extremely large. Using a table and filter would work but my staff isn't familiar with working with tables. Is there a simpler way to do the same thing as a sumif?


Since it needs to sum everything after the ABCD Pretest value in column A, and nothing before it, I imagine it should be easier than including every possible value to appear in column A in the sumif formula.

 

[SirJB7]

Hi, bctyner!


Let's assume your data is in Columns A and I, with header in row 1, ranging from rows 2 thru 16.


In A18 type:

=SI(ESERROR(COINCIDIR("ABCD Pretest";A:A;0));"";SUMA(INDIRECTO(CONCATENAR(DIRECCION(COINCIDIR("ABCD Pretest";A:A;0);9;2);":";DIRECCION(16;9;2))))) -----> in english: =IF(ISERROR(MATCH("ABCD Pretest",A:A,0)),"",SUM(INDIRECT(CONCATENATE(ADDRESS(MATCH("ABCD Pretest",A:A,0),9,2),":",ADDRESS(16,9,2)))))


There is included "ABCD Pretest" row, if you want to exclude it, add "+1" unquoted after "MATCH("ABCD Pretest",A:A,0)" within ADDRESS function.


Check if that works for you.


Regards!

 

[SirJB7]

Hi, bctyner!


A border condition usual problem, it doesn't work for exclusion if "ABCD Pretest" is in last row.


So do this:


a) define a named ranged called "DataRangeTable" assigned to rows of A:I columns as needed, so we get rid of the fixed 16 rows in formula


b) modify formula as:

=IF(ISERROR(MATCH("ABCD Pretest",A:A,0)),"",IF(MATCH("ABCD Pretest",A:A,0)=ROWS(DataRangeTable);"";SUM(INDIRECT(CONCATENATE(ADDRESS(MATCH("ABCD Pretest",A:A,0),9,2),":",ADDRESS(ROWS(DataRangeTable),9,2))))))


Sorry for the inconvenience.

Regards!