Hi,

I am trying to run a COUNTIFS formula with a LEFT condition on an array.
I know that LEFT cannot be combined with COUNTIF as well as COUNTIFS.

The workaround for COUNTIF with LEFT is to use SUMPRODUCT, but since I am using COUNTIFS and there is no such thing as SUMPRODUCTS, I am a bit stumped as to how I should go about it.

Formula I am trying to use:
=COUNTIFS(WEH.xlsx!$BH:$BH,$H$2,LEFT(WEH.xlsx!$BB:$BB,4),LEFT($A$2,4))

I know I can replace the second portion with
=SUMPRODUCT(--(LEFT(WEH.xlsx!$BB:$BB,4)=LEFT($A$2,4))), but it wouldn't be combinable with COUNTIFS as far as I can work out.

I would just add a helper column to the WEH workbook except that the number of LEFT characters differs based on where in A:A they are (some names are shorter than 4, so I need to change it to exact match for those names, I can't do this with WEH because it would require manual changes for every entry with a name less than 4 characters)

Please help