Isolating letters for a formula
 

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:K330)),"-")

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

Isolating letters for a formula
 

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

"Anto111" wrote:

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:K330)),"-")

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

Isolating letters for a formula
 

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

"Anto111" wrote:

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:K330)),"-")

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

Isolating letters for a formula
 

Try this:

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

Or, maybe this:

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

--
Biff
Microsoft Excel MVP


"Anto111" wrote in message
...
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:K330)),"-")

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

Isolating letters for a formula
 

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:K330)),"-"),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:K330)),"-") formula is in A1, use =RIGHT(A1,3) where you want it
to go.

"Anto111" wrote:

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

"Anto111" wrote:

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:K330)),"-")

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

Isolating letters for a formula
 

T. Valko, your an absolute magician,

thank you.

"T. Valko" wrote:

Try this:

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

Or, maybe this:

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

--
Biff
Microsoft Excel MVP


"Anto111" wrote in message
...
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:K330)),"-")

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

Isolating letters for a formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Anto111" wrote in message
...
T. Valko, your an absolute magician,

thank you.

"T. Valko" wrote:

Try this:

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

Or, maybe this:

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

--
Biff
Microsoft Excel MVP


"Anto111" wrote in message
...
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:K330)),"-")

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

Isolating letters for a formula
 

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

"sb1920alk" wrote:

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:K330)),"-"),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:K330)),"-") formula is in A1, use =RIGHT(A1,3) where you want it
to go.

"Anto111" wrote:

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

"Anto111" wrote:

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:K330)),"-")

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