Get Total of Items From Single Letters W/in a Cell

[FingDragons]

Hey all,

First thanks to anyone that helps.

My work gives me a list of multiple config codes and wants me to break it down using a rubrik to get totals of all the items going on each truck. They've been doing this by hand forever and it takes hours every week.

I know you can use VLOOKUP to get totals but i've only seen it as looking up whole cells not just single letters or words within a cell.

I'm trying to see if there is a formula so i can speed this job up. See bleow as an example.

Example.PNG

1: Input a config code in each cell in column A
2: Input Truck number in column B
2: Cell H:3 will total all the first digits in Column:A only if they are Capital 'A' and truck 1
3: Cell I:3 will total all the first digits in Column:A only if they are Capital 'A' and truck 2
4: Cell H:4 will total all the first digits in Column:A only if they are Lowercase 'b' and truck 1
5: Cell I:4 will total all the first digits in Column:A only if they are Lowercase 'b' and truck 2
6: Cell H:9 will total all the 3rd/4th digits in Column:A only if they are Lowercase 'aa' and truck 1
7: Cell I:9 will total all the 3rd/4th digits in Column:A only if they are Lowercase 'aa' and truck 2
8: ETC...

Thanks,

Fingdragons

[EchoPassenger]

Are the Codes always the same format and length?

Edit: Give this a look. Perhaps my understanding is wrong, but this looks to follow your rules. Not all the columns I've added are strictly necessary, but I've done it that way to make it easier to understand.

[FingDragons]

Are the Codes always the same format and length?

Edit: Give this a look. Perhaps my understanding is wrong, but this looks to follow your rules. Not all the columns I've added are strictly necessary, but I've done it that way to make it easier to understand.

Echopassenger,

First thanks, this look like it should work.

Yes, the code lenghts are pretty much always the same, rarely they will have an extra digit but it's not normal. Mine are however 6 digits not 5. Will that make any difference?

Thanks

Fingdragons

[FingDragons]

Echopassenger,

First thanks, this look like it should work.

Yes, the code lenghts are pretty much always the same, rarely they will have an extra digit but it's not normal. Mine are however 6 digits not 5. Will that make any difference?

Thanks

Fingdragons

Ok, i've been trying to work this and something strange is happening.

1. The fomula doesn't work on mine. I've tried to make it all the same but it's only showing 0 and randomly 1 for totals
2. In your product, when i double double click into the cell with a formula and then click off it, but don't change anything, the number changes to 0 or 1. See below

Example 3.png
Example 1.png
Example 2.png

Thanks again

Fingdragons