Count formula issue

[mevetts]

Hi all,

I'm using this formula -

=IF(COUNTA(Markbook!E57:GB57),COUNTIF(Markbook!E57:GB57,"ü")/COUNTA(Markbook!E57
:GB57)*100,"")

It is working out as a percentage how many times a symbol appears in
that range.

However, the symbols being counted are inserted into every other
column. In the columns in between, other data is being placed.

The formula is counting every cell in the range that contains data, but
what I want is for it to just count every other column that contains
symbols.

Is this possible?

Thanks,

Mark.

 

[Bob Phillips]

Try replacing

COUNTIF(Markbook!E57:GB57,"ü")

with

=SUMPRODUCT(--(MOD(ROW(Markbook!E57:GB57),2)=0),--(Markbook!E57:GB57="ü"))
or
=SUMPRODUCT(--(MOD(ROW(Markbook!E57:GB57),2)=1),--(Markbook!E57:GB57="ü"))

depending upon which row the charcater is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mevetts]

Hi Bob,

The info is all on the same row, just every other column contains
symbols.

Does this make a difference to your suggestion?

 

[Biff]

Hi!

Try this:

=IF(COUNTIF(Markbook!E57:GB57,"ü"),COUNTIF(Markbook!E57:GB57,"ü")/90*100,"")

Biff

 

[mevetts]

Hi Biff,

What do the changes you have made actually do

 

[Biff]

What do the changes you have made actually do?

Well, as I understand your post:

Starting in column E through column GB, every other column may contain the
symbol. The columns in between will also contain *other data but not the
symbol*. So, your symbol can only be in a total of 90 columns. E:GB=180/2

Count the number of symbols divided by 90 columns = %

I may be off my rocker but that's how I read your post!

Biff

 

[mevetts]

Not off your rocker, but in fact spot on.

However, of the 90 columns that may contain the symbol, they all wont.

What the original formula does is ask how many cells contain data an
divide by this figure.

However, when the additional data is entered in between each of th
columns, then it screws up the denominator figure

 

[Biff]

Ok, now I am confused!

If you want to know the % of symbols in the range and the symbols can only
be in certain cells then the % of symbols would be restricted to those
certain cells. That's what my formula does.

On the other hand, if you want the % of symbols from the *entire* range then
it should be Countif(symbol)/Counta(range).

What am I missing?

Biff

 

[Bob Phillips]

Yeah, change ROW to COLUMN

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)