Dynamically Use Countifs or Sumproduct

[jeffbaranda]

Both the Target and Lookup Files have the same fields "Name" (C column) and "Dates" (Date1, Date2, etc) (D, E, ... Columns)
The goal is to count the number of instances of "P"

Either of the formulae work:

=COUNTIFS('[Lookup File.xslx]June'!$B$3:$B$112,C33,'[Lookup File.xlsx]June'!$D$3:$D$112,"P")
=SUMPRODUCT(('[Lookup File.xslx]June'!$B$3:$B$112=$C33)*('[Lookup File.xslx]June'!E$3:E$112="P"))

However, it needs to be dynamic enough that the second range would depend on the value of the Date (e.g June 1, June 2, etc). This is important because the Lookup File doesn't have the same number of dates as that of the Source File.