COMBINING SUMIF AND OFFSET FUNCTION

[Ellieprophet]

Hi, I’ve got a huge data set. I’ve used the SUMIF function =SUMIF(Y3:Y49”<>#VALUE!”) to add every 48 values and exclude the #VALUE terms. However I also need to combine OFFSeT to repeat this formula to add every 48 terms in that column if that makes sense? As the next term I need is Y:50-Y96 but excel shows Y4:Y50! Thanks if anyone can help

 

[jasonb75]

Try this one instead

=AGGREGATE(9,6,INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*48+3):INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*48+48))

Not essential, but ideally Z$1:Z1 should refer to the cell holding the first formula, that way it is less likely to break if you delete rows.

 

[Ellieprophet]

Try this one instead

=AGGREGATE(9,6,INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*48+3):INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*48+48))

Not essential, but ideally Z$1:Z1 should refer to the cell holding the first formula, that way it is less likely to break if you delete rows.

Hi Jason, thanks for getting back for me that one isn’t seeming to work!

Just another side question- what if I wanted to average every 336 rows - whilst still excluding the “value!” And to repeat this throughout my data set? So keep averaging every 336 throughout and exclude the error values.
Thanks !!

 

[jasonb75]

Oops, I didn't check the details. Y3:Y49 is 47 rows, not 48. Second part was my fault.

=AGGREGATE(9,6,INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*47+3):INDEX(Y:Y,(ROWS(Z$1:Z1)-1)*47+49))

The bold 9 at the start denotes SUM, you can change it to 1 for average, or 2 for count (there are a lot of other options if needed).
47+3 and 47+49 set the start and end of the first range, with 47 being the number of rows in the range.

Hope that makes sense.

 

[Ellieprophet]

Hi Jason, I think that’s sorted it!! Thank you so much I’ve spent all day trying