Counting & Matching Text
 

Dear Experts,

Please consider this & help me to sort this out:

I have a Data Sheet contains 5 col namely: Emp; From; To; #OfDays; LeaveType
I fill these columns on daily-basis using designed VB Form as below

Emp From To #OfDays LeaveType
-----------------------------------------------------------------------
Mr. A 01-Jan-06 02-Jan-06 2 Sick
Mr. B 08-Jan-06 08-Jan-06 1 Casual
Mr. C 12-Jan-06 16-Jan-06 5 Earned
Mr. A 18-Jan-06 20-Jan-06 3 Casual
....so on

Now, I have a complete list of Employees & we have 3 Leave Category i.e.
Sick, Casual & Earned Leave

What I want to do from above lengthy data is:

Emp Sick Casual Earned Total Leaves
------------------------------------------------------------
Mr. A 2 3 0 5
Mr. B 0 1 0 1
Mr. C 0 0 5 5
....so on

I tried the Vlookup formula but I can not use it with Count forumla...Is it
possible?

I tried my best to explain the situation, If still I am unclear, please ask

Best regards,
Murtaza

PS: I know you people must have already provide the solution for this kind
of problem, but how can I search it in Excel Newsgroups.

Counting & Matching Text
 

It seems to me that the sumproduct function would be appropriate

col A col B col C col D col E
name from to days type
tom jan 1 jan 4 3 sick
dick feb 1 feb 9 8 earned
harry mar 1 mar 3 2 casual
tom jan 1 jan 4 3 sick
dick feb 1 feb 9 8 earned
harry mar 1 mar 3 2 casual


sick casual earned
row 10 tom xxxx
row 11 dick
row 12 harry


xxx = =sumproduct((a1:a7=a10)*(e1:e7="sick")

you should be able to work out the next to formulas and then add the
results in the final column

bob


--
bob777
------------------------------------------------------------------------
bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504
View this thread: http://www.excelforum.com/showthread...hreadid=511024

Counting & Matching Text
 

You could get the results you want using a Pivot Table and making leave type
a pivot column.
--
Kevin Backmann


"Murtaza" wrote:

Dear Experts,

Please consider this & help me to sort this out:

I have a Data Sheet contains 5 col namely: Emp; From; To; #OfDays; LeaveType
I fill these columns on daily-basis using designed VB Form as below

Emp From To #OfDays LeaveType
-----------------------------------------------------------------------
Mr. A 01-Jan-06 02-Jan-06 2 Sick
Mr. B 08-Jan-06 08-Jan-06 1 Casual
Mr. C 12-Jan-06 16-Jan-06 5 Earned
Mr. A 18-Jan-06 20-Jan-06 3 Casual
....so on

Now, I have a complete list of Employees & we have 3 Leave Category i.e.
Sick, Casual & Earned Leave

What I want to do from above lengthy data is:

Emp Sick Casual Earned Total Leaves
------------------------------------------------------------
Mr. A 2 3 0 5
Mr. B 0 1 0 1
Mr. C 0 0 5 5
....so on

I tried the Vlookup formula but I can not use it with Count forumla...Is it
possible?

I tried my best to explain the situation, If still I am unclear, please ask

Best regards,
Murtaza

PS: I know you people must have already provide the solution for this kind
of problem, but how can I search it in Excel Newsgroups.