Combine Index,Match with COUNTIFS() or with SUMIFS()

[Abdulrahman7]

Good Morning Everyone,






I have these two tables, one is having data list and the report table which should read the data from the first table


I have huge number of rows which contain number of course title, no of session, attendance and Hours , as the above pic shown in Report table the pink colors have Course title and Number of Session, I need to make it to read data i think we can make it with combining Index / Match with COUNTIFS()

Here what I did to get the report is COUNTIFS() Several time to get Attendees and Absentees several time that is wasting of time , also to get No OF Hours have to copy the code for each session then change number of session.

If there is another solution i'll be glad



Thank you in Advance

 

[PCosta87]

Good Morning Everyone,


View attachment 40362



I have these two tables, one is having data list and the report table which should read the data from the first table


I have huge number of rows which contain number of course title, no of session, attendance and Hours , as the above pic shown in Report table the pink colors have Course title and Number of Session, I need to make it to read data i think we can make it with combining Index / Match with COUNTIFS()

Here what I did to get the report is COUNTIFS() Several time to get Attendees and Absentees several time that is wasting of time , also to get No OF Hours have to copy the code for each session then change number of session.

If there is another solution i'll be glad



Thank you in Advance

Hi,

If I understood correctly, I believe you already have the best solution...
The only thing I would change is, instead of using "=Oracle" or "=1" I would use the actual pink cell where the criteria is... for K17 it would be something like:

"=COUNTIFS($B$2:$B$22,$I17,$C$2:$C$22,$J17,$E$2:$E$22,"YES")"

and then copy down and across.

Hope this helps

 

[Abdulrahman7]

Mr. PCosta87 Thank you very much , that is exactly what I need , could you please explain why do we use dollar sign($)

 

[PCosta87]

Mr. PCosta87 Thank you very much , that is exactly what I need , could you please explain why do we use dollar sign($)

Hi,

You are welcome

The $ is used to set absolute references, which means that when copied, the formula will retain the original range.
Without it, copying would offset the range.

For more in depth info, try:
https://support.office.com/en-us/ar...ferences-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

 

[Abdulrahman7]

Thank you PCosta87 that's really helped.