Countif Dynamic Range

Hello,

I have a large file with multiple embedded formulas that auto-update based on what Month / Year I input into A2 & B2. I then concatenate this Month and Year along with a static reference number to identify data on other sheets I need to reference - it ends up looking like 320161, 420161, etc.

In the example attached I need to write a formula in cell E4 that will count all of the values under the corresponding reference column on Sheet2. So for the given example I'm trying to get it to return 7.

I've tried using multiple different combinations of the Countif, Match, Address, Indirect, and offset functions but with no success. Can someone please help get me on the right track for how I would do this.

Please also note, this is just an example of what I'm trying to do on a much larger, confidential file.

Thank you!

Try

=SUMPRODUCT((Sheet2!$B$3:$E$10="X")*(Sheet2!$B$2:$E$2=Sheet1!$E$2))

Thank you so much! Both the COUNTIF and COUNTA functions worked. I was, unfortunately, unable to get the SUMPRODUCT solution to work.

Many thanks!