Attendance Register how to count....

O

offensive16

New Member
Joined
May 9, 2015
Messages
3
Hi there,
Like the above recently I've created an attendance register. I have names in rows and the dates in columns. If someone is either present, absent or on holiday in the cells relatively I type values P, A or H.

Below the main table with my data I created a data validation list from which I want to choose a particular name and I want excel to count for me how many days that person was either present, absent or on holiday.

I've tried many ways already using countifs, countif, and if functions and acutally it worked but it doesn't happen dynamically. If I change the name of a person I want the values to change as well.



Thank you in advance for help.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Welcome to the forum

Sample data can be very useful most times

See example below:

Excel 2013
ABCDEFGHIJK
1Name01-May-1502-May-1503-May-1504-May-1505-May-1506-May-1507-May-1508-May-1509-May-1510-May-15
2janePPPHPAHPAH
3johnPAAPPPPPPA
4doeHPAAAAAHPP
5jimAPPAPPHHAA
6markAAAPHAPAHP
7SundayHAAPHAHPAH
8
9NamePAH
10mark352
Sheet3
Cell Formulas
RangeFormula
B10=SUMPRODUCT(($A$2:$A$7=$A$10)*($B$2:$K$7=B$9))
C10=SUMPRODUCT(($A$2:$A$7=$A$10)*($B$2:$K$7=C$9))
D10=SUMPRODUCT(($A$2:$A$7=$A$10)*($B$2:$K$7=D$9))
 
Last edited:
Upvote 0
So it looks like

Code: 
[TABLE="width: 590"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]01/01/15[/TD]
[TD="align: right"]02/01/15[/TD]
[TD="align: right"]03/01/15[/TD]
[TD="align: right"]04/01/15[/TD]
[TD="align: right"]05/01/15[/TD]
[TD="align: right"]06/01/15[/TD]
[TD="align: right"]07/01/15[/TD]
[TD="align: right"]08/01/15[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Bert[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Think you're formula should be

=SUMPRODUCT(($A$2:$A$7=$A14)*($B$2:$I$7=B$13))
 
Upvote 0
avhaug.jpg
[/IMG]




here is how it looks like.
 
Upvote 0
gaz_chops said:
Think you're formula should be

=SUMPRODUCT(($A$2:$A$7=$A14)*($B$2:$I$7=B$13))
Click to expand...
Thanks, I noticed and have updated it, copied to a new sheet and the absolute ranges didn't change.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
240
Fluff
Fluff
J
  • Solved
Formula for attendance daily summary
Replies
6
Views
351
kevin9999
K
M
  • Question
Excel attendance patterns
2
Replies
10
Views
325
mematedavev
M
L
  • Question
Count if consecutive value is repeated 3 times.
Replies
2
Views
899
Fluff
Fluff
A
  • Solved
Counting the multiple consecutive occurrences of a fix sumtotal
Replies
4
Views
521
alan581
A

Forum statistics

Threads
1,214,782
Messages
6,121,532
Members
449,037
Latest member
tmmotairi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top