Count basing on multiple matching criteria

[Venkatesh1414]

Hi,

I had a excel workbook .with 2 sheets.
Sheet1 consists of data as below:
From A4 to A6 vehicle info(Lorry, Minivan & Truck), B1 to B6 Fruit(Apple, Mango & Lemon) Info and C1 to C6(1/11/2023,1/11/2023 & 1/10/2023) dates
Sheet 2 is having the info from Sheet 1 like as below:
From A4 to A6 Fruit info, B1is having =TODAY() and C3 to E3 vehicle names.

Now i need the count of Particular Fruit in sheet2 in respective cells from C4 to E6 , basing on Matching criteria of Sheet1 dates with Sheet2 B1 cell and Sheet1 Vehicle info with Sheet 2 Vehicle Info . I am using the below formula but it's giving the correct data.
=COUNTIF(Sheet1!B:B,INDEX(Sheet1!B:B,MATCH(Sheet2!B1&Sheet2!C3&Sheet2!A4,(Sheet1!C:C&Sheet1!A:A&Sheet1!B:B),0)))


Kindy someone help me on this.

Best Regards,

 

[Fluff]

How about

=COUNTIFS(Sheet1!$B:$B,$A4,Sheet1!$C:$C,$B$1,Sheet1!$A:$A,C$3)

 

[Venkatesh1414]

How about

=COUNTIFS(Sheet1!$B:$B,$A4,Sheet1!$C:$C,$B$1,Sheet1!$A:$A,C$3)

Thank you for quick response, by using this formula is getting the count as 0 same as to my formula.

 

[Fluff]

That suggests that you don't have anything that has a date of today that also matches the other criteria.