SUMIF & INDIRECT problems

[LongTermNoob]

I posted before with a problem which was answered and then tacked this onto
it, but I think it's worth starting another thread as it has gone unanswered
(probably already consigned to history by most!).

I have a workbook for all our capital projects. I have simplified it to
include one "summary" sheet and a separate sheet for each project, where all
information is stored in columnar format (e.g. a column for "committed", a
column for "Invoiced", etc.). This information may be spread over several
months or several years, and I need to get information from individual years
into separate cells on the summary sheet. The project sheets are named by
Project number, which is also the value in the first column of the Summary
sheet

So the summary sheet looks something like:

A ... D

1 Proj # Commit '05

2 00689 50000.00
3 00696 32500.00


and the project sheet (e.g. 00689) something like:

A B C D

1 Order Date Order Value Invoice Amount Invoice Date

2 12-Dec-04 15000.00 15000.00 25-Jan-05
3 15-Mar-05 25000.00 25000.00 01-May-05
4 21-Apr-05 25000.00 15000.00 15-Jan-06

So, I need to sum the values on the project sheet in Column B where Column A
is in 2005 in order to get my Commit for 2005 and put it into Column D of the
Summary sheet. The date column does not always precede the data I need to sum
(e.g. for Invoices) so Lookups are not an option as far as I understand it.

I would like to use the values in column A in the Summary sheet to point to
the worksheet where the information is as entering all the Project numbers
would take half a life-time. The following does not work, but may give you a
better idea of what I am trying to do:


=SUMIF(INDIRECT("'"&A2&"'!A8:A71"),"*05",INDIRECT("'"&A2&"'!B8:B71"))

The above returns 0, event though there are orders placed in nn-xxx-05.

Any assistance would be gratefully received.

 

[Carim]

Hi,

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

HTH

 

[Carim]

.... forgot to mention you cannot use wildcards for numbers, it is only
feasible with text ...

If you must use numbers and wilcards, you would need to use
sumproduct() instead of sumif() ...

HTH

 

[LongTermNoob]

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&"'!D871"))

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.

 

[T. Valko]

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&"'!D871"))

 

[LongTermNoob]

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]

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.

 

[LongTermNoob]

Ah, now I see. Interesting stuff. Thanks Biff.

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!