SUMPRODUCT & INDIRECT?

L

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top