Sumif formula

DmaxHunter

New Member
Joined
Feb 16, 2017
Messages
5
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Feb 16, 2017
Messages
5
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 (n)th year from column D but those costs/years need to start AFTER 'remaining life' year of column F
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=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
Code:
=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

New Member
Joined
Feb 16, 2017
Messages
5
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
Code:
=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 :)
 

Forum statistics

Threads
1,086,204
Messages
5,388,395
Members
402,115
Latest member
ypompoms

Some videos you may like

This Week's Hot Topics

Top