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.
but it is cell x, cell z & cell M, which are not sequential.
G
Guest
=COUNTIF((x12)
z12)m12),3)
Adding the parethesis and colons make them individual cells and not a range.
z12)m12),3)Adding the parethesis and colons make them individual cells and not a range.
G
Guest
thanks
Joel said:=COUNTIF((x12)
Adding the parethesis and colons make them individual cells and not a range.
D
David Biddulph
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.
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.
G
Guest
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 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.
D
David Biddulph
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.
=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.
G
Guest
I'm working with 2003.
David Biddulph said:I tried exactly the formula you quoted:
=COUNTIF((x12)
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.
S
Sandy Mann
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
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
D
David Biddulph
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.
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.
G
Guest
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.
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.