Replacing Countifs with Sumproduct?

[Mr_Phil]

Can someone help me with replacing a Countifs with Sumproduct.
I did an XLOOKUP successfully but it brings back the serial number and won't catch anyone with more than one. So that isn't viable.
The issue is that the Countifs requires the external data source to be open. That was fine for a while. But, now I need to be able to quickly reference the data without opening multiple workbooks. And based on some googling the sumproducts is the way to go. But I am having trouble visualizing how t construct it.

=COUNTIFS('[Pull Sheet.xlsb]FIELD OH'!$I:$I,$A2,'[Pull Sheet.xlsb]FIELD OH'!$A:$A,$B$1)

is the formula I am trying to replace. The absolute references were removed once it was copied to all the cells in a range.

Thank you for looking. I hope everyone is well.



And if you ever need an xlookup that uses concantentation ... here it is. I was pretty proud to have figured it out without having to google or anything. To bad the result isn't a count of lines matching the criteria.

=XLOOKUP($A2&$B$1,'[Pull Sheet.xlsb]FIELD OH'!$I:$I&'[Pull Sheet.xlsb]FIELD OH'!$A:$A,'[Pull Sheet.xlsb]FIELD OH'!$B:$B)

 

[Alex Blakenburg]

See if this works for you.

=LET(fltr,  FILTER('[Pull Sheet.xlsb]FIELD OH'!$I:$I,  ('[Pull Sheet.xlsb]FIELD OH'!$I:$I=$A2) * ('[Pull Sheet.xlsb]FIELD OH'!$A:$A=$B$1) ),
          SUM(--NOT( ISERROR(fltr) ) ) )

 

[Fluff]

Another option

=COUNT(FILTER(ROW('[Pull Sheet.xlsb]FIELD OH'!$I$2:$I$1000),('[Pull Sheet.xlsb]FIELD OH'!$I$2:$I$1000=$A2)*('[Pull Sheet.xlsb]FIELD OH'!$A$2:$A$1000=$B$1)))

 

[mikerickson]

=SUMPRODUCT(--('[Pull Sheet.xlsb]FIELD OH'!$I:$I=$A2)*('[Pull Sheet.xlsb]FIELD OH'!$A:$A=$B$1))

 

[Mr_Phil]

See if this works for you.

=LET(fltr,  FILTER('[Pull Sheet.xlsb]FIELD OH'!$I:$I,  ('[Pull Sheet.xlsb]FIELD OH'!$I:$I=$A2) * ('[Pull Sheet.xlsb]FIELD OH'!$A:$A=$B$1) ),
          SUM(--NOT( ISERROR(fltr) ) ) )

Another option

=COUNT(FILTER(ROW('[Pull Sheet.xlsb]FIELD OH'!$I$2:$I$1000),('[Pull Sheet.xlsb]FIELD OH'!$I$2:$I$1000=$A2)*('[Pull Sheet.xlsb]FIELD OH'!$A$2:$A$1000=$B$1)))

=SUMPRODUCT(--('[Pull Sheet.xlsb]FIELD OH'!$I:$I=$A2)*('[Pull Sheet.xlsb]FIELD OH'!$A:$A=$B$1))

First I need to thank each of you for taking the time to help me. I tried all three solutions and all three worked. I chose the sumproduct solution because I have never used any of the gifted options and I think I will be able to study and understand the syntax and what is going on quicker than the others. But, don't be surprised to get a notification of a message here properly thanking each of you as I dig in and understand the details of all three options. Y'all are the best. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.