COUNT formula

**LoooM** · 11-27-2011, 04:31 PM

Hey Guys..
I`ve been following you for quite a while and this forum helped me a lot with my job.. so I start with a hige THANKS!!
Now.. I`ll need to count the cells in a raw that contain text.. BUT.. then I`ll need to erase the count if in a cell of that raw I insert a specific text that could be "X"..
EXAMPLE:
A B C D E F
1 23 34 45 X 33 45 count should be 2 (only E1 & F1 in the count)
2 13 13 34 45 45 55 no "X" so count should be 6
3 13 34 34 56 45 X count should be 0 as the "X" is at the end!

Hope that I explained myself well enough..
This will save me on a bigger scale a huge amount of work!!
P.S.: it would be ABSOLUTELY amazing if the count could be in time format and every "1" should be an half an hour.... so if the count is 5 should be 02:30 ...

I know I`m asking for a lot but I know this is the right place!!

THAAAAAANKS!!

**martindwilson** · 11-27-2011, 04:44 PM

=IF(ISNA(MATCH("x",A1:G1,0)),7,7-MATCH("x",A1:G1,0))
or
=IF(ISNA(MATCH("x",A1:G1,0)),7,7-MATCH("x",A1:G1,0))*TIMEVALUE("00:30") formatted as HH:mm

**LoooM** · 11-27-2011, 05:03 PM

That`s great THANKS for the quick reply!!
But the maybe I explained myself not perfectly... I`ll need a count of the cells than contain text even if there's never gonna be a "X"... and I really need more than 1 "X" in a raw...
Also the count starts from left to right... so to A1 up in raw 1....!
Example:
A B C D E F
1 12 X 13 12 X 12 count should be 1 (should count only F1)
2 12 12 13 X 93 93 count should be 2 (E1 and F1)

THAAAANKS!!

**martindwilson** · 11-27-2011, 05:08 PM

maybe
=IF(ISERROR(MATCH(REPT("z",255),A1:G1,1)),7,7-MATCH(REPT("z",255),A1:G1,1))*TIMEVALUE("00:30")

**LoooM** · 11-27-2011, 05:35 PM

I`m sorry to bother you again but I don`t understand the formulas so I don`t know how to modify It myself!!
The count should be like in the example sheet...
You`re neeearly there!!
THANKS AGAING

**martindwilson** · 11-27-2011, 06:10 PM

assuming blanks are always at the end never in between
so you don't have
x blank 12 blank 45 48 blank
then
=IF(ISERROR(MATCH(REPT("z",255),A1:G1,1)),7-COUNTIF(A1:G1,""),7-COUNTIF(A1:G1,"")-MATCH(REPT("z",255),A1:G1,1))*TIMEVALUE("00:30")

**LoooM** · 11-27-2011, 07:29 PM

LEGEND!!

Omg I don`t know how to thank you!!
If I have other questions about other formulas should I open a new Thread.. right?!
THANKS AGAIN
Warmest regards..
Luca