Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
Hi,
The issue is with "*05" ... "2005" works fine .. HTH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
.... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF & INDIRECT problems
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with indirect | Excel Discussion (Misc queries) | |||
sumif + indirect | Excel Discussion (Misc queries) | |||
Indirect and Sumif Syntax Problems | Excel Discussion (Misc queries) | |||
Problems with PULL function, INDIRECT.EXT and so forth | Excel Worksheet Functions | |||
Is "INDIRECT" the cause of these problems? | Excel Discussion (Misc queries) |