countifs with different dimensions and multiple criteria

[mira2020]

I want to count number of items but lookup for value both in column and row.
i try countifs but it does not work. How to make the count if the range are changable
Please help. Thanks

here is my sample : countif.xlsx

 

[Fluff]

You have spaces after some of the names, remove them.
Also you need to anchor one of the criteria cells like
=COUNTIFS(A:A,E2,C:C,F2,B:B,G$1)

 

[Toadstool]

Hi Mira2020,

I'm unsure why your G2 is showing zero as it correctly shows 2 in my example, but the reason the formula won't copy down is you need to make the reference to "High" and "Low" absolute so it always checks row 1

Mira2020.xlsx
	A	B	C	D	E	F	G	H
1	Fruit	Priority	Month		Fruit	Month	High	Low
2	Apple	High	01-Sep-20		Apple	01-Sep-20	2	1
3	Apple	High	01-Sep-20		Orange	01-Sep-20	0	1
4	Orange	Low	01-Sep-20		Banana	01-Sep-20	0	0
5	Banana	Medium	01-Sep-20		Apple	01-Oct-20	1	1
6	Apple	Low	01-Sep-20		Orange	01-Oct-20	1	1
7	Apple	Medium	01-Sep-20		Banana	01-Oct-20	0	0
8	Orange	High	01-Oct-20
9	Apple	High	01-Oct-20
10	Orange	Low	01-Oct-20
11	Banana	Medium	01-Oct-20
12	Apple	Low	01-Oct-20
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=COUNTIFS(A:A,E2,C:C,F2,B:B,G$1)
H2:H7	H2	=COUNTIFS(A:A,E2,C:C,F2,B:B,H$1)

 

[Fluff]

I'm unsure why your G2 is showing zero

There's a trailing space after the word in E2.

 

[Toadstool]

There's a trailing space after the word in E2.

I see them on the OneDrive (also E5, A12) but strangely they didn't appear in my Excel when I copy & pasted from the OneDrive web screen.

 

[mira2020]

thanks . it worked

 

[Fluff]

Glad we could help & thanks for the feedback.