Sumproduct? Countifs? multiple criteria and dates (January and blank cells messing everything up)

[85Sarah2005]

I have dates in column A, (but some blanks), then column B has X, Y, or Z.

The zero's were messing up my Countif and including them in the January total, but in the end I managed to count the number of occurrences for each moth by using : =SUMPRODUCT(--(MONTH(A2:A500)=1),--(MONTH(A2:A500<>0))

But I now need to show the same data but just for criteria x, y or z. If I use =SUMPRODUCT(--(MONTH(A2:A500)=1)*(B2:B500=X) then it's just giving me all wrong numbers, but I I try and incorporate --(MONTH(A2:A500<>0 then I'm getting a spill error. What am I doing wrong?

13​

13​

 

[awoohaw]

I'm not sure if this is what you want, but take a look:

mr excel questions 15.xlsm
	A	B	C	D	E	F	G
1	Date
2	2022-11-01	Z
3	2022-11-01	X		Dec	Jan	Feb
4	2022-11-01	Y	X	2	3	3
5	2022-11-01	Y	Y	1	8	1
6	2022-11-01	Z	Z	5	1	1
7	2022-11-01	Y
8	2022-11-01	X
9	2022-11-01	Y
10	2022-11-01	Z
11	2022-11-01	Z
12	2022-11-01	X
13	2022-11-01	Y
14	2022-12-01	X
15	2022-12-01	Y
16	2022-12-01	Z
17	2022-12-01	Z
18	2022-12-01	Z
19	2022-12-01	Z
20	2022-12-01	X
21	2022-12-01	Z
22	2023-01-01	Y
23	2023-01-01	Y
24	2023-01-01	X
25	2023-01-01	Y
26	2023-01-01	Y
27	2023-01-01	Y
28	2023-01-01	Y
29		Z
30		X
31		Y
32		X
33		Y
34	2023-02-01	X
35	2023-02-01	X
36	2023-02-01	Z
37	2023-02-01	X
38	2023-02-01	Y
39	2023-03-01	X
40	2023-03-01	Y
41	2023-03-01	Z
42	2023-03-01	Z
43	2023-03-01	Y
44	2023-03-01	X
45	2023-03-01	Z
46	2023-03-01	Z
47	2023-03-01	X
48	2023-03-01	Y
49	2023-03-01	X
50	2023-03-01	X
51	2023-03-01	X
52
Sheet18

Cell Formulas
Range	Formula
D4:F6	D4	=SUM((--(TEXT($A$2:$A$51,"mmm")=D$3))*(--($B$2:$B$51=$C4)))

 

[AhoyNC]

Welcome to the forum.
Another option. Try changing your formula to:
SUMPRODUCT((MONTH(A2:A500)=1)*(B2:B500="X")*(A2:A500<>""))

 

[Fluff]

Another option

=COUNT(FILTER(ROW(A2:A500),(MONTH(A2:A500)=1)*(B2:B500="x")*(A2:A500<>"")))