IF with variable range

[kala]

Hello! I can't find a similar question, so posting a new thread.
Problem: I want to create a SUMIF or MAXIF statement where the range will vary depending on when the first data point starts but the formulat must have 52 columns in each. For example, the range in row 1 would start from Col A and go to Col AZ. The range in row 2 would start from Col D and go to Col BC. The range in row 3 would start in Col B and go through Col BB.

..........A..........B...........C..........D..........E..........F..........G..........ETC
1........2..........4..........100.......200.......225.......1000.....3000.......ETC
2.....(blank)...(blank)....(blank)....40........60.........5000.....8000.......ETC
3.....(blank)......30.........4..........40.......400........300.......2000.......ETC

Can anyone help? Thank you!

 

[VegasUser]

Does the formula that provides the address need to fit in a single cell, or do you have some flexibility as to assigning intermediate cells to hold progressive values that will 'build' the address?

 

[kala]

The report has flexibility!

 

[snoopyhr]

If there is no blank between the columns, that is each row contains no blank from first occourrance of data to the last then this should work, just adjust the range to your need.

Excel Workbook
	A	B	C	D	E	F	G
1	2	4	100	200	225	1000	3000
2				40	60	5000	8000
3		30	4	40	400	300	2000
4	Result
5	4225
6	13000
7	2700
8
Sheet5

 

[kala]

This is really close...thanks for taking a stab. The formula actually should only account for 52 columns if the cells meet the critiera >200, as in your example. My report has a total of 199 data columns so somehow, the formula should stop summing at 52 columns or when there is blank data. It appears that the formula you posted accounts for all cells that are not blank and above 200 -- am I reading that right?

 

[snoopyhr]

A quick fix will be to adjust the range to match.

Instead of this :

=SUMIF(OFFSET(A1,,,,COUNTA(1:1)+SUMPRODUCT(--ISBLANK(A1:G1))),">200")

change it to :


=SUMIF(OFFSET(A1,,,,COUNTA(A1:AZ1)+SUMPRODUCT(--ISBLANK(A1:AZ1))),">200")

From A1 to AZ1 there are 52 columns
or
=SUMIF(OFFSET(A1,,,,52),">200")

 

[kala]

I should have mentioned that I did adjust my range but the values look as if they are adding everything >200. I know this because I did a manually check.

Since I have 199 columns (C:GS), the start AND end column will vary. I need 52 columns summed together if it is greater than "200" (as in your example). Can we adjust the formula to start counting from when data is greater than 200 and end after 52 columns?

 

[snoopyhr]

the best i can come up with is this :

=SUM(OFFSET($A1,,MATCH(201,1:1,1)-1,,52))

The match returns the ordinance of which the data range follows with greater than 200, and from there expands to next 52 column.

Hope this help,