How to calculate the number of days present using formula?

[rajeshntiwari]

In below data: Out of 6 working days John was present for 3 days.

So how to get the answer 3, using formula? Can someone please help me. Thanks

NOTE: JOHN is attending the class twice in a day! 1st class in morning and 2nd class in evening.

Monday	John
Monday	John
Monday	Tim
Tuesday	John
Tuesday	David
Wednesday	David
Thursday	Smith
Friday	Smith
Saturday	John

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[shg]

Remove duplicates and then use a COUNTIF formula:

Row\Col	A	B
1	Monday	John
2	Monday	Tim
3	Tuesday	John
4	Tuesday	David
5	Wednesday	David
6	Thursday	Smith
7	Friday	Smith
8	Saturday	John

[xxcarlos20x]

With duplicates removed, something such as this would work:

=COUNTIF(B2:B8,"John")

[rajeshntiwari]

@protonLeah

Sorry about that! I have made the changes. Thanks

[AlKey]

Use this formula to get unique names in C2 and copy down

=IFERROR(INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF(C$1:C1,$B$2:$B$10),,),)),"")

and to count

in D2 and copy down

=SUMPRODUCT((B$2:B$10=C2)/COUNTIFS(A$2:A$10,A$2:A$10,B$2:B$10,B$2:B$10))

Row\Col	A	B	C	D
1	Day	Name	Unique Name	Count
2	Monday	John	John	3
3	Monday	John	Tim	1
4	Monday	Tim	David	2
5	Tuesday	John	Smith	2
6	Tuesday	David
7	Wednesday	David
8	Thursday	Smith
9	Friday	Smith
10	Saturday	John

[Tony Valko]

Here's one way...

Data Range

	A	B	C	D	E
1	Weekday	Student	------	Student	Count
2	Monday	John		John	3
3	Monday	John
4	Monday	Tim
5	Tuesday	John
6	Tuesday	David
7	Wednesday	David
8	Thursday	Smith
9	Friday	Smith
10	Saturday	John

This array formula** entered in E2:

=SUM(IF(FREQUENCY(IF(B2:B10=D2,MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[rajeshntiwari]

@Alkey @Tony Valko

Both the formulas are working perfect! Thank you so much for your time

[Tony Valko]

You're welcome. We appreciate the feedback!

[daddylonglegs]

Given that there are are a finite number of days possible you could turn this around a little and use this non-array formula in E2 (assuming Tony's suggested setup)

=SUM((COUNTIFS(B:B,D2,A:A,TEXT({1,2,3,4,5,6,7},"dddd"))>0)+0)