formula to search for Absent and Present

[hassan khansa]

Hi All;

i need some formula to search for absent and present student by date , please see attached Excel file .

Best Regard

[josephteh]

Is your Excel version still 2007? Please update if it's not.

[josephteh]

While waiting for formulas from the forum experts, may I suggest an alternative way:

1) Record the data in a tabular format
2) Use filter to show the data you want to see, e.g. Student A for the month of Sep 2021
3) Use SUMPRODUCT & SUBTOTAL formula to calculate number of days Present & Absent for the filtered items
4) I have formatted the data in an Excel table so that the table will auto-expand to include new data and formulas automatically adjusted

Absent students.png

[wk9128]

Cell B40 formula , Copy and Drag down

[hassan khansa]

hi Mr. Joseph and wk9128 thanks for reply to me,
i have to work on the shape as in attached file and my excel is 2007
i want when i enter the date in the cell B38, the formula should be in A40 to search for that date in the rows date ( Row 3 and Row 15 and Row 27 ) and get the first student Name who has any absent (A) in that day in any hour of the 4 hours . then in cell B40:E40 get the result absent (A) or present (P) of this student , and in cell A41 to search for the second student Name in this date who has any (A) under that date Ext...
Note: ignore and don't get the student who has 4 present (P) in all hours of that date .
Best Regards

[JeteMc]

This proposed solution for example 1 employs a helper column (Y) which may be moved and/or hidden for aesthetic purposes.
1. The helper column is populated using formulas like the following which must be updated for each month as shown in the attached file: =SUMPRODUCT((B$3:U$3=B$38)*(B5:U5="A"))
2. Cells A40:A43 are populated using**: =IFERROR(INDEX(A$5:A$35,SMALL(IF(Y$5:Y$35>0,ROW(A$5:A$35)-ROW(A$4)),ROWS(A$1:A1))),"")
**Denotes an array formulas which are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
3. Cells B40:E43 are populated using a variation of wk9128's formula: =IF(A40="","",INDEX($1:$36,MOD(MONTH($B$38),3)*12+MATCH($A40,$A$1:$A$36,),DAY($B$38)*4-CHOOSE(COLUMNS($B40:B40),2,1,0,-1)))
I believe that the above formulas will work in Excel 2007 although I don't have a copy of that version with which to test.
Let us know if you have any questions.

[wk9128]

POST#4 formula can meet your requirements
Please watch this animation teaching file . FILE name as S10.gif

After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

Formula can suit for 2019 and below version . Pls Click the left mouse button to select the ranges area B40:E40 ; pls Place the following formula in Formula Bar , then Array formula

[hassan khansa]

thank you a lot Mr JeteMc and wk9128 , this is what i want exactly , it is perfect and i will try to read many times the formula to understand how it work because i have more student names than in the example file and all the day of the months not from 1st to 5th day.

Best Regards

[wk9128]

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

2019 and below version . Pls Click the left mouse button to select the ranges area B40:E40 ; pls Place the following formula in Formula Bar , then Array formula

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[hassan khansa]

Dear Mr JeteMc and wk9128, sorry for bothering you , i still need one thing from you if you please. i want to put the same formula and get the same result but in another sheet (sheet2) ,not in the same sheet of data, how can i do this in sheet2 ?:
A B C D E
1 Date 03/09/2021
2 Name 1st hour 2nd hour 3rd hour 4th hour
3 Elie A P P A
4 George A A P P
5 Alin p p A A

in B1 i will insert the date i want and in A3 the formula to get the student Name and from B3:E3 the 4 hours (A or P).

Best Regards

[JeteMc]

As for my file in post #6, if you cut (Ctrl + x) A38:E45, then select cell A1 on Sheet2 and then paste (Ctrl + v) the formulas will automatically change to reference the ranges on Sheet1.
I.E. the formula for the names will be: =IFERROR(INDEX(Sheet1!A$5:A$35,SMALL(IF(Sheet1!Y$5:Y$35>0,ROW(Sheet1!A$5:A$35)-ROW(Sheet1!A$4)),ROWS(Sheet1!A$1:A1))),"")
The formula for the A's and P's will be: =IF(A3="","",INDEX(Sheet1!$1:$36,MOD(MONTH($B$1),3)*12+MATCH($A3,Sheet1!$A$1:$A$36,),DAY($B$1)*4-CHOOSE(COLUMNS($B3:B3),2,1,0,-1)))
Let us know if you have any questions.

[hassan khansa]

Dear Mr JeteMc, i am sorry i failed edit the formula to get the result i want because i couldn't understand how your formula works =IF(A3="","",INDEX(Sheet1!$1:$36,MOD(MONTH($B$1),3)*12+MATCH($A3,Sheet1!$A$1:$A$36,),DAY($B$1)*4-CHOOSE(COLUMNS($B3:B3),2,1,0,-1)))
i attached a copy of original file to help me with insert the true formulas , please see attached file with two sheets ( Students and Absent ) , the formulas will be in Absent sheet.

Best Regards and thank you in advanced

[hassan khansa]

sorry i couldn't attach the file , i will try tomorrow to attach it

[JeteMc]

If the file is too large try making a .zip file from it and attaching the .zip file.
Let us know if you have any questions.

[hassan khansa]

Dear Mr. JeteMc, i'm very thankfull for your cooperation and your idea to zip my file because i have been since last reply trying to attache the file but i failed till now when i zipped the file..
if you please to see the two sheets ( Students and Absent ) in the attached file. the data are in the Sheet "Students" the students names in column J and the dates are in the rows 5, 30, 55, 80,105, 130, 155,180, 205 and 230.
and the helper column is EK, i tried to insert the formula in the sheet " Absent" but i failed to get the result i want.
please resend me the file with the true formula.

Best Regards

[JeteMc]

The layout of the .zip file is quite different than that of the file in post #1.
As we have not gotten too far into the school year I would suggest putting the records into a row over row arrangement similar to josephteh's suggestion, except that I feel that you could get your desired result by only recording the name date and period when a student is absent and assuming that the student is present otherwise.
That said, and with some modifications, here is a formula that appears to do what you asked in post #13:

Please Login or Register  to view this content.

The modifications include a helper table for months and month numbers (relating to a school year) in columns M:N
Populating cell D2 with the month name using: =TEXT(C2,"mmmm")
Populating cell E2 with the month number using: =VLOOKUP(D2,M3:N12,2,0)
Removing the columns highlighted in red on the 'Students' sheet.
Let us know if you have any questions.

[hassan khansa]

dear Mr Jete , sorry i didn't know that the formula is difference between file in post#1 and file post#13 , i thought the same formula i can use so i attached in post#1 a simple file.
when i enter date more than 3 sep 2021 in the last zip file you attached, I get the correct result in sheet Absent column B (Names) but i didn't get the correct results in columns C:F ( A and P) , how can i edit the formula ? if you please to help me.

Best Regards

[JeteMc]

If you mean that when you enter the date 6 Sept. 2021 you get zeros in column C, that is why I said that the red columns on the Students sheet need to be deleted.
To get the correct results for 6 Sept. 2021:
1. Select cell AJ1 on the Students sheet
2. Press the Ctrl and space bar keys
3. Select Delete from the Home tab
4. If necessary select "move cells to left"
If you still have problems, please upload another file that shows the answers that the formula should return.
Let us know if you have any questions.

[hassan khansa]

Dear Mr JeteMc i attached the file please see it, the wrong not just on 10-9-2021 but also on many dates like 13-9-2021 or 14-9-2021 ...
i'm sorry for bothering you again.

[JeteMc]

I added another helper column (EH) to the Students sheet.
The formula for the helper column is: =IF(EG10>0,MATCH(Absent!C$2,K$5:EE$5,0),"")
As with the first helper column the reference to the date row (5) will need to be changed for each month.
The formula on the Absent sheet is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[hassan khansa]

Dear Mr. JeteMC , i'm very very thank you for your cooperation, this is what i want and all the results are correct. sorry again for bothering you.

Best Regards

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.