countif in filter mod

[mohammad1990]

hi

I have a table and want to use count-if in filter mod i searched and find this formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-ROW(B3),0,1)),--(B3:B10=D2))

it works well but only in one column
when using a range( B3:C10) instead of B3:B10 it doesn't work And gives #Value! error
pleas look the attached file

 

[Phuoc]

Is your expected result 5 or 3?

 

[kevin9999]

I think @Eric W may have solved this type of problem in This thread?

Book1
	A	B	C	D
1	data			Criteria
2	Date	Product 1	Product 2	B
6	March 2, 2023	A	B
7	March 2, 2023	B	B
8	March 2, 2023	A	A
9	March 2, 2023	A	A
10	March 2, 2023	B	B
11				5
Sheet1

Cell Formulas
Range	Formula
D11	D11	=SUM((B3:C10=D2)*(SUBTOTAL(103,OFFSET(B3,ROW(B3:C10)-ROW(B3),COLUMN(B3:C10)-COLUMN(B3)))))

 

[mohammad1990]

Is your expected result 5 or 3?

5

 

[mohammad1990]

I think @Eric W may have solved this type of problem in This thread?

Book1
	A	B	C	D
1	data			Criteria
2	Date	Product 1	Product 2	B
6	March 2, 2023	A	B
7	March 2, 2023	B	B
8	March 2, 2023	A	A
9	March 2, 2023	A	A
10	March 2, 2023	B	B
11				5
Sheet1

Cell Formulas
Range	Formula
D11	D11	=SUM((B3:C10=D2)*(SUBTOTAL(103,OFFSET(B3,ROW(B3:C10)-ROW(B3),COLUMN(B3:C10)-COLUMN(B3)))))

Thanks a lot
But do you know why I get #Value! Error when use your solution?

 

[kevin9999]

Try entering the formula with Ctrl+Shift+Enter

 

[mohammad1990]

Try entering the formula with Ctrl+Shift+Enter

Problem Solved
I just can say
Thank You

 

[kevin9999]

Glad you got it working, and thanks for the feedback