SUMPRODUCT & INDIRECT?

[lou031205]

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?

 

[T. Valko]

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!

 

[T. Valko]

I did test the INDIRECT stuff!

Based on a *static* range.

 

[lou031205]

Thank you - works really well now!

Based on a *static* range.

 

[T. Valko]

You're welcome. Thanks for the feedback!