Isolating letters for a formula

[Anto111]

Hi guys,

I am currently applying the following formula to a speadsheet:

=IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F
DEF"),--(K6:K33>0)),"-")

In a seperate formula I have also replaced "F DEF" with "C DEF" to return a
different value. What I would now like to do is create a similar formula that
incorparates both sets of data into one formula by identifying only the last
three letters, i.e "DEF".

Any ideas would be greatly appreciated.

Thanks in advance,

Ant

 

[sb1920alk]

Use the RIGHT forumula. =RIGHT(A1,3) if you're referring to A1.

 

[Anto111]

Hi,

thanks for that, just been reading up on that by coincidence but not sure
how to incorparate it into the formula?

Cheers for your time,

Ant

 

[T. Valko]

Try this:

=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K33>0)),"-")

Or, maybe this:

=IFERROR(SUMIF(B6:B33,"*DEF",K6:K33)/COUNTIFS(B6:B33,"*DEF",K6:K33,">0"),"-")

 

[sb1920alk]

You could either nest your original formula in RIGHT like this:
=RIGHT(IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F
DEF"),--(K6:K33>0)),"-"),3)

or use RIGHT to refer to what you have now from another cell. So if your
=IFERROR(SUMPRODUCT(--(B6:B33="F DEF"),K6:K33)/SUMPRODUCT(--(B6:B33="F
DEF"),--(K6:K33>0)),"-") formula is in A1, use =RIGHT(A1,3) where you want it
to go.

 

[Anto111]

T. Valko, your an absolute magician,

thank you.

Try this:

=IFERROR(SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),K6:K33)/SUMPRODUCT(--(RIGHT(B6:B33,3)="DEF"),--(K6:K33>0)),"-")

Or, maybe this:

=IFERROR(SUMIF(B6:B33,"*DEF",K6:K33)/COUNTIFS(B6:B33,"*DEF",K6:K33,">0"),"-")

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Anto111]

sb, they both worked. I really appreciate your time, thanks. ;-)