SUMIFS with MID and RIGHT as lookups

[LEWI_74]

Hi,
I hope someone can help.

I have some dowmloaded data in the following columns:
Column B: 13 number and letter code e.g. F20110000U340 (the code in red can change as can the last 4 digits coloured in blue - thus are key variables)
Column E: The base amount for that particular transaction.

I have used the Function button to check my formula and it appears correct in each of the boxes but there is no value after the = sign in the grey function box.

I want to use SUMIFS on a summary table based on different numbers in RED and BLUE and I have so far:

=SUMIFS(E:E,RIGHT(B:B,4),"U340",MID(B:B,5,5),"10000")

I hope it is something relatively straight forward.

Many thanks

David

 

[machopicho]

Can you make an example of your expected output?

 

[T. Valko]

Hi,
I hope someone can help.

I have some dowmloaded data in the following columns:
Column B: 13 number and letter code e.g. F20110000U340 (the code in red can change as can the last 4 digits coloured in blue - thus are key variables)
Column E: The base amount for that particular transaction.

I have used the Function button to check my formula and it appears correct in each of the boxes but there is no value after the = sign in the grey function box.

I want to use SUMIFS on a summary table based on different numbers in RED and BLUE and I have so far:

=SUMIFS(E:E,RIGHT(B:B,4),"U340",MID(B:B,5,5),"10000")

I hope it is something relatively straight forward.

Many thanks

David

You can't "manipulate" the criteria ranges when using the SUMIF(S) function(s).

Maybe this...

=SUMIF(B:B,"*10000U340",E:E)