Conditional sum - date conditions

[jd-mateng]

Hi,

I've inherited, so to speak, a large Excel workbook detailing our
production losses and waste amounts. The losses and wastes totals, per
day, are entered in one worksheet, and then are summarised by category,
per month, in a second worksheet. Beforehand, all the summation
formulas were entered manually, so I'm trying to automate this.

Sample of data entry sheet:

Month-Year Date Cat#1 Cat#2 Cat#3 Cat#4
Feb-2003 1-Feb-03 0.00 0.00 0.00 3.40
Feb-2003 2-Feb-03 0.00 7.95 0.00 0.00
Feb-2003 3-Feb-03 0.00 4.50 0.00 0.00
Feb-2003 4-Feb-03 0.00 0.00 0.00 0.00
etc...

The 'Month-Year' column is to help with calculations.

I've tried using the Conditional Sum wizard to help with the montly
summary calculations but when trying to select a value to match in the
Month-Year column, it won't display a list of dates that are in the
spreadsheet, just dates from next year onwards.

For example: I want condition 'Month-Year=Apr-2004', but if I try that,
I get a sum of zero instead of 20; the drop-down list next to the value
box has dates starting from Feb 2007.

What the heck is going on?! If you have some clue as to why Excel is
behaving this way, please respond.

Regards,

JD

 

[jd-mateng]

Hi all,

I managed to solve the problems with SUMIF, and converting the
evaluated ranges to text. Appears Excel's methods of calculating dates
(absolute no. of seconds since an arbitrary datum) gets in the way. An
answer isn't urgent anymore, but this may be a discussion point. My
thanks to the unknowns who may have read this, and may be yet working
on potential solutions!

JD