Excel Formula

[rmishra4]

Using Excel Formula(Sumproduct)or Countifs or any other formula: how to count the number of present, Absent,CL, WO in the Attendance database for different processes or for the employees.

 

[xld]

Use COUNTIFS


=COUNTIFS(name_range,"somebody's name",attendance_range,"Absent")

 

[rmishra4]

I have the Database somethibg like this:


NAME Process ID 1-JUN 2-JUN 3-JUN 4-JUN 5-JUN

A AB 12345 P W W P P

B AC 12346 P W W P P

C AD 12348 P W W P P

D AF 12348 P W W P P


Now i have to count the number of "P" in for particular process for the particualr date tigether.


Please provide me the solution...

 

[rmishra4]

=SUMPRODUCT((Processlist=D5)*(Datelist=D6)*(Attendance!F2:F201="P")*Attendancedata)....have used this formula however it is not coming...

 

[rmishra4]

I have used your formula...=COUNTIFS(Processlist,$D$5,Attendancedata,"P")..however it is showing #VALUE! error!!...

 

[SirJB7]

Hi, rmishra4!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Would you please upload a sample file? If needed, uploading guidelines may be found at second green sticky post.


Regards!

 

[rmishra4]

Hi SirJB7,

I read the green sticky topics of your forum.. I am unable to upload the file from office..due to some restrictions..

However..


I have the Database something like this:


NAME Process ID 1-JUN 2-JUN 3-JUN 4-JUN 5-JUN

A AB 12345 P W W P P

B AC 12346 P W W P P

C AD 12348 P W W P P

D AF 12348 P W W P P


Now i have to count the number of "P" in for particular process for the particualr date together.


I want to do it with Excel formula....can u please help me...

 

[SirJB7]

Hi, rmishra4!


I saw your introduction post, thanks a lot for sharing that with us.


I understand firewall policies, don't worry, we'll keep this way for now.


I saw your "Database" (as a MIS specialist you shouldn't call it a database, F. Codd will raise from anywhere and talk to you about normal forms...) :=)

Focusing, what do you mean by counting the number of "P" for a particular process date? The word together confuses me. Why? Without 'together' the count of P for process/date will be 0 zero or 1 one; with it, do you mean a date range from/to or an uninterrupted sequence of P, or anything else?


Regards!

 

[rmishra4]

Hi,

What I mean here is Suppose I select.. Date-as "1-JUN" and then I select process name-as "AB'..then it should give the count of Total "P" ( Present Satus) of that particular process(AB) for that given date 1- JUN)...hope now I am bit clear...

 

[SirJB7]

Hi, rmishra4!

If I follow your initial table, that will only give you 0 (zero, ="P") or 1 (one, <>"P" or ="W"). Am I wrong?

Regards!

 

[Hui]

To count the number of P on 1 June & AB

=SUMPRODUCT((A2:A5="AB")*(C1:G1=DATE(2012,6,1))*(C2:G5="P"))

 

[SirJB7]

@Hui

Hi!

Thanks, I'd be arriving late.

Regards!