The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000 contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!



=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORT S!T2:T50000,"*"))

=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/
SUMPRODUCT((IMPORTS!R2:R50000="Alpha")+((IMPORTS!T 2:T50000<"")*(NOT(ISNUMBE
R(IMPORTS!T2:T50000)))))

--
HTH

Bob Phillips

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

"B.H.JIG" wrote in message
...
The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000
contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that
will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!




=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(
IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORTS!T2:T500 00,"*"))

thanks for taking the time to answer my question, but when i tried typing
your answer in the computer highlights ISNUMBER and says NOT(logical)
HELP!!!!


"Bob Phillips" wrote:

=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/
SUMPRODUCT((IMPORTS!R2:R50000="Alpha")+((IMPORTS!T 2:T50000<"")*(NOT(ISNUMBE
R(IMPORTS!T2:T50000)))))

--
HTH

Bob Phillips

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

"B.H.JIG" wrote in message
...
The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000
contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that
will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!




=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(
IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORTS!T2:T500 00,"*"))

Probably wrap-around. Try

=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/
SUMPRODUCT((IMPORTS!R2:R50000="Alpha")+((IMPORTS!T 2:T50000<"")*
(NOT(ISNUMBER(IMPORTS!T2:T50000)))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"B.H.JIG" wrote in message
...
thanks for taking the time to answer my question, but when i tried typing
your answer in the computer highlights ISNUMBER and says NOT(logical)
HELP!!!!


"Bob Phillips" wrote:

=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/
SUMPRODUCT((IMPORTS!R2:R50000="Alpha")+((IMPORTS!T 2:T50000<"")*(NOT(ISNUMBE
R(IMPORTS!T2:T50000)))))

--
HTH

Bob Phillips

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

"B.H.JIG" wrote in message
...
The Question mark in the below formula represents the area that i am
stuck
with. what i would like is to work out the % but only if T2-T50000
contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that
will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max
&
Pinmaster for helping me get this far!!!!




=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(
IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORTS!T2:T500 00,"*"))

im not sure what im doing wrong but i still cant get any results from that
I think I need to start gradually, what would help me to understand is how
might one type a formula for (column 1)*(column 2 only if column 3 has text
in it)?
Thank you for your understanding & help
"B.H.JIG" wrote:

The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000 contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!



=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORT S!T2:T50000,"*"))

This would go in D1 so that IF column C has text, then column A*Column B else
it stays blank ("")


=IF(ISTEXT(C1),A1*B1,"")


"B.H.JIG" wrote:


im not sure what im doing wrong but i still cant get any results from that
I think I need to start gradually, what would help me to understand is how
might one type a formula for (column 1)*(column 2 only if column 3 has text
in it)?
Thank you for your understanding & help
"B.H.JIG" wrote:

The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000 contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!



=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORT S!T2:T50000,"*"))

the way i read it is that you need to get the % of counted data which has
R2:R50000="Alpha" AND V2:V50000="Y" AND T2:T50000<""
over the counted Data which has
R2:R50000="Alpha" AND V2:V50000="Y"

lets try on cell C1 : format cell as percentage%
=IF(ISERROR(SUMPRODUCT((R2:R10="Alpha")*(T2:T10<" "))/SUMPRODUCT((R2:R10="Alpha")*(V2:V10="Y"))),"NO
MATCH",(SUMPRODUCT((R2:R10="Alpha")*(T2:T10<""))/SUMPRODUCT((R2:R10="Alpha")*(V2:V10="Y"))))

this will give a result of "NO MATCH" or percentage value from 0 to100%

--
*****
birds of the same feather flock together..



"B.H.JIG" wrote:

The Question mark in the below formula represents the area that i am stuck
with. what i would like is to work out the % but only if T2-T50000 contains
some text, the computer suggests that i put brakets round the
COUNTIF(IMPORTS!T2:T50000,"*")) and a * before these brackets but that will
not give a true % as the number that i am / by will be too high.
Thank you for any help or advice offered and I would like to Thank Max &
Pinmaster for helping me get this far!!!!



=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2 :V50000="Y"))/(COUNTIF(IMPORTS!R2:R50000,"Alpha")?COUNTIF(IMPORT S!T2:T50000,"*"))