Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do


"Neon520" wrote:

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

Hi there,
What I am trying to do is: if cell AA8 is x OR if cell AA11:AA49 are C,
please calculate as follow, but unfortunately, Excel doesn't pick up the
formular when I enter the cell range AA11:AA49.
However, when I try to put only one cell (AA11), the formular works just fine.

And the thing is I also tried a dummy test that I basically use the same
type of formular (but less complex calculation at the end), the formular also
works fine.

I wonder what went wrong? Doesn't Excel pick the original formular because
it is AA, or because the cell range (AA11:AA49) related to something else in
the sheet that might cause this complication/error?

Neon520

"daddylonglegs" wrote:

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do


"Neon520" wrote:

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

You can only use the construction IF(AA11:AA49="C", in a so-called "array
formula" where the next part matches, e.g. in a formula like

=SUM(IF(AA11:AA49="C",BB11:BB49))

In your formula the $A$50-COUNTIF...etc part gives a single value so this
isn't valid..
I still can't quite work out what you want to do. Can you explain what you
want the result to be in a single cell?

"Neon520" wrote:

Hi there,
What I am trying to do is: if cell AA8 is x OR if cell AA11:AA49 are C,
please calculate as follow, but unfortunately, Excel doesn't pick up the
formular when I enter the cell range AA11:AA49.
However, when I try to put only one cell (AA11), the formular works just fine.

And the thing is I also tried a dummy test that I basically use the same
type of formular (but less complex calculation at the end), the formular also
works fine.

I wonder what went wrong? Doesn't Excel pick the original formular because
it is AA, or because the cell range (AA11:AA49) related to something else in
the sheet that might cause this complication/error?

Neon520

"daddylonglegs" wrote:

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do


"Neon520" wrote:

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

Hi there again,

I don't think the end part (value_if_false) is the cause of this problem.
Let's say if you want to calculate: if cell AA8 is "X" OR if cell range
AA11:AA49 are "C", please say "YES" other wise say "NO". And the formular
for this would be: =IF(OR(AA3="X",AA11:AA49="C"),"YES","NO")

Is this the correct formular to do so?
If not, what should I do to get the result I want?

Anyway, do you know what function should I use if want to calculate a
certain cell range, but ONLY IF they contain value, otherwise the cell blank.
I don't want to put all SUM function, because when there are no value, it'll
come out as "0"

Thanks,
Neon520


"daddylonglegs" wrote:

You can only use the construction IF(AA11:AA49="C", in a so-called "array
formula" where the next part matches, e.g. in a formula like

=SUM(IF(AA11:AA49="C",BB11:BB49))

In your formula the $A$50-COUNTIF...etc part gives a single value so this
isn't valid..
I still can't quite work out what you want to do. Can you explain what you
want the result to be in a single cell?

"Neon520" wrote:

Hi there,
What I am trying to do is: if cell AA8 is x OR if cell AA11:AA49 are C,
please calculate as follow, but unfortunately, Excel doesn't pick up the
formular when I enter the cell range AA11:AA49.
However, when I try to put only one cell (AA11), the formular works just fine.

And the thing is I also tried a dummy test that I basically use the same
type of formular (but less complex calculation at the end), the formular also
works fine.

I wonder what went wrong? Doesn't Excel pick the original formular because
it is AA, or because the cell range (AA11:AA49) related to something else in
the sheet that might cause this complication/error?

Neon520

"daddylonglegs" wrote:

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do


"Neon520" wrote:

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520

You can use that formula if you confirm with CTRL+SHIFT+ENTER but, as I said
before, it is only valid for a single cell.

In your original formula you have another check for AA11=AA49="C". I'm not
sure you need both, and the second would also need to be enclosed in an OR
function. I'm not sure it matches your intent but perhaps you could use this
formula:


=IF(AA8="X","",IF(OR(AA11:AA49="C"),"",$A$50-SUM(COUNTIF(AA11:AA49,{"X","N","C","A","AE","B","B E","LTL"}))))

confirmed with CTRL+SHIFT+ENTER

"Neon520" wrote:

Hi there again,

I don't think the end part (value_if_false) is the cause of this problem.
Let's say if you want to calculate: if cell AA8 is "X" OR if cell range
AA11:AA49 are "C", please say "YES" other wise say "NO". And the formular
for this would be: =IF(OR(AA3="X",AA11:AA49="C"),"YES","NO")

Is this the correct formular to do so?
If not, what should I do to get the result I want?

Anyway, do you know what function should I use if want to calculate a
certain cell range, but ONLY IF they contain value, otherwise the cell blank.
I don't want to put all SUM function, because when there are no value, it'll
come out as "0"

Thanks,
Neon520


"daddylonglegs" wrote:

You can only use the construction IF(AA11:AA49="C", in a so-called "array
formula" where the next part matches, e.g. in a formula like

=SUM(IF(AA11:AA49="C",BB11:BB49))

In your formula the $A$50-COUNTIF...etc part gives a single value so this
isn't valid..
I still can't quite work out what you want to do. Can you explain what you
want the result to be in a single cell?

"Neon520" wrote:

Hi there,
What I am trying to do is: if cell AA8 is x OR if cell AA11:AA49 are C,
please calculate as follow, but unfortunately, Excel doesn't pick up the
formular when I enter the cell range AA11:AA49.
However, when I try to put only one cell (AA11), the formular works just fine.

And the thing is I also tried a dummy test that I basically use the same
type of formular (but less complex calculation at the end), the formular also
works fine.

I wonder what went wrong? Doesn't Excel pick the original formular because
it is AA, or because the cell range (AA11:AA49) related to something else in
the sheet that might cause this complication/error?

Neon520

"daddylonglegs" wrote:

Hello Neon520,

I don't think you can enter that formula in a range. Perhaps you can explain
in words what you are trying to do


"Neon520" wrote:

Hi there,
I have a question about the "cell reference" in Excel.
I wonder why Excel doesn't pick up the cell reference that I enter in my
fomula
(=IF(OR(AA8="X",AA11:AA49="C"),"",IF(AA11:AA49="C" ,"",$A$50-COUNTIF(AA11:AA49,"X")-COUNTIF(AA11:AA49,"N")-COUNTIF(AA11:AA49,"C")-COUNTIF(AA11:AA49,"A")-COUNTIF(AA11:AA49,"AE")-COUNTIF(AA11:AA49,"B")-COUNTIF(AA11:AA49,"BE")-COUNTIF(AA11:AA49,"LTL")))

Excel doesn't pick up (OR(AA8="X",AA11:AA49="C") this part at all. I think
the rest of the formular is fine. But when I try (ORAA8="X",AA11="C"), the
formular works fine.

Excel gives me (#VALUE!) error when I enter it as a cell range (AA11:AA49),
instead of one cell (AA11)

Does anyone have any idea of how to fix this, or what is wrong with my
formular?
Any info/clue are appreciated!!!!!

Neon520