Re: Formula to chart every nth row data using named ranges (without using another col

Tried NBVC's solution and first solution works great with charting individual rows.

Second formula however is a bit off the mark. Lots of trial and I still don't seem to be able to get it to work...

What I was looking for is that if I entered 3, then I wish to sum up values for every 3 rows and then plot a column chart having sum of every 3 rows in a column in each individual column.

For example, the desired result when using the data range given in my original post (and entering 3 in cell A1), my chart should be plotting bars values of:

**Bar 1:** 154 (10+26+18) (viz. sum of row nos. 1,2,3)

**Bar 2:** 48 (16+20+12) ,(viz. sum of row nos. 4,5,6)

**Bar 3: **54 (24+5+25) ,(viz. sum of row nos. 7,8,9)

**Bar 4: ** 46 (14+9+23) (viz. sum of row nos. 10,11,12)

However, the formula =SUM(OFFSET($A$3,((ROW(Sht!$A$3:INDEX(Sht!$A:$A,21))-3)*Sht!$A$1),0,$A$1,1)) is summing up the values of every 3rd row and **giving me a single bar having the sum of every third row**, i.e. I am getting a single bar summing up values of row nos. 1,4,7,10...

Interestingly, when I enter the formula =SUM(OFFSET(sht!$A$3,((ROW(sht!$A3:INDEX(sht!$A:$A,21))-3)*sht!$A$1),0,sht!$A$1,1)) in a cell in excel and drag it down, I get the desired resulting values. But it does not seem to be working when entered as a formula in a named range for mapping a chart.

Could you please help me figure out what am I missing?

Sample file showing result of the formulas when used as a named range attached for reference.