Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing ranges...
I have a spreadsheet with Start date, (Column C) and
Service, Column E)... What I want to do is, on another tab, have a button that states total... When the button is clicked, a input box comes up with a drop down list of years, 2000, 2001, 2002, 2003... The user hits what year the they want to see, and a message box pops up with the total... The code would have to search through Column C for the requested year, and add when the criteria is met add Column E... I'm assumming that I would need two ranges, Start Range and Service Range?? Thanks in Advance Kevin... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing ranges...
Sorry for the confussion, yes the date is a true date...
Column E is costs for services (eg. $1200.56)... The drop down box with the dates, and the SUMIF formula in another cell would work, never thought of that option... That would have to be in an array formula?? Thanks Nick.. -----Original Message----- Kevin The description is maybe a little vague. Is the 'date' in C a true date eg. 1/1/2000 or simply a text year, eg. 2000? What is being summed? Or did you want the cells counted. Is it imperative that you have the button, inputbox, etc. This could be achieved on the other sheet by placing combobox on the sheet with the values in and using simply a SUMIF() (If you are looking to sum) function to display the total anywhere in a worksheet cell. -- HTH Nick Hodge Southampton, England "Kevin" wrote in message ... I have a spreadsheet with Start date, (Column C) and Service, Column E)... What I want to do is, on another tab, have a button that states total... When the button is clicked, a input box comes up with a drop down list of years, 2000, 2001, 2002, 2003... The user hits what year the they want to see, and a message box pops up with the total... The code would have to search through Column C for the requested year, and add when the criteria is met add Column E... I'm assumming that I would need two ranges, Start Range and Service Range?? Thanks in Advance Kevin... . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing ranges...
Kevin
It is a little more complicated with exact dates as the input cell dates are probably many hundreds/thousands. If you asked the operator to enter a date in a cell you could simply use a SUMIF function to return the total. EG. Date entry cell G1, dates in C1:C1000 costs in E1:E1000 then with a formula in H1 enter =SUMIF($C$1:$C$1000,$G$1,$E$!:$E$1000) -- HTH Nick Hodge Southampton, England "Kevin" wrote in message ... Sorry for the confussion, yes the date is a true date... Column E is costs for services (eg. $1200.56)... The drop down box with the dates, and the SUMIF formula in another cell would work, never thought of that option... That would have to be in an array formula?? Thanks Nick.. -----Original Message----- Kevin The description is maybe a little vague. Is the 'date' in C a true date eg. 1/1/2000 or simply a text year, eg. 2000? What is being summed? Or did you want the cells counted. Is it imperative that you have the button, inputbox, etc. This could be achieved on the other sheet by placing combobox on the sheet with the values in and using simply a SUMIF() (If you are looking to sum) function to display the total anywhere in a worksheet cell. -- HTH Nick Hodge Southampton, England "Kevin" wrote in message ... I have a spreadsheet with Start date, (Column C) and Service, Column E)... What I want to do is, on another tab, have a button that states total... When the button is clicked, a input box comes up with a drop down list of years, 2000, 2001, 2002, 2003... The user hits what year the they want to see, and a message box pops up with the total... The code would have to search through Column C for the requested year, and add when the criteria is met add Column E... I'm assumming that I would need two ranges, Start Range and Service Range?? Thanks in Advance Kevin... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing to defined ranges | Excel Worksheet Functions | |||
REF# error on summing ranges | Excel Worksheet Functions | |||
Summing ranges | Excel Discussion (Misc queries) | |||
summing non contiguous ranges | Excel Discussion (Misc queries) | |||
Summing moving ranges & dates | Excel Worksheet Functions |