Is there an easy way to sum across a gap?

users
#1

I have data in a table by month. At the end of each year there is a summary row separated by a blank line above and below. Frequently I need to sum from, say, 03/16-02/17 so I have 10 rows with data I want, three rows with data I don't want and two more rows with data I want. My approach has been something like this:

sum(A3:A12)+sum(A16:A17)

A13:A15 contain the data I don't want but I want to be able to see.

This works but it is less than automatic. When I copy the formulae I need to edit them so they apply to the right rows.

Another thing is the rows summed aren't always the same. Sometimes I may need only two rows and other times maybe as many as 12.

#2

Hello,

Is there a way to identify the data you want? If so, you could use a
set of =SUMIF() or =SUMIFS() to add up the data that matches one or a
group of criteria. Otherwise, you may need to provide an example
(obviously scrubbed since it seems to be some financial data) because I
cannot quite picture what you are attempting to do.

I hope this helps.
Rémy Gauthier.

I have data in a table by month.  At the end of each year there is a 
summary row separated by a blank line above and below.  Frequently I 
> need to sum from, say, 03/16-02/17 so I have 10 rows with data I

want,

> three rows with data I don't want and two more rows with data I

want.

My approach has been something like this:

sum(A3:A12)+sum(A16:A17)

A13:A15 contain the data I don't want but I want to be able to see.

> This works but it is less than automatic.  When I copy the formulae

I

need to edit them so they apply to the right rows.

> Another thing is the rows summed aren't always the same.  Sometimes

I

#3

I have data in a table by month. At the end of each year there is a summary row separated by a blank line above and below. Frequently I need to sum from, say, 03/16-02/17 so I have 10 rows with data I want, three rows with data I don't want and two more rows with data I want. My approach has been something like this:

sum(A3:A12)+sum(A16:A17)

This is unnecessarily complicated. Use =SUM(A3:A12,A16:A17)

A13:A15 contain the data I don't want but I want to be able to see. This works but it is less than automatic. When I copy the formulae I need to edit them so they apply to the right rows.

I'm not sure why you are copying the formula. If you construct it properly - with $ signs in the right places - it ought to modify itself appropriately. But you can anyway form such a formula very simply: type "=SUM(", drag across A3:A12, type ",", drag across A16:A17, type ")", press Enter of click the green arrow.

Another thing is the rows summed aren't always the same. Sometimes I may need only two rows and other times maybe as many as 12.

There was I, thinking most years had twelve months! If you always have twelve rows for the months, your SUM() function will add zeros for any empty cells and still give the correct answer.

The solution to all this is very probably to design your spreadsheet to make the process of creating formulae easy. One obvious possibility would be to run your months sequentially, not breaking the lists for the annual sums. (After all, January follows December just as surely as December follows November.) Your sums could be in a new, adjacent column - perhaps next to the December values. Again, with appropriately constructed formulae, everything will copy seamlessly. Alternatively, you could have a separate table of annual sums in consecutive rows somewhere else on the spreadsheet.

I trust this helps.

Brian Barker