Excel 2007 : COUNTIFS with LEFT - workaround needed

**dip11** · 05-09-2012, 08:49 AM

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

**daddylonglegs** · 05-09-2012, 09:06 AM

You can use multiple conditions within SUMPRODUCT so try

=SUMPRODUCT(--(WEH.xlsx!$BH:$BH=$H$2),--(LEFT(WEH.xlsx!$BB:$BB,4)=LEFT($A$2,4)))

[although it's not advisable to use whole columns with SUMPRODUCT, it'll make it rather slow]

or, better, you can use a wildcard with COUNTIFS assuming the data isn't numeric, i.e.

=COUNTIFS(WEH.xlsx!$BH:$BH,$H$2,WEH.xlsx!$BB:$BB,LEFT($A$2,4)&"*")

**Marcol** · 05-09-2012, 09:22 AM

Also maybe watch how you reference the other workbook.
=SUMPRODUCT(--(LEFT([WEH.xlsx]Sheet1!$BB$2:$BB$1000,4)=LEFT($A$2,4)),--([WEH.xlsx]Sheet1!$BH$2:$BH$1000=$H$2))