Wedding Woes

Excel help

Please excuse me. This isn't a wedding woe.  
I have an Excel woe.
Anyone have experience with the SUMIF or SUMIFS function?
Column A is a list of states.  If A2 is OH, I want D2 to be the sum of C2 multiplied by B250 from another worksheet. If A2 is CA, I want D2 to be the sum of  C2 multiplied by B251 in another worksheet.  If A2 is FL, etc....
(Note: all worksheets are in the same workbook.  If this can't be done with linking to a different worksheet, then I can alway put the info on the same worksheet - I would just prefer not to.)

Re: Excel help

  • is column a all the states? if so, it's a long formula.
    image
  • GBCKGBCK member
    First Anniversary 5 Love Its Name Dropper Combo Breaker
    I think this can be done...but I'm saying that while being to fuzzy to grasp quite what you're saying.

    If-then excel things are a PITA
  • can you post the formula, either here or in fb, for tinkering?
    image
  • hmonkey said:
    can you post the formula, either here or in fb, for tinkering?
    I can't because I haven't figured out the formula yet.  Hence the post asking for help.
  • GBCKGBCK member
    First Anniversary 5 Love Its Name Dropper Combo Breaker
    Im a bit fuzzy still , but below is what I'd see as the formula...I'd create a list of states on another sheet/elsewhere in the document--in this case, (I"m arbitrarily assigning that to the Q row), BUT, I don't know how you can create layers of that--I just know how to do it with 2 options--so if Q2 is OH, then you get the first opton.  if Q2 is not OH, then...I don't know how to put a second layer of "ok, is it option 3?  4?  5?"

    =IF(A2=Q2, "SUM(worksheet2!C2*Worksheet2!B250", "Not Ohio")
  • start small.
    as in, the formula for D2 is C2*Sheetname!B250
    then add the SUMIFs

    If A2 is OH, I want D2 to be the sum of C2 multiplied by B250 from another worksheet
    D2
    =SUMIF A2:A2,"OH", C2*Sheetname!B250

    image
  • Crap. Nothing's working. Thanks for your help anyway!
  • this might be a bit more work - but if the sheet's not a living document, could you just sort or filter by state and add in the calculation manually and copy down?

    I'm assuming you've already considered that (or that the data is dynamic, and it wouldn't work). 

    You may be able to create a macro to do it for a dynamic worksheet, but I'm not that knowledgeable in Excel. DK would probably know - he's a software engineer, and works in Excel all of the time. 
  • Make sure you use quotes around "OH" so it knows it is text
  • I found a work around.  I added a column, and in the new column (which I will hide), i used an IF formula.  And had to add 15 different instructions to get it to calculate the price for each state:  =IF(A2="OH",12.31,IF(A2="UT",22.58,IF(A2="IN",12.33,IF(A2="FL",15.99,IF(A2="AL",13.45,IF
    (A2="AZ",22.58,IF(A2="CA",24.99,IF(A2="IL",13.39,IF(A2="KS",13.45,IF(A2="KY",12.33,IF
    (A2="MI",13.39,IF(A2="MO",13.45,IF(A2="OR",24.99,IF(A2="TN",13.39,IF(A2="VA",13.45)))))))))))))))

    And then the next column multiplies that column with C2, which is the sum that I wanted...
    Not ideal, and when any of the rates change on the different states, or when a new state is added it will be a HUGE PITA, but it gets the job done.  I figure the couple hours I spent figuring it out will save me a couple of hours over a year.  So, not the greatest use of my time...  Oh well.  At least I learned something that I can apply to other projects, right?

    Thanks everyone for the advice.
  • GBCKGBCK member
    First Anniversary 5 Love Its Name Dropper Combo Breaker
    I'm impressed.
  • I give most of the credit to the Google.
  • Google is teh ossum for so many things....Excel help, fun gifs, camo wedding dresses, etc.
This discussion has been closed.
Choose Another Board
Search Boards