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
------------------------------------------------------------------------
dutch4fire23's Profile: http://www.excelforum.com/member.php...o&userid=36900
View this thread: http://www.excelforum.com/showthread...hreadid=566146

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.


--
dutch4fire23
------------------------------------------------------------------------
dutch4fire23's Profile: http://www.excelforum.com/member.php...o&userid=36900
View this thread: http://www.excelforum.com/showthread...hreadid=566146

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=566146