Multiple match with Counta/Index/Match/Match

Hi, everybody,

I have an Excel file listing the trip location per driver and per day.

The result I am looking for is a synthesis table of the number of days worked per weekday and per driver.
I try to find a formula to count the number of non-empty cells according to the name of the driver and the day of the week.

Function.png

So far, by combining the functions "Counta/Index/Match/Match" I only manage to find the number of days on the first week, the function stops after finding the result of the first Match.

=COUNTA(INDEX($A$1:$U$21,MATCH($A25,$A$1:$A$21,0),MATCH(B$24,$A$1:$U$1,0)))

The result I'm looking for would be for example: for Driver 1, Sunday = 2.

Does someone have any ideas?
I hope my question is clear enough, I attach the file for more clarity.

Thank you in advance for your help !

Anais

Try COUNTIFS, choose your fields and criteria.

Pete

Hello Pete,

Thank you for your answer.
I tried with the Countifs function, unfortunately, because some of the arguments are present in line and not in column (days of the week) the function doesn't work.

Maybe you know another function that might be appropriate in this situation?

B25=if(and($a25<>"",b$24<>""),sumproduct(($a$3:$a$21=$a25)*($b$1:$u$1=b$24)*($b$3:$u$21<>"")),"")


copy across and down

It works perfectly, many thanks !!

Hi all,

I did a multiple criteria index match and I want to count the results. I keep getting "1" even though I know there are multiple results.

Not sure what is wrong with this formula:

=COUNTA(INDEX(Sheet1!$A$1:$AB$2192,MATCH(1,(Sheet7!A:A=Sheet7!A3)*(Sheet7!B:B=Sheet7!B3)*(Sheet7!C:C=Sheet7!C3)*(Sheet7!E2),0),Sheet1!A:A))

Attach a file with results

B25=SUMPRODUCT(($A$3:$A$21=$A25)*($B$1:$U$1=B$24)*($B$3:$U$21<>""))

Copy across and down