Trying to use CountIfs worksheet function in excel VBA
Im trying to search a range of data D8:D27 and count the number of entries that are greater than and less than two numbers that I have entered in F29 and F30 respectively. I cannot get the function to work properly. When I run it, it outputs 0 into L27 which is incorrect. I believe I have a syntax error but I cannot figure it out. Any help is appreciated. Thanks
Private Sub Calculation()
Dim MySum1 As Double
Dim UCL1 As Double
Dim LCL1 As Double
UCL1 = Range("F29")
LCL1 = Range("F30")
MySum1 = Application.WorksheetFunction.CountIfs(Range("D8:D27"), "> UCL1",
Range("D8:D27"), "< LCL1")
Range("L27").Value = MySum1
End Sub
The sample data im using is below. F29 is 235.500 and F30 is 230.500
It should be outputting 4, but I am getting 0 for some reason.
233.232
232.183
233.634
231.342
235.684
231.883
233.931
233.755
232.119
231.543
236.423
233.517
234.653
234.444
231.298
235.755
230.223
233.782
233.511
233.987
excel vba excel-vba
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
add a comment |
Im trying to search a range of data D8:D27 and count the number of entries that are greater than and less than two numbers that I have entered in F29 and F30 respectively. I cannot get the function to work properly. When I run it, it outputs 0 into L27 which is incorrect. I believe I have a syntax error but I cannot figure it out. Any help is appreciated. Thanks
Private Sub Calculation()
Dim MySum1 As Double
Dim UCL1 As Double
Dim LCL1 As Double
UCL1 = Range("F29")
LCL1 = Range("F30")
MySum1 = Application.WorksheetFunction.CountIfs(Range("D8:D27"), "> UCL1",
Range("D8:D27"), "< LCL1")
Range("L27").Value = MySum1
End Sub
The sample data im using is below. F29 is 235.500 and F30 is 230.500
It should be outputting 4, but I am getting 0 for some reason.
233.232
232.183
233.634
231.342
235.684
231.883
233.931
233.755
232.119
231.543
236.423
233.517
234.653
234.444
231.298
235.755
230.223
233.782
233.511
233.987
excel vba excel-vba
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35
add a comment |
Im trying to search a range of data D8:D27 and count the number of entries that are greater than and less than two numbers that I have entered in F29 and F30 respectively. I cannot get the function to work properly. When I run it, it outputs 0 into L27 which is incorrect. I believe I have a syntax error but I cannot figure it out. Any help is appreciated. Thanks
Private Sub Calculation()
Dim MySum1 As Double
Dim UCL1 As Double
Dim LCL1 As Double
UCL1 = Range("F29")
LCL1 = Range("F30")
MySum1 = Application.WorksheetFunction.CountIfs(Range("D8:D27"), "> UCL1",
Range("D8:D27"), "< LCL1")
Range("L27").Value = MySum1
End Sub
The sample data im using is below. F29 is 235.500 and F30 is 230.500
It should be outputting 4, but I am getting 0 for some reason.
233.232
232.183
233.634
231.342
235.684
231.883
233.931
233.755
232.119
231.543
236.423
233.517
234.653
234.444
231.298
235.755
230.223
233.782
233.511
233.987
excel vba excel-vba
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
Im trying to search a range of data D8:D27 and count the number of entries that are greater than and less than two numbers that I have entered in F29 and F30 respectively. I cannot get the function to work properly. When I run it, it outputs 0 into L27 which is incorrect. I believe I have a syntax error but I cannot figure it out. Any help is appreciated. Thanks
Private Sub Calculation()
Dim MySum1 As Double
Dim UCL1 As Double
Dim LCL1 As Double
UCL1 = Range("F29")
LCL1 = Range("F30")
MySum1 = Application.WorksheetFunction.CountIfs(Range("D8:D27"), "> UCL1",
Range("D8:D27"), "< LCL1")
Range("L27").Value = MySum1
End Sub
The sample data im using is below. F29 is 235.500 and F30 is 230.500
It should be outputting 4, but I am getting 0 for some reason.
233.232
232.183
233.634
231.342
235.684
231.883
233.931
233.755
232.119
231.543
236.423
233.517
234.653
234.444
231.298
235.755
230.223
233.782
233.511
233.987
excel vba excel-vba
excel vba excel-vba
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
edited Nov 12 '18 at 7:22
Pᴇʜ
21.8k42750
21.8k42750
asked Nov 11 '18 at 16:16
CShieldCShield
13
asked Nov 11 '18 at 16:16
CShieldCShield
13
asked Nov 11 '18 at 16:16
CShieldCShield
13
13
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35
add a comment |
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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%2fstackoverflow.com%2fquestions%2f53250655%2ftrying-to-use-countifs-worksheet-function-in-excel-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53250655%2ftrying-to-use-countifs-worksheet-function-in-excel-vba%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
Looks like you should have ">" & CStr(UCL1) instead of "> UCL1"
– Abdurrahim
Nov 11 '18 at 16:22
I made the changes you mentioned and its still outputting 0. I shared the data in the questions now so you can see exactly what im using. I would like it to count any entries > 235.500 and < 230.500. Thanks for the help.
– CShield
Nov 11 '18 at 16:31
Wait! I think I figured it out. The countif is looking for a number that satisfies both conditions, which is no possible so thats why its 0. I need to seperate countifs, one for > and one for <. Then Ill add them together. Thanks for your help!
– CShield
Nov 11 '18 at 16:35