Wedding Woes

# Excel help

member
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

• member
• member
is column a all the states? if so, it's a long formula.
• member
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
• member
can you post the formula, either here or in fb, for tinkering?
• member
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.
• member
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")
• member
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

• member
Crap. Nothing's working. Thanks for your help anyway!
• member
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.
• member
Make sure you use quotes around "OH" so it knows it is text
• member
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.
• member
I'm impressed.
• member
I give most of the credit to the Google.
• member
Google is teh ossum for so many things....Excel help, fun gifs, camo wedding dresses, etc.
This discussion has been closed.