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

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

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

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

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

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

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

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