Sumproduct formula not giving me the results i expect

[KarlRadaza]

Hello Excel-lent people,

Very basic issue but i cant seem to figure out why i cant get the results i desire. I just basically need the formula to count the number of data that has the matching month and year. For example on the screenshot ,lets focus on cell Q6, the formula i came up with is:

=SUMPRODUCT(($B$2:$B$24=Q$3)*($C$2:$C$24=$E6))

And i prefer this over countif but trust me it leads to a faulty result of "1" when in fact there is no "Dec 2020" on the array..

And as you can see ,it yields 1 for all which is incorrect, and frustratingly i cant figure it out despite changing the table array referencing. Please help me guys, looking forward to any help and thank you for yalls time! Stay safe all!

 

[Michael M]

Might be more beneficial if you posted your data set using XL2BB ( see my tag) so we can use real data.

 

[AhoyNC]

Your formula works for me.
Could it be that on your Year columns that one is numeric and the other is text?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Month	Year
2	Jan	2020
3	Jan	2018			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
4	Nov	2019		2018	1	0	0	0	0	0	0	0	0	0	0	0
5	Nov	2020		2019	0	0	0	0	0	0	0	0	0	0	2	1
6	Nov	2019		2020	1	0	0	0	0	0	0	0	0	0	1	0
7	Dec	2019
Sheet1

Cell Formulas
Range	Formula
F4:Q6	F4	=SUMPRODUCT(($B$2:$B$24=F$3)*($C$2:$C$24=$E4))

 

[Michael M]

Could it also be that ColsB & C are either the results of formulas OR formatted to appear as Month & Year, when in fact they are actual dates ??

 

[KarlRadaza]

Just remembered it might be in fact the formatting,will check later and let you guys know, thanks for the help guys!!!

 

[KarlRadaza]

Just checked and it is in fact the formatting of the cells with the formulas that caused the issue! Thank you for the help guys ,will never neglect something as basic as formatting moving forward!!

 

[AhoyNC]

You're welcome. Glad we could help. Thanks for the feedback.