help with formula range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i try to add formula of "count if". the range is not from cell x to cell y,
but it is cell x, cell z & cell M, which are not sequential.
 
=COUNTIF((x12):(z12):(m12),3)

Adding the parethesis and colons make them individual cells and not a range.
 
Joel,

Have you tried that formula putting a value of 3 in one or more of the
intervening cells, such as N12 and/or Y12?
Colons are used for ranges.
 
This is fully tested. I made sure it was not including the cells in between.
I put data in columns A - E and then included only columns A, C, E and it
did not include b and c in the sum. Why don't you try it.
 
I tried exactly the formula you quoted:
=COUNTIF((x12):(z12):(m12),3)

With X12, Z12, and M12 blank, putting a 3 in N12 gave a result of 1, and
also putting a 3 in Y12 increased the count to 2.

Clicking on the fx button next to the formula bar shows the 3 from N12 among
the long string of zeroes, making it clear that the range includes more than
just the 3 cells..

Hence my question to you.
 
I'm working with 2003.

David Biddulph said:
I tried exactly the formula you quoted:
=COUNTIF((x12):(z12):(m12),3)

With X12, Z12, and M12 blank, putting a 3 in N12 gave a result of 1, and
also putting a 3 in Y12 increased the count to 2.

Clicking on the fx button next to the formula bar shows the 3 from N12 among
the long string of zeroes, making it clear that the range includes more than
just the 3 cells..

Hence my question to you.
Click to expand...
 
Joel,

Your formula also works in XL97 plus the fx button shows the whole range
including the intermediate values as David says but it only counts, (or sums
if you use SUMIF()), the referenced cells not the intermediate ones.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ditto (11.8146.8132).
It'll be interesting to hear whether other folk get the same results as I
do, or the same as yours.

My Windows Regional Settings are English (UK), but I don't know whether
that's likely to have any effect.
 
Joel,

Your formula doesn't work on my machine with XL97. I get the same answere
with David Biddulph. To me your formula is not a "bullet proof" and won't
work on all machines.
 
Back
Top