• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Any Cell contain text in a row then the return value is... ""?

Something like this should suffice:
=IF(COUNTA(C2:G2)-COUNT(C2:G2)>0,"",E2)

COUNTA returns number of cells with something in them, COUNT returns number of cells with numbers. Thus, the difference between two is the number of cells with text.
 
Luke M said:
Something like this should suffice:
=IF(COUNTA(C2:G2)-COUNT(C2:G2)>0,"",E2)

COUNTA returns number of cells with something in them, COUNT returns number of cells with numbers. Thus, the difference between two is the number of cells with text.
Click to expand...

Sir,

what if we are looking for returning the text "Absent" to be return as output, if any of the cell contains "Absent" in it? Is this possible?
 
Certainly. That's actually easier:
=IF(COUNTIF(C2:G2,"Absent")>0,"Absent",E2)
 
Sir,
The minimum pass mark is 50 then the student is not eligible for Rankers. In that case how the rankers should be display?
 
Luke M said:
Certainly. That's actually easier:
=IF(COUNTIF(C2:G2,"Absent")>0,"Absent",E2)
Click to expand...

Actually, I was wondering if it is possible to sum a range as we usually do, but if any of the cell contains a certain text, the formula should return that text, like in the above example it has returned "Absent" but when there are numbers in whole range it should sum.
 
Sir,
If all subject have different (individual) pass mark, then how it posible for display the rankers? Eg: English - Pass Mark = 35, Maths - Pass mark= 50
 
shaikhrulez said:
Actually, I was wondering if it is possible to sum a range as we usually do, but if any of the cell contains a certain text, the formula should return that text, like in the above example it has returned "Absent" but when there are numbers in whole range it should sum.
Click to expand...

Hi,

Try below formula:

=IF(COUNT(A1:E1)=COUNTA(A1:E1),SUM(A1:E1),"TEXT")

Suppose A1:E1 has numbers or text.

Regards,
 
shaikhrulez said:
Thanks @Somendra Misra, you simply nailed it. I was trying so hard but failed in doing this. Can you please explain how come COUNT & COUNTA function is giving the desired result?
Click to expand...
COUNT function counts numbers in the range and COUNTA counts anything. SO if count of numbers is equal to count of anything it will give the sum and if count of numbers is not equal to count of everything it will result in TEXT.

Regards,
 
You must log in or register to reply here.
Back
Top