SUMIF & INDIRECT problems
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
|