Excel Formulas - slowly going mad!!

  • Thread starter Thread starter John
  • Start date Start date
J

John

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,"<=From>To",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!!
 
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
 
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,
 
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,"<=From>To",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!!
Click to expand...

=SUMIF(Date,">=" & From,Cost) - SUMIF(Date,">" & To, Cost)

will total costs that are in the range of "From" to "To" inclusive
--ron
 
Back
Top