Sum table using a dropdown list.

Microsoft Office 2007 home and student
July 16, 2010 at 01:10:11
Specs: Windows XP

I want to sum values from a table I have. I try to describe it here.
This is how the table looks like.
Year Hours Injuries
2000 2 1
2001 5 2
2002 4 1
2003 9 3

Under this table I have made two drop down list so I can choose "From year" to "To Year".

Under this I want to it to sum up all the values for number of "Hours" and number of "Injuries" from the years I have chosen.

I have tried to make a SUMIF function but it isn't correct. What I have written is:
SUMIF($A$3:$A$19;">"&$B$23;$C$3:$C$19) where column A is years, B is Hours and where I choose "From year", and C is Injuries and "To year". With this code it doesn't matter what I choose in "To year".

I would appreciate all the help I can get :)


See More: Sum table using a dropdown list.

Report •

July 16, 2010 at 01:52:45
Does this work for you...

Using your example data I put drop downs in D1 and D2 to select my years. In E1 I used this formula to get the hours:

=SUM(B2:B5) - SUMIF(A2:A5,"<"&D2,B2:B5) - SUMIF(A2:A5,">"&D3,B2:B5)

      A      B    C      D      E
1   Year     H    I     Year    H
2   2000     2    1     2001    9
3   2001     5    2     2002
4   2002     4    1
5   2003     9    3

Report •

July 16, 2010 at 06:42:13
Thanks a lot DerbDad03!! It works great.


Report •

July 16, 2010 at 08:08:03
Glad I could help.

You could improve upon the system by making sure it doesn't give weird results if your date range is out of whack, like your end date being less than your starting date.

Let me know if you need help with that.

Report •

Related Solutions

July 20, 2010 at 23:21:14

Do you have any suggestions to what I can do to make sure not to have weird results (like negative results) ?


Report •

Ask Question