Results 1 to 5 of 5

Thread: Sumif formula

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumif formula

    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][/IMG]

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  5. #5
    New Member
    Join Date
    Feb 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •