How to get count in pivot table that does NOT count duplicate entries
I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be counted once regardless if there are duplicate records for that combination.
For example, with the raw data below, I would like the pivot table to count 10 (not 12) total records for combinations of teacher and subject. That is, (Teacher 1, Math) only gets counted once, and the same for (Teacher 3, English).
Teacher 1 Math Student 1
Teacher 1 Math Student 2
Teacher 1 English Student 3
Teacher 1 History Student 4
Teacher 2 Math Student 5
Teacher 2 Science Student 6
Teacher 2 Biology Student 7
Teacher 3 Math Student 8
Teacher 3 Science Student 9
Teacher 3 English Student 10
Teacher 3 English Student 11
Teacher 3 Math Student 12
How can I set up my pivot table to do this?
microsoft-excel-2007 pivot-table
edited Oct 13 '14 at 15:51
Excellll
11k74162
asked Oct 13 '14 at 9:52
user379028
412
add a comment |
I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be counted once regardless if there are duplicate records for that combination.
For example, with the raw data below, I would like the pivot table to count 10 (not 12) total records for combinations of teacher and subject. That is, (Teacher 1, Math) only gets counted once, and the same for (Teacher 3, English).
Teacher 1 Math Student 1
Teacher 1 Math Student 2
Teacher 1 English Student 3
Teacher 1 History Student 4
Teacher 2 Math Student 5
Teacher 2 Science Student 6
Teacher 2 Biology Student 7
Teacher 3 Math Student 8
Teacher 3 Science Student 9
Teacher 3 English Student 10
Teacher 3 English Student 11
Teacher 3 Math Student 12
How can I set up my pivot table to do this?
microsoft-excel-2007 pivot-table
edited Oct 13 '14 at 15:51
Excellll
11k74162
asked Oct 13 '14 at 9:52
user379028
412
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52
add a comment |
I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be counted once regardless if there are duplicate records for that combination.
For example, with the raw data below, I would like the pivot table to count 10 (not 12) total records for combinations of teacher and subject. That is, (Teacher 1, Math) only gets counted once, and the same for (Teacher 3, English).
Teacher 1 Math Student 1
Teacher 1 Math Student 2
Teacher 1 English Student 3
Teacher 1 History Student 4
Teacher 2 Math Student 5
Teacher 2 Science Student 6
Teacher 2 Biology Student 7
Teacher 3 Math Student 8
Teacher 3 Science Student 9
Teacher 3 English Student 10
Teacher 3 English Student 11
Teacher 3 Math Student 12
How can I set up my pivot table to do this?
microsoft-excel-2007 pivot-table
edited Oct 13 '14 at 15:51
Excellll
11k74162
asked Oct 13 '14 at 9:52
user379028
412
I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be counted once regardless if there are duplicate records for that combination.
For example, with the raw data below, I would like the pivot table to count 10 (not 12) total records for combinations of teacher and subject. That is, (Teacher 1, Math) only gets counted once, and the same for (Teacher 3, English).
Teacher 1 Math Student 1
Teacher 1 Math Student 2
Teacher 1 English Student 3
Teacher 1 History Student 4
Teacher 2 Math Student 5
Teacher 2 Science Student 6
Teacher 2 Biology Student 7
Teacher 3 Math Student 8
Teacher 3 Science Student 9
Teacher 3 English Student 10
Teacher 3 English Student 11
Teacher 3 Math Student 12
How can I set up my pivot table to do this?
microsoft-excel-2007 pivot-table
microsoft-excel-2007 pivot-table
edited Oct 13 '14 at 15:51
Excellll
11k74162
asked Oct 13 '14 at 9:52
user379028
412
edited Oct 13 '14 at 15:51
Excellll
11k74162
asked Oct 13 '14 at 9:52
user379028
412
edited Oct 13 '14 at 15:51
Excellll
11k74162
edited Oct 13 '14 at 15:51
Excellll
11k74162
edited Oct 13 '14 at 15:51
Excellll
11k74162
11k74162
asked Oct 13 '14 at 9:52
user379028
412
asked Oct 13 '14 at 9:52
user379028
412
asked Oct 13 '14 at 9:52
user379028
412
412
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52
add a comment |
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52
add a comment |
2 Answers
2
active
oldest
votes
Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.
Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Then create your pivot based on the 1st and 3rd column.
edited May 23 '17 at 12:41
Community♦
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
add a comment |
I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.
answered Mar 22 '15 at 6:57
Brian
1
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%2f825486%2fhow-to-get-count-in-pivot-table-that-does-not-count-duplicate-entries%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
Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.
Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Then create your pivot based on the 1st and 3rd column.
edited May 23 '17 at 12:41
Community♦
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
add a comment |
Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.
Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Then create your pivot based on the 1st and 3rd column.
edited May 23 '17 at 12:41
Community♦
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
add a comment |
Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.
Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Then create your pivot based on the 1st and 3rd column.
edited May 23 '17 at 12:41
Community♦
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
Unfortunately, this isn't directly possible in Excel 2007. If you were on Excel 2013 or Excel 2010 with the PowerPivot add-in, you could do it using the new 'Distinct Count' feature.
Siddharth Rout outlined a good workaround on StackOverflow. Add a column to your data, and copy this formula down:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Then create your pivot based on the 1st and 3rd column.
edited May 23 '17 at 12:41
Community♦
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
answered Oct 23 '14 at 16:01
Andi Mohr
3,33542044
3,33542044
add a comment |
add a comment |
I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.
answered Mar 22 '15 at 6:57
Brian
1
add a comment |
I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.
answered Mar 22 '15 at 6:57
Brian
1
add a comment |
I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.
answered Mar 22 '15 at 6:57
Brian
1
I use a concatenate function, add a D column with concatenate(a2,b2) thus creating a unique id for teacher+subject. Copy the formula down. Then use that column as your count value.
answered Mar 22 '15 at 6:57
Brian
1
answered Mar 22 '15 at 6:57
Brian
1
answered Mar 22 '15 at 6:57
Brian
1
answered Mar 22 '15 at 6:57
Brian
1
1
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f825486%2fhow-to-get-count-in-pivot-table-that-does-not-count-duplicate-entries%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
Thanks David, I really am useless when it comes to excel actually pretty much anything technical
– user379028
Oct 13 '14 at 10:16
No, this doesn't make sense to me. Could you give an example of what the data should look like in the pivot table?
– Raystafarian
Oct 13 '14 at 11:17
Total Subjects Total Students
– user379028
Oct 13 '14 at 11:39
the pivot table should have Teacher 1, 2 and 3 in the first column and then calculate how many subjects per teacher, so teacher 1 will have 3 subjects as Math is repeated.
– user379028
Oct 13 '14 at 11:42
I tried to edit your question but ended up basically rewriting it. I think I got what you're asking for, but if I messed something up, you can rollback the edit.
– Excellll
Oct 13 '14 at 15:52