ONLY IF

[Guest]

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

 

[Bob Phillips]

=1-SUMPRODUCT((IMPORTS!R2:R50000="Alpha")*(IMPORTS!V2:V50000="Y"))/
SUMPRODUCT((IMPORTS!R2:R50000="Alpha")+((IMPORTS!T2: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)

 

[Guest]

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]

Probably wrap-around. Try

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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%