Find function with additional search criteria

**Neil07979** · 07-02-2009, 06:52 AM

Hi all,

I'm attempting to create a calendar of absence across the team, using a download of data from a HR system.

The data contains various columns, with 2 in particular that I am trying to summarise on. SID : Username; & AbsenceDate : A list of dates.

Within the summary page - I have a table that shows me SIDs as a list (Column C), and Dates going across (Row 3). I'm trying to get the table to then identify if that person is absent for that particular day, therefor creating a calendar effect absence tracker.

I have attemted the below - using the array function, searching initially for the SID, and then searching for text (C$3 = Date) within the AbsenceDate column of the data.

I just cant seem to get the Sum(If( function to work with the Find function.

Please Login or Register  to view this content.

The data could also contain multiple entries for each SID, so need to be able to pick any of them up.

Any help is greatly appreciated.

I have attached an example file for ease of understanding what I'm trying to do.

Regards

**rwgrietveld** · 07-02-2009, 07:32 AM

Place this in C4:

=SUMPRODUCT(--($B4=SID),IF(ISERR(FIND(","&TEXT(C$3,"d-m-yyyy"),AbsenceDate,1)),0,1))

Note that I use the ","&TEXT(C$3,"d-m-yyyy") and this means the data entry needs to follow this text pattern. Maybe you should change that as it is easy to make a typo.

Gr,
Ricardo

**DonkeyOte** · 07-02-2009, 07:44 AM

I would probably opt for a variant of that approach, namely:

D4: =SUMPRODUCT(--(SID=$B4),--ISNUMBER(SEARCH(","&TEXT(D$3,"d-m-yyyy"),","&AbsenceDate)))
applied across matrix

**Neil07979** · 07-02-2009, 10:33 AM

Thanks both - seems to work fine at the moment.

Out of interest - what does the "--" do before each array?

Thanks for your help

**DonkeyOte** · 07-02-2009, 10:38 AM

Re: --

This is the double unary operator and is used in essence to coerce Booleans (True/False) to integer equivalents (1/0 respectively) - for more info. on Sumproduct and coercion see the link in my sig to Bob Phillips' white paper on the subject.

The advantage of the latter SUMPRODUCT formula is that it does not require to be set as an Array (ie use of CTRL + SHIFT + ENTER to commit the formula, the former Sumproduct does given use of IF)
Generally speaking if you need to CSE a SUMPRODUCT you may as well use a normal array function given a SUMPRODUCT is still processed along the same lines as a CSE array - it's advantage over a CSE Array is that it does not require CSE and is thus deemed slightly more robust to end user adaptation.

I hope that helps.