Trying to use the value of a cell for criteria in COUNTIF
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I've constructed a gradebook in LibreOffice Calc, and I'm trying to keep track of how many students have scored an A, B, etc., on assignments. Currently, I have the grades for an assignment in a single column. The grades are displayed in terms of points (not percentages, so the total number of points available is not necessarily 100). I am trying to construct a cell in the column that will count the number of cells representing the grades if the cell is >= 0.9 × (total number of points), and the total number of points is also in a cell in the same column, but I keep getting an error. So far I've tried the following:
=COUNTIF(E2:E25, ">="0.9*E27)
but no luck.
After reading some articles on Open Office, I tried the following
=COUNTIF(E2:E25, ">="&E27)
which did not throw an error, but is not comparing it to the quantity desired.
I've also tried
=COUNTIF(E2:E25, ">=0.9*"&E27)
and it just produces 0.
Does anyone know if this type of thing is possible to do within COUNTIF, or do I actually need to construct an additional cell where I put in 0.9*E27 and just reference that cell?
worksheet-function libreoffice-calc countif
edited Feb 2 at 6:50
Scott
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
add a comment |
I've constructed a gradebook in LibreOffice Calc, and I'm trying to keep track of how many students have scored an A, B, etc., on assignments. Currently, I have the grades for an assignment in a single column. The grades are displayed in terms of points (not percentages, so the total number of points available is not necessarily 100). I am trying to construct a cell in the column that will count the number of cells representing the grades if the cell is >= 0.9 × (total number of points), and the total number of points is also in a cell in the same column, but I keep getting an error. So far I've tried the following:
=COUNTIF(E2:E25, ">="0.9*E27)
but no luck.
After reading some articles on Open Office, I tried the following
=COUNTIF(E2:E25, ">="&E27)
which did not throw an error, but is not comparing it to the quantity desired.
I've also tried
=COUNTIF(E2:E25, ">=0.9*"&E27)
and it just produces 0.
Does anyone know if this type of thing is possible to do within COUNTIF, or do I actually need to construct an additional cell where I put in 0.9*E27 and just reference that cell?
worksheet-function libreoffice-calc countif
edited Feb 2 at 6:50
Scott
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
1
Your first form is nearly correct: it should be=COUNTIF(E2:E25, ">="&0.9*E27)(you omitted the&).
– AFH
Feb 1 at 17:55
add a comment |
I've constructed a gradebook in LibreOffice Calc, and I'm trying to keep track of how many students have scored an A, B, etc., on assignments. Currently, I have the grades for an assignment in a single column. The grades are displayed in terms of points (not percentages, so the total number of points available is not necessarily 100). I am trying to construct a cell in the column that will count the number of cells representing the grades if the cell is >= 0.9 × (total number of points), and the total number of points is also in a cell in the same column, but I keep getting an error. So far I've tried the following:
=COUNTIF(E2:E25, ">="0.9*E27)
but no luck.
After reading some articles on Open Office, I tried the following
=COUNTIF(E2:E25, ">="&E27)
which did not throw an error, but is not comparing it to the quantity desired.
I've also tried
=COUNTIF(E2:E25, ">=0.9*"&E27)
and it just produces 0.
Does anyone know if this type of thing is possible to do within COUNTIF, or do I actually need to construct an additional cell where I put in 0.9*E27 and just reference that cell?
worksheet-function libreoffice-calc countif
edited Feb 2 at 6:50
Scott
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
I've constructed a gradebook in LibreOffice Calc, and I'm trying to keep track of how many students have scored an A, B, etc., on assignments. Currently, I have the grades for an assignment in a single column. The grades are displayed in terms of points (not percentages, so the total number of points available is not necessarily 100). I am trying to construct a cell in the column that will count the number of cells representing the grades if the cell is >= 0.9 × (total number of points), and the total number of points is also in a cell in the same column, but I keep getting an error. So far I've tried the following:
=COUNTIF(E2:E25, ">="0.9*E27)
but no luck.
After reading some articles on Open Office, I tried the following
=COUNTIF(E2:E25, ">="&E27)
which did not throw an error, but is not comparing it to the quantity desired.
I've also tried
=COUNTIF(E2:E25, ">=0.9*"&E27)
and it just produces 0.
Does anyone know if this type of thing is possible to do within COUNTIF, or do I actually need to construct an additional cell where I put in 0.9*E27 and just reference that cell?
worksheet-function libreoffice-calc countif
worksheet-function libreoffice-calc countif
edited Feb 2 at 6:50
Scott
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
edited Feb 2 at 6:50
Scott
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
edited Feb 2 at 6:50
Scott
16.2k113990
edited Feb 2 at 6:50
Scott
16.2k113990
edited Feb 2 at 6:50
Scott
16.2k113990
16.2k113990
asked Feb 1 at 17:35
user992998user992998
1
asked Feb 1 at 17:35
user992998user992998
1
asked Feb 1 at 17:35
user992998user992998
1
1
1
Your first form is nearly correct: it should be=COUNTIF(E2:E25, ">="&0.9*E27)(you omitted the&).
– AFH
Feb 1 at 17:55
add a comment |
1
Your first form is nearly correct: it should be=COUNTIF(E2:E25, ">="&0.9*E27)(you omitted the&).
– AFH
Feb 1 at 17:55
1
1
Your first form is nearly correct: it should be
=COUNTIF(E2:E25, ">="&0.9*E27) (you omitted the &).– AFH
Feb 1 at 17:55
Your first form is nearly correct: it should be
=COUNTIF(E2:E25, ">="&0.9*E27) (you omitted the &).– AFH
Feb 1 at 17:55
add a comment |
1 Answer
1
active
oldest
votes
Your third form is nearly right.
It should be
=COUNTIF(E2:E25, ">="&0.9*E27)
— you got the order wrong.
The issue is that, for the relationship operators (>, >=, <, >=,
<> (the bizarre way of expressing “not equal” in spreadsheets) or =),
the second argument to COUNTIF must be a text string:
Syntax:
COUNTIF(test_range; condition)
test_rangeis the range to be tested.
conditionmay be:
- a number, such as
34.5
- an expression, such as
2/3orSQRT(B5)
- a text string
COUNTIFcounts those cells intest_range
that are equal tocondition,
unlessconditionis a text string that starts with a comparator:
>,<,>=,<=,=,<>.
In this caseCOUNTIFcompares those cells intest_range
with the remainder of the text string
(interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell
is greater than the number 4.5,
and the condition “<dog” tests if the content of each cell
would come alphabetically before the textdog.
Source: Documentation / How Tos / Calc: COUNTIF function
(it’s the same in Excel and LibreOffice)
So, if, for example, cell E27 contains 60,
then you want to count the cells that contain values ≥ 54
(because 90% × 60 = 54).
So you need to pass COUNTIF a string argument of >=54.
So you need to compute 54 and append it to >=.
Use 0.9*E27 to compute 54,
and then ">="&0.9*E27 to do the string concatenation.
answered Feb 1 at 23:43
ScottScott
16.2k113990
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401081%2ftrying-to-use-the-value-of-a-cell-for-criteria-in-countif%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your third form is nearly right.
It should be
=COUNTIF(E2:E25, ">="&0.9*E27)
— you got the order wrong.
The issue is that, for the relationship operators (>, >=, <, >=,
<> (the bizarre way of expressing “not equal” in spreadsheets) or =),
the second argument to COUNTIF must be a text string:
Syntax:
COUNTIF(test_range; condition)
test_rangeis the range to be tested.
conditionmay be:
- a number, such as
34.5
- an expression, such as
2/3orSQRT(B5)
- a text string
COUNTIFcounts those cells intest_range
that are equal tocondition,
unlessconditionis a text string that starts with a comparator:
>,<,>=,<=,=,<>.
In this caseCOUNTIFcompares those cells intest_range
with the remainder of the text string
(interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell
is greater than the number 4.5,
and the condition “<dog” tests if the content of each cell
would come alphabetically before the textdog.
Source: Documentation / How Tos / Calc: COUNTIF function
(it’s the same in Excel and LibreOffice)
So, if, for example, cell E27 contains 60,
then you want to count the cells that contain values ≥ 54
(because 90% × 60 = 54).
So you need to pass COUNTIF a string argument of >=54.
So you need to compute 54 and append it to >=.
Use 0.9*E27 to compute 54,
and then ">="&0.9*E27 to do the string concatenation.
answered Feb 1 at 23:43
ScottScott
16.2k113990
add a comment |
Your third form is nearly right.
It should be
=COUNTIF(E2:E25, ">="&0.9*E27)
— you got the order wrong.
The issue is that, for the relationship operators (>, >=, <, >=,
<> (the bizarre way of expressing “not equal” in spreadsheets) or =),
the second argument to COUNTIF must be a text string:
Syntax:
COUNTIF(test_range; condition)
test_rangeis the range to be tested.
conditionmay be:
- a number, such as
34.5
- an expression, such as
2/3orSQRT(B5)
- a text string
COUNTIFcounts those cells intest_range
that are equal tocondition,
unlessconditionis a text string that starts with a comparator:
>,<,>=,<=,=,<>.
In this caseCOUNTIFcompares those cells intest_range
with the remainder of the text string
(interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell
is greater than the number 4.5,
and the condition “<dog” tests if the content of each cell
would come alphabetically before the textdog.
Source: Documentation / How Tos / Calc: COUNTIF function
(it’s the same in Excel and LibreOffice)
So, if, for example, cell E27 contains 60,
then you want to count the cells that contain values ≥ 54
(because 90% × 60 = 54).
So you need to pass COUNTIF a string argument of >=54.
So you need to compute 54 and append it to >=.
Use 0.9*E27 to compute 54,
and then ">="&0.9*E27 to do the string concatenation.
answered Feb 1 at 23:43
ScottScott
16.2k113990
add a comment |
Your third form is nearly right.
It should be
=COUNTIF(E2:E25, ">="&0.9*E27)
— you got the order wrong.
The issue is that, for the relationship operators (>, >=, <, >=,
<> (the bizarre way of expressing “not equal” in spreadsheets) or =),
the second argument to COUNTIF must be a text string:
Syntax:
COUNTIF(test_range; condition)
test_rangeis the range to be tested.
conditionmay be:
- a number, such as
34.5
- an expression, such as
2/3orSQRT(B5)
- a text string
COUNTIFcounts those cells intest_range
that are equal tocondition,
unlessconditionis a text string that starts with a comparator:
>,<,>=,<=,=,<>.
In this caseCOUNTIFcompares those cells intest_range
with the remainder of the text string
(interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell
is greater than the number 4.5,
and the condition “<dog” tests if the content of each cell
would come alphabetically before the textdog.
Source: Documentation / How Tos / Calc: COUNTIF function
(it’s the same in Excel and LibreOffice)
So, if, for example, cell E27 contains 60,
then you want to count the cells that contain values ≥ 54
(because 90% × 60 = 54).
So you need to pass COUNTIF a string argument of >=54.
So you need to compute 54 and append it to >=.
Use 0.9*E27 to compute 54,
and then ">="&0.9*E27 to do the string concatenation.
answered Feb 1 at 23:43
ScottScott
16.2k113990
Your third form is nearly right.
It should be
=COUNTIF(E2:E25, ">="&0.9*E27)
— you got the order wrong.
The issue is that, for the relationship operators (>, >=, <, >=,
<> (the bizarre way of expressing “not equal” in spreadsheets) or =),
the second argument to COUNTIF must be a text string:
Syntax:
COUNTIF(test_range; condition)
test_rangeis the range to be tested.
conditionmay be:
- a number, such as
34.5
- an expression, such as
2/3orSQRT(B5)
- a text string
COUNTIFcounts those cells intest_range
that are equal tocondition,
unlessconditionis a text string that starts with a comparator:
>,<,>=,<=,=,<>.
In this caseCOUNTIFcompares those cells intest_range
with the remainder of the text string
(interpreted as a number if possible or text otherwise).
For example the condition “>4.5” tests if the content of each cell
is greater than the number 4.5,
and the condition “<dog” tests if the content of each cell
would come alphabetically before the textdog.
Source: Documentation / How Tos / Calc: COUNTIF function
(it’s the same in Excel and LibreOffice)
So, if, for example, cell E27 contains 60,
then you want to count the cells that contain values ≥ 54
(because 90% × 60 = 54).
So you need to pass COUNTIF a string argument of >=54.
So you need to compute 54 and append it to >=.
Use 0.9*E27 to compute 54,
and then ">="&0.9*E27 to do the string concatenation.
answered Feb 1 at 23:43
ScottScott
16.2k113990
answered Feb 1 at 23:43
ScottScott
16.2k113990
answered Feb 1 at 23:43
ScottScott
16.2k113990
answered Feb 1 at 23:43
ScottScott
16.2k113990
16.2k113990
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1401081%2ftrying-to-use-the-value-of-a-cell-for-criteria-in-countif%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Your first form is nearly correct: it should be
=COUNTIF(E2:E25, ">="&0.9*E27)(you omitted the&).– AFH
Feb 1 at 17:55