Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
Hi,
Please help as I am slowly going mad - I know there must be a simple solution to the problem but I have lost the plot trying to find it! I have a simple sheet with 2 defined columns - Cost(A:A:) & Date (B:B:). I am trying to produce a simple formula that will total the costs from when given a date range. I have named cells "from" & "to" for the date criteria to entered and have used the SUMIF to try to calculate as required. =SUMIF(Date,"<=FromTo",Cost) - This does not work! If I change the formula as follows then it will return the correct values =SUMIF(Date,"<=01/03/20008",Cost) but I cannot get it to accept the defined names for the criteria, nor to calculate using 2 criteria. Any help would be greatly appreciated, as I am actually quite fond of my laptop and don't really want to throw it across the room!! -- John |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
One way is to use 2 SUMIFs, something like this:
=SUMIF(Date,"="& -- "2 Jan 2008",Cost)-SUMIF(Date,"="& -- "8 Jan 2008",Cost) Above expression will return the cost totals for dates from 2 Jan 2008 to 7 Jan 2008 (inclusive). Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 Files: 352, Subscribers: 53, Downloads: 15,500 xdemechanik --- "John" wrote: Hi, Please help as I am slowly going mad - I know there must be a simple solution to the problem but I have lost the plot trying to find it! I have a simple sheet with 2 defined columns - Cost(A:A:) & Date (B:B:). I am trying to produce a simple formula that will total the costs from when given a date range. I have named cells "from" & "to" for the date criteria to entered and have used the SUMIF to try to calculate as required. =SUMIF(Date,"<=FromTo",Cost) - This does not work! If I change the formula as follows then it will return the correct values =SUMIF(Date,"<=01/03/20008",Cost) but I cannot get it to accept the defined names for the criteria, nor to calculate using 2 criteria. Any help would be greatly appreciated, as I am actually quite fond of my laptop and don't really want to throw it across the room!! -- John |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
Thanks Max, I can't believe there's no easy way to do this!
Any ideas why the defined cell names aren't being read by the formula? Using the formula you suggest would mean entering the required dates into it each time I have different criteria, instead of just modifying a named cell, -- John "Max" wrote: One way is to use 2 SUMIFs, something like this: =SUMIF(Date,"="& -- "2 Jan 2008",Cost)-SUMIF(Date,"="& -- "8 Jan 2008",Cost) Above expression will return the cost totals for dates from 2 Jan 2008 to 7 Jan 2008 (inclusive). Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 Files: 352, Subscribers: 53, Downloads: 15,500 xdemechanik --- "John" wrote: Hi, Please help as I am slowly going mad - I know there must be a simple solution to the problem but I have lost the plot trying to find it! I have a simple sheet with 2 defined columns - Cost(A:A:) & Date (B:B:). I am trying to produce a simple formula that will total the costs from when given a date range. I have named cells "from" & "to" for the date criteria to entered and have used the SUMIF to try to calculate as required. =SUMIF(Date,"<=FromTo",Cost) - This does not work! If I change the formula as follows then it will return the correct values =SUMIF(Date,"<=01/03/20008",Cost) but I cannot get it to accept the defined names for the criteria, nor to calculate using 2 criteria. Any help would be greatly appreciated, as I am actually quite fond of my laptop and don't really want to throw it across the room!! -- John |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
On Sun, 6 Jul 2008 02:23:01 -0700, John wrote:
Hi, Please help as I am slowly going mad - I know there must be a simple solution to the problem but I have lost the plot trying to find it! I have a simple sheet with 2 defined columns - Cost(A:A:) & Date (B:B:). I am trying to produce a simple formula that will total the costs from when given a date range. I have named cells "from" & "to" for the date criteria to entered and have used the SUMIF to try to calculate as required. =SUMIF(Date,"<=FromTo",Cost) - This does not work! If I change the formula as follows then it will return the correct values =SUMIF(Date,"<=01/03/20008",Cost) but I cannot get it to accept the defined names for the criteria, nor to calculate using 2 criteria. Any help would be greatly appreciated, as I am actually quite fond of my laptop and don't really want to throw it across the room!! =SUMIF(Date,"=" & From,Cost) - SUMIF(Date,"" & To, Cost) will total costs that are in the range of "From" to "To" inclusive --ron |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
On Sun, 6 Jul 2008 03:56:00 -0700, John wrote:
Any ideas why the defined cell names aren't being read by the formula? See my solution for how to do this. --ron |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
Thanks Ron,
That's exactly what I needed - appreciated -- John "Ron Rosenfeld" wrote: On Sun, 6 Jul 2008 02:23:01 -0700, John wrote: Hi, Please help as I am slowly going mad - I know there must be a simple solution to the problem but I have lost the plot trying to find it! I have a simple sheet with 2 defined columns - Cost(A:A:) & Date (B:B:). I am trying to produce a simple formula that will total the costs from when given a date range. I have named cells "from" & "to" for the date criteria to entered and have used the SUMIF to try to calculate as required. =SUMIF(Date,"<=FromTo",Cost) - This does not work! If I change the formula as follows then it will return the correct values =SUMIF(Date,"<=01/03/20008",Cost) but I cannot get it to accept the defined names for the criteria, nor to calculate using 2 criteria. Any help would be greatly appreciated, as I am actually quite fond of my laptop and don't really want to throw it across the room!! =SUMIF(Date,"=" & From,Cost) - SUMIF(Date,"" & To, Cost) will total costs that are in the range of "From" to "To" inclusive --ron |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
On Sun, 6 Jul 2008 06:20:01 -0700, John wrote:
Thanks Ron, That's exactly what I needed - appreciated -- John You're welcome. Glad to help. Thanks for the feedback. --ron |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Formulas - slowly going mad!!
Sorry, think I missed your line on this:
I have named cells "from" & "to" But if you look closely, the 2 suggested expressions are quite similar. -- Max Singapore http://savefile.com/projects/236895 Files: 352, Subscribers: 53, Downloads: 15,500 xdemechanik --- "John" wrote in message ... Thanks Max, I can't believe there's no easy way to do this! Any ideas why the defined cell names aren't being read by the formula? Using the formula you suggest would mean entering the required dates into it each time I have different criteria, instead of just modifying a named cell, -- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel work slowly? | Excel Discussion (Misc queries) | |||
Excel file opens slowly | Excel Discussion (Misc queries) | |||
Excel responding slowly | Excel Discussion (Misc queries) | |||
Excel 97 files open very slowly | Excel Discussion (Misc queries) | |||
excel 2002 opens slowly | Excel Discussion (Misc queries) |