Sum a range containing 3 text Characters then a number but not case sensitive

Hi All,

I have attached a workbook, however, Sheet 1 contains some data initials followed by number in 1 cell. Sheet 2 has all the magic that separates it all out. Formula credit to zbor.

{=SUM(IFERROR(--SUBSTITUTE(OFFSET(Sheet1!$C$3:$C$100,0,MATCH($C$2,dateh1,0),,1+MATCH($C$3,dateh1,0)-MATCH($C$2,dateh1,0)),$D5,  ""),0))}

The formula works perfect it the Initals are entered in uppercase like AAA4 however I can see it being typed as aaa4.

The question I need your talent for is how to make this formuala case insensitive. I thought MATCH was case insensitive so stuck.

Help.xlsx

Try

=SUMPRODUCT((LEFT(Sheet1!$D$4:$ND$13,3)=$D5)*(Sheet1!$D$2:$ND$2>=$C$2)*(Sheet1!$D$2:$ND$2<=$C$3)*(0&REPLACE(Sheet1!$D$4:$ND$13,1,3,"")))

Thank you to you both Jonmo1 & Ron Coderre..

Both formula work a treat and a standard not array.

Now to choose which one to use?

This forum is brilliant all, thanks to you guys.

Glad to hlep.


They both do pretty much the same thing.
I just didn't put in the named ranges like Ron did.

Mine uses the Replace function to remove the Text string, Ron's uses MID.
Though mine is assuming your text codes will always be 3 digits.
Ron's will accomodate depending on the string you put in column D.

So Mine might be a 'tad' faster (probably not a negiligible difference)
But if your text codes will ever be anything other than 3 characters, then you'll need to use the LEN function like Ron's.

Thank you,

Yes they might by 2 characters, but both fail one comes up with a 0 the other #VALLUE, zbor works but is case sensitive.

Any thoughts

Can you post an example where it's 2 ?

Hi Jonmo

Here is the workbook with an example.

Help.xlsx

Try this (array entered with CTRL + SHIFT + ENTER)

=SUM((LEFT(Sheet1!$D$4:$ND$13,LEN($D5))=$D5)*(Sheet1!$D$2:$ND$2>=$C$2)*(Sheet1!$D$2:$ND$2<=$C$3)*(IFERROR(REPLACE(Sheet1!$D$4:$ND$13,1,LEN($D5),"")+0,0)))

Thank you so much, works great, Thank you again for your time.