[Anto111]

Hi guys,

I have the following formula:

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

In the second part of the formula I have specified to ignore cells
containing zero values, however I also need to specify to the formula not to
count cells containing text.

Any help on what I need to add to the formula would be highly appreciated.

Kind regards,

Ant

[Bob Phillips]

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

but you can use AVERAGEIFS

=IFERROR(AVERAGEIFS(K6:K33,B6:B33,"DEF",K6:K33,"0 "),"-")

as AVERAGE ignores blanks

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Anto111" wrote in message
...
Hi guys,

I have the following formula:

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

In the second part of the formula I have specified to ignore cells
containing zero values, however I also need to specify to the formula not
to
count cells containing text.

Any help on what I need to add to the formula would be highly appreciated.

Kind regards,

Ant

[joel]

I would use ISNUMBER

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


"Anto111" wrote:

Hi guys,

I have the following formula:

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

In the second part of the formula I have specified to ignore cells
containing zero values, however I also need to specify to the formula not to
count cells containing text.

Any help on what I need to add to the formula would be highly appreciated.

Kind regards,

Ant