Problem with Formula

[John]

I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc

=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail))

My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)

Any ideas appreciated


Thanks

 

[Don Guillett]

Try to sum absencedetail

=SUMPRODUCT((DailyDate=F67)*(StaffNumber=$C$11)*AbsenceDetail)

 

[Aladin Akyurek]

In case AbsenceDetail houses text-formatted numbers instead of true numbers:

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--AbsenceDetail)

 

[Bob Phillips]

Are you saying that the AbsenceDetail cell contains the text Holiday? If so,
that will sum as 0.

 

[John]

Aladin / Don

Getting #values returned, I expect the word 'Holidays'

 

[John]

Thats correct Bob, it contains Holiday and returns 0, but I want it to
return 'Holiday'

 

[Aladin Akyurek]

Can you post a few rows from AbsenceDetail?

Aladin / Don

Getting #values returned, I expect the word 'Holidays'

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[John]

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

Can you post a few rows from AbsenceDetail?

Aladin / Don

Getting #values returned, I expect the word 'Holidays'

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Bob Phillips]

You cannot sum text. And what if many cells c ontain Holiday, what do you
want then? Or some don't.

Maybe you want

=SUMPRODUCT(--(DailyDate=F67),--(StaffNumber=$C$11),--(AbsenceDetail="Holida
y"))

 

[John]

Thanks Bob, what I want is whatever value is within the field associated
with the Range Name AbsenceDetail that equates to the DailyDate &
StaffNumber

Can't use hardcoded value of "Holidays' becuase there are quite a number of
different values that can be returned within the AbsenceDetail field

 

[Aladin Akyurek]

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

 

[Bob Phillips]

I think you will have to hardcode them, within an array. What are they?

 

[John]

Thanks Aladin, thats exactly what I'm looking for and with the correct
answer also

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

 

[John]

Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0))}

Any hints?

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

 

[John]

Think I have it if it's incorrect someone might let me know

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))))}

Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0))}

Any hints?

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays

[...]

 

[Aladin Akyurek]

John,

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))))}

is correct but expensive. How about using a 2-cell approach...

Y2 or any other convenient cell:

{=MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0)}

X2:

=IF(ISNUMBER(Y2),INDEX(AbsenceDetail,Y2),"")

which is confirmed with just enter?

Think I have it if it's incorrect someone might let me know

{=IF(ISNA(MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))))}

Aladin, I'm trying to supress #N/A that result from your formula (i.e. if
the DailyDate I'm looking for does not exist in the Database) and have
entered the following but it says I have too many arguments

{=(if(ISNA(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0)),"",(INDEX(AbsenceDetail,MATCH(1,(DailyDate=H70)*(StaffNumber=$C$11),0))}

Any hints?

It seems you have a lookup question...

Assuming that DailyDate, StaffNumber, and AbsenceDetail are names of the
relevant ranges in different columns:

=INDEX(AbsenceDetail,MATCH(1,(DailyDate=F67)*(StaffNumber=$C$11),0))

which must be confirmed with control+shift+enter, not just with enter.

John wrote:

Aladin, not sure how this will come out, but see below is the Row I am
looking at for the 09/05/05. First value 100 is in Column A, and the
AbsenceDetail is 'Holidays' in Column Q

100 09/05/05 0 0 0 0 0 0 0 0 0 0 0 FHOP Mgr HO Holidays


[...]