Text strings with sumif

Hi All,

I have the below formula to help me extract information.

I have a list of numbered customers that have letter after, the customers are in numerical order.

The formula picks up training hours based on the first 5 numbers.

I then have a broken gap and another list below which isn't numerical.

I want it to say that if Left(E100,5) appears in any cell in a range
i.e. E1:E1000 then return 0 else the sumif formula

IF(LEFT(E49,5)=LEFT(E48,5),0,SUMIF('RBS Download'!$AH$4:$AH$8000,LEFT(SWL!E49,5)&"T",'RBS Download'!$P$4:$P$8000))


Any help would be great.

Thanks
Martin

Originally Posted by martinjones23

I want it to say that if Left(E100,5) appears in any cell in a range
i.e. E1:E1000 then return 0 else the sumif formula

Hello Martin, perhaps you can use COUNTIF for the first part, e.g.

=IF(COUNTIF(E1:E1000,LEFT(E100,5))>0,0,SUMIF......

Hi Daddy Long Legs,

The only problem is that;

"=IF(COUNTIF(E1:E1000,LEFT(E100,5))>0,0,SUMIF...... "

I need the E1:E1000 to only take the first 5 number as the list is the same format i.e. E100 could have 12345AB where in E1:E1000 there could be another 12345CD

So the first part needs to only consider 5 numbers.

Everything I try is struggling with the two left requests any ideas.

P.S Thanks for the replys