Forum Discussion
Solved
Use COUNTIFS and INDEX/MATCH to pull data from another table
Hi.
I have two tables, one with information (Attendance Sheet) and one where I need to pull information into (Info sheet). I have attached screenshots of both tables.
I would like to count how many athletes are in each Council LGA for each Term (does not need to be specified by each week), based off the value of LGA in the Info Sheet. I would then like to count how many athletes from each council area attended in each week and each term. I have tried many countifs and index/match functions to no avail. Any help would be appreciated. Thank you.
=SUMPRODUCT((Attendance_table[[week1]:[week6]]="attended")*(Attendance_table[council]=[@concil])*(Attendance_table[term]=[@term])*(Attendance_table[[#Headers],[week1]:[week6]]=[@week]))
The attached sample file uses SUMPRODUCT and should return the intended result if i correctly understand what you are looking for.
2 Replies
=SUMPRODUCT((Attendance_table[[week1]:[week6]]="attended")*(Attendance_table[council]=[@concil])*(Attendance_table[term]=[@term])*(Attendance_table[[#Headers],[week1]:[week6]]=[@week]))
The attached sample file uses SUMPRODUCT and should return the intended result if i correctly understand what you are looking for.
- Legend, thank you
