Please Help with Formula!

[dutch4fire23]

Hello, I am a new user and I am having great difficulty in figuring out something. I have a dataset in Excel with first names, last names, states, cd sales total cost by state... and I am I started a new worksheet cales total sales y state. Now, in this new sheet I want have the states copied over and in the B2 cell I want to write a formula that will enable me to have the total cost from O2:O112 cells ("Total Sales") to be sumed up by each state with absolute references. How would I do this? Example I am currently using =SUMIF(Demographics!F2:F112, "AK", Demographics!O2:O112) which is giving me the correct total sales for AK, but I want to copy this formula using absolute references and have the states change while keeping the fields O2:O112 the same. I just don't want to enter in each state which is what I was starting to do, but It will take a while =SUMIF(Demographics!F2:F112, "IA", Demographics!O2:O112)

as an example here is Totals by State! worksheet:

AK $324.50 <---- Total from Demographics! for AK
IA $428.68

Using the above formula to calculate the totals for each state. I want to copy the formula from the B2 for AK cell and paste it so I do not have to go in and change the formula for each one

Here is the Demographics! Worksheet as an example:

States: Total Sales for the year
AK <--F2 $100 <--- O2
AK $100
AK $100
Ak $100
IA $150
IA $150
IA $100


Do I need a different formula all together or just change F2:F112 to $F$2:$F$112 and O2:O112 to $O$2:$O$112, but how to I have Excel change the states without me having to type in each state in each new state row.

Any Help id greatly appreciated, Thank you

Andy

[dutch4fire23]

I found the answer from another post, but I'm sure I'll have more questions. Thank for those who looked. If you have anymore suggestions let me know, Thank you

the correct formula was:
=SUMIF(Demographics!$F$2:$F$212,$A2,Demographics!$O$2:$O$212)

I do have another question actually. In the Total Sales worksheet, I have a a cell in S2 that I wish to show the max value from the "total sales" colum and then show the first and last name related to that particular row where the max value was. so, I only have =MAX(O2:O112), but I want to show the Name associated with the max and not just the $ amount.

[VBA Noob]

This website should help

Change the column index (IN bold) and ranges to suit


=OFFSET(G32,MATCH(MAX(G32:G38),$G$32:$G$38,0)-1,-1,1,1)

http://www.cpearson.com/excel/lookups.htm

VBA Noob