Countif function

[xiaor1212]

Hi There, I am need urgent help from expert here:

Member	Total of error	Last error/ claim Date	Today	Total offday
Ee001	3	04/11/2013	01/12/2013
Ee002	5	02/11/2013	01/12/2013
Ee003	7	08/11/2013	01/12/2013
Ee004	9	07/11/2013	01/12/2013
Ee005	1	07/10/2013	01/12/2013
Ee006	15	09/11/2013	01/12/2013
CSID	01/11/2013	02/11/2013	03/11/2013	04/11/2013	05/11/2013	06/11/2013	07/11/2013	08/11/2013
Ee001	AL	6AM-4PM	6AM-4PM	OFF	OFF	6AM-4PM	6AM-4PM	6AM-4PM
Ee002	OFF	AL	AL	6AM-4PM	6AM-4PM	OFF	OFF	6AM-4PM
Ee003	AL	OFF	OFF	10AM-8PM	6AM-4PM	6AM-4PM	6AM-4PM	6AM-4PM
Ee004	OFF	AL	AL	AL	AL	AL	OFF	OFF
Ee005	6AM-4PM	6AM-4PM	6AM-4PM	6AM-4PM	AL	Special	OFF	OFF
Ee006	10AM-8PM	10AM-8PM	OFF	OFF	10AM-8PM	10AM-8PM	10AM-8PM	10AM-8PM

<colgroup><col span="9"></colgroup><tbody>
</tbody>

how is the formula to count the total offday of each Ee-ID start from "Last Error/Claimed Data"???
should i use count if or vlookup or any others formula would be better?

your prompt response is highly appreciated

 

[poiu]

Hi xiaor1212,

1. First put each table on a different worksheet, to keep your data separate when your formulae are doing lookups.

2. Start with a sumif function to count the number of occurances of "OFF" in the row for Ee001.

3. Then upgrade your sumif function so that your row number is defined automatically. This is done using an INDEX MATCH combination (google it, e.g. How to Use INDEX MATCH) that will tell you the row number that you want your sumif function to look at.

Let us know how you get on,

Poiu