max value required

**SAMEEP** · 03-21-2011, 02:59 PM

GUys, Can you please check the attached file and see if there can be a formula for attached report.

**SAMEEP** · 03-21-2011, 03:11 PM

Is it ok now?

**ChemistB** · 03-21-2011, 03:55 PM

Here are some options. In I3
=MAX(IF($A$3:$A$100=F3,$D$3:$D$100)) entered as an array formula (CNTRL SHFT ENTER). You'll see brackets around the formula { }

In H3
=SUMPRODUCT(--($A$3:$A$100=F3),--($D$3:$D$100=I3),$C$3:$C$100)

I haven't come up with a formula for G3 yet that doesn't involve dummy columns. Is it okay to use dummy columns?

**SAMEEP** · 03-21-2011, 03:59 PM

YES! It is fine to use dummy colums!

**SAMEEP** · 03-21-2011, 04:10 PM

it aint working properly! Can you please complete it on the sheet and send it!

**ChemistB** · 03-21-2011, 05:01 PM

Okay, I inserted a column A and set that equal to = B3&E3. I left it open but you can hide that column without affected performance.

Then, since we have that column, I went to VLOOKUPs for Zone and Date like so

=VLOOKUP(G3&J3, $A$3:$D$51,3,FALSE) for zone
=VLOOKUP(G3&J3, $A$3:$D$51,4, FALSE) for Date

See attachment.

**SAMEEP** · 03-22-2011, 10:11 AM

beautiful! You are the best

**SAMEEP** · 07-05-2012, 03:04 PM

All trying to create a formula to calculate attendance where we have the raw data in the below mentioned sequence:
Code Date status
SAM 07-06 PRESENT
SAM 07-07 ABSENT
SAM 07-08 HALF DAY

NOW THE TRACKER IS IN THE FOLLOWING FORMAT
CODE 07-06 | 07-07 |07-08

NOW I NEED A FORMULA WHICH WILL EXTRACT INFORMATION FOR THE CODE FOR THE DATES IN INCREMENT