SUM/AVERAGE every nth row

[johnmitch38]

On the attached, I have Mileage figures in the yellow Rows 2, 5 & 11 (note that row 8 is empty)

I have Fuel figures in the green rows 3, 6 & 13 (note that row 9 is empty)

I want to return an average MPG by summing all of the Mileage figures and dividing by the sum of all the Fuel figures.

I also want to ignore any blank cells.

Much appreciated.

[PFDave]

Please Login or Register  to view this content.

[jeffreybrown]

How about >> =SUMIF(A2:A12,"Mileage*",B2:B12)/SUMIF(A2:A12,"Fuel*",B2:B12)

[johnmitch38]

SUMIF/SUMIFS don't seem to work. SUMIF I think is including the individual average in rows 4,7,10 & 13 so this needs to be ignored.

SUMIFS just returns a #VALUE error.

I want the result of the formula in Cell B1 and I want to be able to copy the formula accross multiple columns.

[jeffreybrown]

Maybe you could update your example sheet and provide us with some data and what you expect as an answer.

For me the Sumif formula works, but I don't' know what your data looks like.

[johnmitch38]

The attached now shows expected result shown ib cell B1

I will have multiple columns so want to be able to drag the formula accross a worksheet.

[davsth]

Thats the same result as Jeffrey's formula
=SUMIF(A2:A12,"Mileage*",B2:B12)/SUMIF(A2:A12,"Fuel*",B2:B12)

or if you are to drag it accross
=SUMIF($A2:$A12,"Mileage*",B2:B12)/SUMIF($A2:$A12,"Fuel*",B2:B12)