YTD total based on sum of last number in another row that has a value?

July 12, 2010 12:54 PM

YTD total based on sum of last number in another row that has a value? I need to sum up the values in one row based on the matching row below it. For example, if I have a target of $1000 a month, and I only have actual revenue numbers through June, I would like to calculate the year to date for the target from Jan to June. Once July numbers are put in, I'd want the YTD for the target to update to Jan to Jul. I have tried a couple of variations on COUNT, INDEX and OFFSET. Example is here: http://malcommc.com/ytd.xls I am using Excel 2003
posted by malcommc to computers & internet (12 answers total)

Assuming I understand your question, use this formula in B2:
=SUMIF(C3:N3, ">0", C2:N2)
posted by ChrisHartley at 1:05 PM on July 12, 2010

Try this (ChrisHartley got me halfway there):

=SUMIF(C3:N3, ">0", C3:N3)+SUMIF(C3:N3, 0, C2:N2)

I.e. if there is a value in the actuals row, use that PLUS if there is zero in the actuals row, then use the target row.
posted by saintsguy at 1:13 PM on July 12, 2010

What Chris said, unless there is some change the actual revenue could be less than zero (no sales + returns?) and then you could do:

=(SUM(C2:N2) - SUMIF(C3:N3, 0, C2:N2))
posted by CharlieSue at 1:14 PM on July 12, 2010

Oh wait, scratch that, i didn't read properly ... the shame ... ChrisHartley has it

My formula calculates an adjusted overall target for the year.
posted by saintsguy at 1:16 PM on July 12, 2010

Perfect! Thank you. I knew I was overthinking it.
posted by malcommc at 1:17 PM on July 12, 2010

This list of Excel functions is quite handy, there really aren't that many options and with plain Excel it is easier to pick one when they are all right there in front of you.
posted by ChrisHartley at 1:20 PM on July 12, 2010

Thanks Chris. I've also encountered a snag. The same scenario, but let's say that April is $0.00. The other months, plus May and June have positive revenue numbers for actuals.
posted by malcommc at 1:33 PM on July 12, 2010

To deal with that scenario, you'll need to distinguish between a zero and an empty cell. So for April, enter 0; for July onwards, make sure the cell is blank. Then you can use:

=SUMIF(C3:N3, ">=0", C2:N2)
posted by saintsguy at 1:57 PM on July 12, 2010

I did some Googling and
=SUMPRODUCT(--(ISNUMBER(C3:N3)),C2:N2)
will work even when your actual is less than $0 (in case of returns, etc).
posted by ChrisHartley at 2:04 PM on July 12, 2010

What are you going to do if June is an "actuals" month, but has a zero value? Assuming there are going to be multiple lines of this (say, two rows for every region/sales person/etc), much better to put a "global" setting row at the top of the spreadsheet that indicates which months are actual rather than a hairy sumif() buried in a single cell. I recommend ones and zeros in the same columns as your months with the ones representing "actuals" months. Then you have an easy sumproduct() formula for your YTD.
posted by jbradley at 2:07 PM on July 12, 2010

Chris - thanks, but the formula gave me the same result as saintsguy's original formula, which ends up summing the target row. I guess that would be a good thing to use if I had negative numbers, but the real problem I'm fighting here is what happens when I'm missing a month's numbers. Shouldn't happen often, but it does happen.

saintsguy - what you said about empty cells really helps. Instead of making the future months into blanks, I just cleared the contents of the missing data, and then Chris' original solution takes care of it.

jbradley - that is some good food for thought, but the problem is that I'm making a dashboard that feeds off of many, many worksheets worth of report data. So unless there's a function that will automatically turn that 0 into a 1 when a value populates it, I am not sure it would fit what I'm trying to do. However, you gave me a good idea for a future project I have coming up.

Thanks to everyone for your help so far!
posted by malcommc at 2:48 PM on July 12, 2010

Actually, one amendment to what I wrote earlier. Blanking out the one set of missing revenue numbers didn't work. Since this is just a single anomaly in this report, I decided to cheat a bit. I went to the cells that are missing data and placed $0.0001 cents in each cell. Since the format of the cells only take it two decimal places, it still shows as $0.00, but now Chris' original formula works in that one problem row.
posted by malcommc at 6:12 AM on July 13, 2010