Counting a number only once if it appears multiple times.

[Leslielashawn]

Hi there!
I have a spreadsheet for a classroom. It is divided into the days of the week. Within those days, it is further divided into the meals we serve the children: Breakfast, Lunch, Snack. The teacher enters a "1" for every meal the child is present for, a "0" if the child is absent during that meal. The final column is where I am having an issue. This is the Average Daily Attendance column. It is not a total, nor an average (despite the title). I need a formula that will tell me if a child was there at all for any given day but will only count them once, even if they were there for all three meals. The maximum number to be counted is 5 because even if a child is present for all three meals, all five days, we only count them once for each day. I am attaching a sample for clarification. I need someone smarter than I to help!

[Phuocam]

Try this in R5:

=(COUNTIF(C5:E5,1)>0)+(COUNTIF(F5:H5,1)>0)+(COUNTIF(I5:K5,1)>0)+(COUNTIF(L5:N5,1)>0)+(COUNTIF(O5:Q5,1)>0)

Or try:

=SUMPRODUCT(--(COUNTIFS(OFFSET(B5,0,{1,4,7,10,13},1,3),1)>0))

[wk9128]

Cell R5 formula , Drag down

Please Login or Register  to view this content.

[Leslielashawn]

Thank you so much! This will save me a tremendous amount of time and effort.

[wk9128]

@Leslielashawn You're Welcome. Glad to help . Thank You for the feedback.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. Whoever helps you, give it to whoever you like

[HansDouwe]

This is also a possibility for cell R5: =OR(C5,D5,E5)+OR(F5,G5,H5)+OR(I5,J5,K5)+OR(L5,M5,N5)+OR(O5,P5,Q5)

[FlameRetired]

Drawing on HansDouwe's approach similarly in S5 and fill down

Please Login or Register  to view this content.

[soledad]

Array formula in R5

Please Login or Register  to view this content.

Ctrl+Shift+Enter