Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT & INDIRECT?
Hi
I am getting steadily to grips with excel's more interesting functions, but am definitely no expert! I have a spreadsheet with the following ranges: Sheet 1 - Absence Summary. Sheet 2 - Formula Data Sheet (Hidden from view) Sheets 3-75 - Individual employee absence sheets titled '"Surname", "Initial"' I have the following named ranges: Absence_Date: Workbook level named range. One_year_ago: Workbook level. Absence_Code: Worksheet level. Workdays_since_start_date: Worksheet level Workdays_in_last_year:Worksheet level The following ranges are used (e.g.) Absence Summary B9 = (Text) Surname Absence Summary C9 = (Text) Initial Absence Summary E9 = (Date) Start Date I have entered the following formula into Absence Summary F9: =(SUMPRODUCT(-- (ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(-- (ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'! Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year What I wanted it to do is to look up the sheet titled "Surname", "Initial", and count all cells that contain either an "a", "u", "s", or "us" in the named range "Absence_Code" between one year ago and today, then divide that number by the number of workdays in the last year specified in the formula data sheet in named range "Workdays_in_last_year". I get the result "Ref" so I am obviously doing something wrong. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT & INDIRECT?
The problem is in INDIRECT. The syntax is incorrect.
(INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code"={"A","U","S","US"})) Try it like this: (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}) Is the named range Absence_Code a *static* range or is it dynamic? INDIRECT has problems with dynamic ranges. Also, you should use the same operator throughout the entire formula. You have both "--" and "*". You also have some extra ( ) that aren't needed. Here it is cleaned up: =SUMPRODUCT((ABSENCE_DATE=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*( ABSENCE_DATE<=TODAY())*(INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}))/workdays_in_last_year I didn't test all the date stuff but I did test the INDIRECT stuff! -- Biff Microsoft Excel MVP "lou031205" wrote in message ups.com... Hi I am getting steadily to grips with excel's more interesting functions, but am definitely no expert! I have a spreadsheet with the following ranges: Sheet 1 - Absence Summary. Sheet 2 - Formula Data Sheet (Hidden from view) Sheets 3-75 - Individual employee absence sheets titled '"Surname", "Initial"' I have the following named ranges: Absence_Date: Workbook level named range. One_year_ago: Workbook level. Absence_Code: Worksheet level. Workdays_since_start_date: Worksheet level Workdays_in_last_year:Worksheet level The following ranges are used (e.g.) Absence Summary B9 = (Text) Surname Absence Summary C9 = (Text) Initial Absence Summary E9 = (Date) Start Date I have entered the following formula into Absence Summary F9: =(SUMPRODUCT(-- (ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(-- (ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'! Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year What I wanted it to do is to look up the sheet titled "Surname", "Initial", and count all cells that contain either an "a", "u", "s", or "us" in the named range "Absence_Code" between one year ago and today, then divide that number by the number of workdays in the last year specified in the formula data sheet in named range "Workdays_in_last_year". I get the result "Ref" so I am obviously doing something wrong. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT & INDIRECT?
I did test the INDIRECT stuff!
Based on a *static* range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is in INDIRECT. The syntax is incorrect. (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code"={"A","U","S","US"})) Try it like this: (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}) Is the named range Absence_Code a *static* range or is it dynamic? INDIRECT has problems with dynamic ranges. Also, you should use the same operator throughout the entire formula. You have both "--" and "*". You also have some extra ( ) that aren't needed. Here it is cleaned up: =SUMPRODUCT((ABSENCE_DATE=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*( ABSENCE_DATE<=TODAY())*(INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}))/workdays_in_last_year I didn't test all the date stuff but I did test the INDIRECT stuff! -- Biff Microsoft Excel MVP "lou031205" wrote in message ups.com... Hi I am getting steadily to grips with excel's more interesting functions, but am definitely no expert! I have a spreadsheet with the following ranges: Sheet 1 - Absence Summary. Sheet 2 - Formula Data Sheet (Hidden from view) Sheets 3-75 - Individual employee absence sheets titled '"Surname", "Initial"' I have the following named ranges: Absence_Date: Workbook level named range. One_year_ago: Workbook level. Absence_Code: Worksheet level. Workdays_since_start_date: Worksheet level Workdays_in_last_year:Worksheet level The following ranges are used (e.g.) Absence Summary B9 = (Text) Surname Absence Summary C9 = (Text) Initial Absence Summary E9 = (Date) Start Date I have entered the following formula into Absence Summary F9: =(SUMPRODUCT(-- (ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(-- (ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'! Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year What I wanted it to do is to look up the sheet titled "Surname", "Initial", and count all cells that contain either an "a", "u", "s", or "us" in the named range "Absence_Code" between one year ago and today, then divide that number by the number of workdays in the last year specified in the formula data sheet in named range "Workdays_in_last_year". I get the result "Ref" so I am obviously doing something wrong. Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT & INDIRECT?
Thank you - works really well now!
On Nov 3, 7:01 pm, "T. Valko" wrote: I did test the INDIRECT stuff! Based on a *static* range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is in INDIRECT. The syntax is incorrect. (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code"={"A","U","S","US"})) Try it like this: (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}) Is the named range Absence_Code a *static* range or is it dynamic? INDIRECT has problems with dynamic ranges. Also, you should use the same operator throughout the entire formula. You have both "--" and "*". You also have some extra ( ) that aren't needed. Here it is cleaned up: =SUMPRODUCT((ABSENCE_DATE=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*( ABSENCE_DATE<=TODAY())*(INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}))/workdays_in_last_year I didn't test all the date stuff but I did test the INDIRECT stuff! -- Biff Microsoft Excel MVP "lou031205" wrote in message oups.com... Hi I am getting steadily to grips with excel's more interesting functions, but am definitely no expert! I have a spreadsheet with the following ranges: Sheet 1 - Absence Summary. Sheet 2 - Formula Data Sheet (Hidden from view) Sheets 3-75 - Individual employee absence sheets titled '"Surname", "Initial"' I have the following named ranges: Absence_Date: Workbook level named range. One_year_ago: Workbook level. Absence_Code: Worksheet level. Workdays_since_start_date: Worksheet level Workdays_in_last_year:Worksheet level The following ranges are used (e.g.) Absence Summary B9 = (Text) Surname Absence Summary C9 = (Text) Initial Absence Summary E9 = (Date) Start Date I have entered the following formula into Absence Summary F9: =(SUMPRODUCT(-- (ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(-- (ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'! Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year What I wanted it to do is to look up the sheet titled "Surname", "Initial", and count all cells that contain either an "a", "u", "s", or "us" in the named range "Absence_Code" between one year ago and today, then divide that number by the number of workdays in the last year specified in the formula data sheet in named range "Workdays_in_last_year". I get the result "Ref" so I am obviously doing something wrong. Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT & INDIRECT?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "lou031205" wrote in message ups.com... Thank you - works really well now! On Nov 3, 7:01 pm, "T. Valko" wrote: I did test the INDIRECT stuff! Based on a *static* range. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The problem is in INDIRECT. The syntax is incorrect. (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code"={"A","U","S","US"})) Try it like this: (INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}) Is the named range Absence_Code a *static* range or is it dynamic? INDIRECT has problems with dynamic ranges. Also, you should use the same operator throughout the entire formula. You have both "--" and "*". You also have some extra ( ) that aren't needed. Here it is cleaned up: =SUMPRODUCT((ABSENCE_DATE=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*( ABSENCE_DATE<=TODAY())*(INDIRECT("'"&$B9&", "&$C9&"'!Absence_Code")={"A","U","S","US"}))/workdays_in_last_year I didn't test all the date stuff but I did test the INDIRECT stuff! -- Biff Microsoft Excel MVP "lou031205" wrote in message oups.com... Hi I am getting steadily to grips with excel's more interesting functions, but am definitely no expert! I have a spreadsheet with the following ranges: Sheet 1 - Absence Summary. Sheet 2 - Formula Data Sheet (Hidden from view) Sheets 3-75 - Individual employee absence sheets titled '"Surname", "Initial"' I have the following named ranges: Absence_Date: Workbook level named range. One_year_ago: Workbook level. Absence_Code: Worksheet level. Workdays_since_start_date: Worksheet level Workdays_in_last_year:Worksheet level The following ranges are used (e.g.) Absence Summary B9 = (Text) Surname Absence Summary C9 = (Text) Initial Absence Summary E9 = (Date) Start Date I have entered the following formula into Absence Summary F9: =(SUMPRODUCT(-- (ABSENCE_DATE=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))*(-- (ABSENCE_DATE<=(TODAY())))*(INDIRECT("'"&$B9&", "&$C9&"'! Absence_Code"={"A","U","S","US"}))))/workdays_in_last_year What I wanted it to do is to look up the sheet titled "Surname", "Initial", and count all cells that contain either an "a", "u", "s", or "us" in the named range "Absence_Code" between one year ago and today, then divide that number by the number of workdays in the last year specified in the formula data sheet in named range "Workdays_in_last_year". I get the result "Ref" so I am obviously doing something wrong. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with using INDIRECT with SUMPRODUCT and ROW() | Excel Worksheet Functions | |||
Need help with using SUMPRODUCT with INDIRECT | Excel Worksheet Functions | |||
Help with Sumproduct with Indirect | Excel Worksheet Functions | |||
sumproduct & indirect | Excel Worksheet Functions | |||
Indirect range in SUMPRODUCT? | Excel Discussion (Misc queries) |