Excel Formulas - slowly going mad!!

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!!
 
M

Max

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
 
J

John

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,
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Sumproduct 0
Excel Drag autofill only part of formula 3
macro to action formula 1
Excel Getting an annualized figure based on YTD in Excel 1
Add/Subtract columns from tab to tab in same file 3
formula for excel spreadsheet 9
Adding dates in columns 2
Sumif formula 4

Top