excel countif filtered by style
I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?
microsoft-excel countif
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
add a comment |
I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?
microsoft-excel countif
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
add a comment |
I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?
microsoft-excel countif
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?
microsoft-excel countif
microsoft-excel countif
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
asked Apr 18 '12 at 2:06
Bill WeinmanBill Weinman
17316
17316
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.
If your styles have different shading colours then you can use the following method.
Step 1: Convert your range to a list and then adding a total row showing COUNT
Step 2: Apply a colour filter (should work on Excel 2007 and later):
Done: The COUNT total will show the filtered number of rows.
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
add a comment |
You could use VBA for that:
Function CountStyle(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Neutral"
If Item.Style = "Neutral" Then
Total = Total + 1
End If
Next Item
CountStyle = Total
End Function
Taken from here.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- Go to Excel and choose the cell, where the result should be in. Write e.g.
=CountStyle (B4:B23)
Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.
answered Oct 18 '16 at 19:36
testingtesting
33141125
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%2f413697%2fexcel-countif-filtered-by-style%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.
If your styles have different shading colours then you can use the following method.
Step 1: Convert your range to a list and then adding a total row showing COUNT
Step 2: Apply a colour filter (should work on Excel 2007 and later):
Done: The COUNT total will show the filtered number of rows.
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
add a comment |
No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.
If your styles have different shading colours then you can use the following method.
Step 1: Convert your range to a list and then adding a total row showing COUNT
Step 2: Apply a colour filter (should work on Excel 2007 and later):
Done: The COUNT total will show the filtered number of rows.
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
add a comment |
No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.
If your styles have different shading colours then you can use the following method.
Step 1: Convert your range to a list and then adding a total row showing COUNT
Step 2: Apply a colour filter (should work on Excel 2007 and later):
Done: The COUNT total will show the filtered number of rows.
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.
If your styles have different shading colours then you can use the following method.
Step 1: Convert your range to a list and then adding a total row showing COUNT
Step 2: Apply a colour filter (should work on Excel 2007 and later):
Done: The COUNT total will show the filtered number of rows.
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
answered Apr 18 '12 at 8:31
Mike FitzpatrickMike Fitzpatrick
14.4k33540
14.4k33540
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
add a comment |
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks.
– Bill Weinman
May 3 '12 at 18:24
add a comment |
You could use VBA for that:
Function CountStyle(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Neutral"
If Item.Style = "Neutral" Then
Total = Total + 1
End If
Next Item
CountStyle = Total
End Function
Taken from here.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- Go to Excel and choose the cell, where the result should be in. Write e.g.
=CountStyle (B4:B23)
Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.
answered Oct 18 '16 at 19:36
testingtesting
33141125
add a comment |
You could use VBA for that:
Function CountStyle(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Neutral"
If Item.Style = "Neutral" Then
Total = Total + 1
End If
Next Item
CountStyle = Total
End Function
Taken from here.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- Go to Excel and choose the cell, where the result should be in. Write e.g.
=CountStyle (B4:B23)
Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.
answered Oct 18 '16 at 19:36
testingtesting
33141125
add a comment |
You could use VBA for that:
Function CountStyle(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Neutral"
If Item.Style = "Neutral" Then
Total = Total + 1
End If
Next Item
CountStyle = Total
End Function
Taken from here.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- Go to Excel and choose the cell, where the result should be in. Write e.g.
=CountStyle (B4:B23)
Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.
answered Oct 18 '16 at 19:36
testingtesting
33141125
You could use VBA for that:
Function CountStyle(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Neutral"
If Item.Style = "Neutral" Then
Total = Total + 1
End If
Next Item
CountStyle = Total
End Function
Taken from here.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- Go to Excel and choose the cell, where the result should be in. Write e.g.
=CountStyle (B4:B23)
Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.
answered Oct 18 '16 at 19:36
testingtesting
33141125
answered Oct 18 '16 at 19:36
testingtesting
33141125
answered Oct 18 '16 at 19:36
testingtesting
33141125
answered Oct 18 '16 at 19:36
testingtesting
33141125
33141125
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%2f413697%2fexcel-countif-filtered-by-style%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
