Excel help

AuntFlo

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.)

mrsconn23

Step 1: Turn off the computer

Step 2: unplug it

Step 3: find a bat

Step 4: drive to a field

Step 5: Smash computer to smithereens while "Damn it feels good to be a gangsta" plays in the background

Step 6: Leave the scene

Step 7: Drink (really, this should be happening the whole time)

0Face

hmonkey

is column a all the states? if so, it's a long formula.

GBCK

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

AuntFlo

Sorry - - I posted and went to lunch.  Priorities.

Anyway, hmo - - It is only 15 states, and yes, I know it will be a long formula.  

I'm sure it can be done, I just can't get the formula right.  

mrs.conn - I asked for people to help, not be assholes. Funny it's easy to be a dick online. And there are actual camo wedding dresses bitch so fuck you both.

hmonkey

can you post the formula, either here or in fb, for tinkering?

AuntFlo

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.

GBCK

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")

hmonkey

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

AuntFlo

Crap. Nothing's working. Thanks for your help anyway!

*Barbie*

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. 

cupcake985

Make sure you use quotes around "OH" so it knows it is text

AuntFlo

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.

GBCK

I'm impressed.

AuntFlo

I give most of the credit to the Google.

mrsconn23

Google is teh ossum for so many things....Excel help, fun gifs, camo wedding dresses, etc.