Advanced Countif/H/VLOOKUP Function
 

Hi All,

So I have a fairly basic calcualtion that I need to do, however structuring the correct formula to use is what seems to be the problem. I have a worksheet where I need to Calculate leave available vs. leave taken.

Part of this would include building a trend pattern to see on which day of the week leave is most often taken. The purpose of this is to see if, as an example, a person is misusing sick leave on a friday to get an "Extend Weekend"

The problem I have is because of the way the document is layout. I have attached the document.

What I need to happen (Example):
1. SHEET (Leave_Matrix) Brian Windsor has taken SLV every friday in January.
2. SHEET (Leave_Trends_Balances) The table (Leave Trends C18:J25) needs to count/calculate 4 SLV on Friday and 1 ALV on Thursday for Brian Windsor.
3. SHEET (Leave_Trends_Balances) Should this trend continue into February as an example the Friday leave count should keep counting over other months. ie. by the end of February, Brian Windsor might have 6 Fridays as SLV.

I hope my explination makes sense of what I am trying to achieve?

Thanks....

Advanced Countif/H/VLOOKUP Function
 

On 6/06/2012 8:57 PM, SalientAnimal wrote:
Hi All,

So I have a fairly basic calcualtion that I need to do, however
structuring the correct formula to use is what seems to be the problem.
I have a worksheet where I need to Calculate leave available vs. leave
taken.

Part of this would include building a trend pattern to see on which day
of the week leave is most often taken. The purpose of this is to see if,
as an example, a person is misusing sick leave on a friday to get an
"Extend Weekend"

The problem I have is because of the way the document is layout. I have
attached the document.

What I need to happen (Example):
1. SHEET (Leave_Matrix) Brian Windsor has taken SLV every friday in
January.
2. SHEET (Leave_Trends_Balances) The table (Leave Trends C18:J25) needs
to count/calculate 4 SLV on Friday and 1 ALV on Thursday for Brian
Windsor.
3. SHEET (Leave_Trends_Balances) Should this trend continue into
February as an example the Friday leave count should keep counting over
other months. ie. by the end of February, Brian Windsor might have 6
Fridays as SLV.

I hope my explination makes sense of what I am trying to achieve?

Thanks....


+-------------------------------------------------------------------+
|Filename: Leave_Matrix_2012.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=403|
+-------------------------------------------------------------------+



Hi there

I mailed you your workbook back to your ExcelBanter email address.

I was able to get the result you required using " =SUMPRODUCT() " and
some Helper Columns.

If you do not get the file shoot me an email to noodnuttATgmailDOTcom
and I will send it back to you.

Cheers
Mick.