Sum the first x cells in a range

G

Guest

Weird one, but I know someone out there knows how to do this...

I have two rows, this fiscal year and last fiscal year.
I have a count of the cells that have values for this fiscal year (let's say
the count of cells e6:p6 is 8).
I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last
fy to date compared to this fy to date.

How would I build a function that would sum the first 8 cells in row 5?
 
P

Peo Sjoblom

Try

=SUM(E5:INDEX(E5:p5,COUNTA(E6:p6)))

replace COUNTA with COUNT if E6:p6 holds numbers
 
G

Guest

No, because on month three of the year, it would be =sum(e5:g5) while on
month 10 of the year it would be =sum(e5:n5).
 
G

Guest

That won't work - all cells have values (each represents a value for a month
in the prior fiscal year).

I don't want to sum all of the year, only the same months in the last year
that i have values for in this year.
 
P

Peo Sjoblom

Note that although this formula is shorter than the index version it is also
volatile so it will always recalculate regardless whether you do any changes
or not. That means that if you just open the workbook it will ask if you
want to save it when you close it
 
G

Guest

sorry for misunderstanding your lines below...

from your first line:
from your next line :
I don't want to sum all of the year, only the same months in the last year
that i have values for in this year.

regards,
driller
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top