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.

"Carmit" wrote:

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.

thanks

"Joel" wrote:

=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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.
--
David Biddulph

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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.

"David Biddulph" wrote:

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.
--
David Biddulph

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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.
--
David Biddulph

"Joel" wrote in message
...
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.

"David Biddulph" wrote:

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.

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

I'm working with 2003.

"David Biddulph" wrote:

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.
--
David Biddulph

"Joel" wrote in message
...
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.

"David Biddulph" wrote:

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.

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
I'm working with 2003.

"David Biddulph" wrote:

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.
--
David Biddulph

"Joel" wrote in message
...
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.

"David Biddulph" wrote:

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.

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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.
--
David Biddulph

"Joel" wrote in message
...
I'm working with 2003.

"David Biddulph" wrote:

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.

"Joel" wrote in message
...
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.

"David Biddulph" wrote:

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.

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.

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.


"Joel" wrote:

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.

"David Biddulph" wrote:

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.
--
David Biddulph

"Joel" wrote in message
...
=COUNTIF((x12):(z12):(m12),3)

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

"Carmit" wrote:

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.