You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LongTermNoob" wrote in message
...
Ah, now I see. Interesting stuff. Thanks Biff.

"T. Valko" wrote:

With SUMIF you were testing for the integer 2005 (year). The true
underlying
value of a date doesn't contain a specific integer (such as 2005) for the
year.

Dates are really just numbers formatted to look like dates. For example,
1/1/2008 is the *displayed* value of the date. However, the *true
underlying* value for that date is 39448. This number is called the
serial
date. This means that 1/1/2008 is the 39,448th day since a base date of
1/1/1900. 1/1/1900 is serial date 1. 1/1/2008 is serial date 39448.

So, the SUMIF was looking for the integer 2005 compared to the serial
dates
like 39448. The YEAR function can calculate the year number from a serial
date. =YEAR(39448) returns 2008.

--
Biff
Microsoft Excel MVP


"LongTermNoob" wrote in message
...
Many thanks Biff, it works a treat. I originally got a #VAL error and
when
I
checked it through found two date fields with "N/A" in them. Could this
have
caused the problem with SUMIF, or is it a definite no-go for this type
of
evaluation?

Thanks again, and thanks to Carim for trying to help.



"T. Valko" wrote:

You would need to use SUMPRODUCT since you're testing an array of
dates
for
the year:

=SUMPRODUCT(--(YEAR(INDIRECT("'"&A3&"'!E8:E71"))=2005),INDIRECT( "'"&A3&"'!D8:D71"))

--
Biff
Microsoft Excel MVP


"LongTermNoob" wrote in
message
...
Thanks for the info Carim, but it's still returning 0. This is the
actual
formula, rather than an example:

=SUMIF(INDIRECT("'"&A3&"'!E8:E71"),"2005",INDIRECT ("'"&A3&"'!D8:D71"))

When I evaluate the formula it appears to be looking in all the
right
places.

The date format in E8:E71 is dd-mmm-yy in case that makes a
difference.


"Carim" wrote:

Hi,

The issue is with "*05" ... "2005" works fine ..

HTH