jb
Member
Hello Helpers,
I want to count total no. of entries from a tabular data based on multiple criteria. I tried many formula like countif, sumproduct but it is not working.
I have an excel file containing total 5 sheets.
Original_data, Attendance_data, register, temp and final.
Original_data contains names of workers with their other details in rows.
Row 3 contains code1 and row 4 contains code 2 from F column onwards.
F6 to K15 cells contains value Y or NA means code1 and code2 are applicable or not applicable. This sheet is one time entry.
Attendance_data contains code1 and code2 wise date wise attendance data in table from f20 to wg119.
For example, F column has attendance data for code1 as CF and code2 as DM. Only those short IDs are entered here for whom Y is mentioned in DM CF combination in Original_data sheet. Here, values are 1,3 and 10. In original_data sheet DM CF combination Y is mentioned for Short ID 1, 3 and 10.
This sheet will be updated on daily basis.
Register sheet is a presentation of Attendance_data means it will show P status in front of short ID if his/her number is entered in attendance_data sheet for particular code1 and code2 on a particular date and sr. no.. If for particular worker the status is NA in original sheet, it will display NA. I have done this.
Now I have created temp sheet where it copies Y/NA status for each code1 and code2 combination for all the entries of attendance. This is helper sheet. And it is working fine. I have created this sheet to apply some formula. But didn't worked.
In Final sheet, cell F6 i have tried a formula to calculate total no. of entries containing P in register sheet for ID 1 with code CF and DM.
It should show 1.
Cell K7 should display 2 because for ID 2, PM RD code combination, there are 2 entries of P in register sheet.
Helpers please note that, original_data sheet can contain upto 30 combinations of code1 and code2.
Attendance entries can go upto wg column.
Test file is attached herewith.
I want to count total no. of entries from a tabular data based on multiple criteria. I tried many formula like countif, sumproduct but it is not working.
I have an excel file containing total 5 sheets.
Original_data, Attendance_data, register, temp and final.
Original_data contains names of workers with their other details in rows.
Row 3 contains code1 and row 4 contains code 2 from F column onwards.
F6 to K15 cells contains value Y or NA means code1 and code2 are applicable or not applicable. This sheet is one time entry.
Attendance_data contains code1 and code2 wise date wise attendance data in table from f20 to wg119.
For example, F column has attendance data for code1 as CF and code2 as DM. Only those short IDs are entered here for whom Y is mentioned in DM CF combination in Original_data sheet. Here, values are 1,3 and 10. In original_data sheet DM CF combination Y is mentioned for Short ID 1, 3 and 10.
This sheet will be updated on daily basis.
Register sheet is a presentation of Attendance_data means it will show P status in front of short ID if his/her number is entered in attendance_data sheet for particular code1 and code2 on a particular date and sr. no.. If for particular worker the status is NA in original sheet, it will display NA. I have done this.
Now I have created temp sheet where it copies Y/NA status for each code1 and code2 combination for all the entries of attendance. This is helper sheet. And it is working fine. I have created this sheet to apply some formula. But didn't worked.
In Final sheet, cell F6 i have tried a formula to calculate total no. of entries containing P in register sheet for ID 1 with code CF and DM.
It should show 1.
Cell K7 should display 2 because for ID 2, PM RD code combination, there are 2 entries of P in register sheet.
Helpers please note that, original_data sheet can contain upto 30 combinations of code1 and code2.
Attendance entries can go upto wg column.
Test file is attached herewith.
