Sumif formula

[DmaxHunter]

Greetings! Thanks in advance for any help! I love being able to find solutions on the forums but sometimes I don't have the best luck if I can't come up with the right key word or phrase when searching for it.

I've got half of my solution it seems, however I'm now trying to sum a certain number of cells over after a sumif condition has already been accomplished. Rather than try to describe this all thru text, I have included a picture (since I'm a visual kind of guy) and description of exactly what I'm trying to accomplish.

So any ideas on how to sum this and/or highlight it? Thanks in advance for any sort of help with this.

[/IMG]

 

[Gerald Higgins]

Re: Request for help with sumif formula

Hi.
Why exactly is the result in I11 4000 ?
Is it because that is the year when you're expecting to incur the Replacement Cost (col G) for the Furnace (row 2) ?
Same for the value of 2000 in J11, because that year you'll incur the replacement cost for the Water Heater ?

In cell N11 should the result be 3000 ?
1000 to replace Water Heater plus 2000 to replace the item on row 5 ?

 

[DmaxHunter]

Re: Request for help with sumif formula

Yes, that is exactly it, N11 should be 3000.

So essentially every 'Replacement Cost' from column G should repeat itself every th year from column D but those costs/years need to start AFTER 'remaining life' year of column F

 

[Gerald Higgins]

Re: Request for help with sumif formula

OK, so there are probably lots of ways of doing this.
If I was doing it myself, I think I would use separate helper rows to
a) identify which year the replacements happen, and
b) calculate the replacement costs in those years

Some people are not keen on helper columns / rows, but I think they make it easier to check the calculations.
And once you've set them up, you can hide them if you don't want to see them - either just hide the rows, or place them on a completely separate sheet and hide that if you like.

So here goes.
To identify the years in which replacement is required, for "Furnace", use something like

=IF(H$1< $F2,0,IF(MOD((H$1-$F2),$D2)=0,1,0))

Let's say you put this in H12, and copy across to the right as far as required.
This should give you a string of mostly 0s, with the occasional 1, in year 2, year 14, and so on.
This identifies which years see a replacement.

Then have another formula, like this

=H12*$G2

and again, copy across as far as required.
This puts 4000 into each year that a new furnace is required.

Then your required results in row 11 are the sum of your monthly cost, PLUS the replacement cost (if any) identified in the second formula I've given you.

 

[DmaxHunter]

Re: Request for help with sumif formula

This works perfectly thank you!! Using your two formulas I created two additional helper tables essentially. And then using another formula

=IF(H$1<=$F2,$G2/$F2,$G2/$D2)

I was able to keep the original yearly amounts in the original table area. And best yet the conditional formatting for highlighting all still works. So I'll just have to hide the helper tables somewhere but that's no real problem when it results in a working solution. Thank you so much for the assistance! I greatly appreciate it! I've spent many many many hours on this one and you solved it in minutes