Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Hmmmm, doesn't seem to be returning the results I want.
If I have 14 text entries of "House" that all meet the criteria that I set, I want the result to be 1 (unique entry). So I'm looking for a way (like with Sumproduct) to set multiple criteria for Col. A but also to identify how many unique entries satisfy those criteria since there are many duplicates. Does that help? "Sheeloo" wrote: You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2)) with double negations before all open parentheses. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheeloo" <Click above to get my email id wrote in message ... You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Thanks Bernard,
Sorry for the typo... -Sheeloo "Bernard Liengme" wrote: That should read =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2)) with double negations before all open parentheses. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheeloo" <Click above to get my email id wrote in message ... You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Not sure what you want... Pl. provide sample data.
"blswes" wrote: Hmmmm, doesn't seem to be returning the results I want. If I have 14 text entries of "House" that all meet the criteria that I set, I want the result to be 1 (unique entry). So I'm looking for a way (like with Sumproduct) to set multiple criteria for Col. A but also to identify how many unique entries satisfy those criteria since there are many duplicates. Does that help? "Sheeloo" wrote: You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Does it matter, Bernard? Couldn't you also get away with
=SUMPRODUCT(-(A2:A100=A2),-(B2:B100=B2),-(C2:C100C2),-(D2:D100<D2)) ? Isn't it OK provided that the total number of negations is even? -- David Biddulph "Bernard Liengme" wrote in message ... That should read =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2)) with double negations before all open parentheses. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheeloo" <Click above to get my email id wrote in message ... You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
You are right David :-)
Unfortunately it was not intentional in this case... It was a typo on my part. "David Biddulph" wrote: Does it matter, Bernard? Couldn't you also get away with =SUMPRODUCT(-(A2:A100=A2),-(B2:B100=B2),-(C2:C100C2),-(D2:D100<D2)) ? Isn't it OK provided that the total number of negations is even? -- David Biddulph "Bernard Liengme" wrote in message ... That should read =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2)) with double negations before all open parentheses. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sheeloo" <Click above to get my email id wrote in message ... You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Here would be a basic example:
Col. A Col. B Col. C Col. D Tree 40 Yes 1 Tree 45 Yes 1 Tree 45 Yes 1 Leaf 43 Yes 1 Leaf 44 Yes 1 What I'm looking for is unique entries that satisfy my criteria. So if I want unique Col. A entries that are 35 (Col. B), that have "Yes" (Col. C), and that equal 1 (Col. D), the answer is 2. But if I'm unable to get unique Col. A entries, then the answer with Sumproduct would be 5, which is not what I'm seeking. Does that help? Thanks in advance. "Sheeloo" wrote: Not sure what you want... Pl. provide sample data. "blswes" wrote: Hmmmm, doesn't seem to be returning the results I want. If I have 14 text entries of "House" that all meet the criteria that I set, I want the result to be 1 (unique entry). So I'm looking for a way (like with Sumproduct) to set multiple criteria for Col. A but also to identify how many unique entries satisfy those criteria since there are many duplicates. Does that help? "Sheeloo" wrote: You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Now I understand what you want..
You need to add a column to mark 1,2,3, against each entry in Col A - 1 against first occurence, two against the second and so on... In your example enter this in E2 =COUNTIF($A$2:A2,A2) and copy down... Starting at row 2 assuming headers in row 1 Now use SUMPRODUCT in F1 like this =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1)) "blswes" wrote: Here would be a basic example: Col. A Col. B Col. C Col. D Tree 40 Yes 1 Tree 45 Yes 1 Tree 45 Yes 1 Leaf 43 Yes 1 Leaf 44 Yes 1 What I'm looking for is unique entries that satisfy my criteria. So if I want unique Col. A entries that are 35 (Col. B), that have "Yes" (Col. C), and that equal 1 (Col. D), the answer is 2. But if I'm unable to get unique Col. A entries, then the answer with Sumproduct would be 5, which is not what I'm seeking. Does that help? Thanks in advance. "Sheeloo" wrote: Not sure what you want... Pl. provide sample data. "blswes" wrote: Hmmmm, doesn't seem to be returning the results I want. If I have 14 text entries of "House" that all meet the criteria that I set, I want the result to be 1 (unique entry). So I'm looking for a way (like with Sumproduct) to set multiple criteria for Col. A but also to identify how many unique entries satisfy those criteria since there are many duplicates. Does that help? "Sheeloo" wrote: You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique text that meets several criteria
Missed a - again :-(
Use =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),--(D2:D100=1)) instead of =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1)) which will give you a negative count... due to single - in -(D2:D100=1) "Sheeloo" wrote: Now I understand what you want.. You need to add a column to mark 1,2,3, against each entry in Col A - 1 against first occurence, two against the second and so on... In your example enter this in E2 =COUNTIF($A$2:A2,A2) and copy down... Starting at row 2 assuming headers in row 1 Now use SUMPRODUCT in F1 like this =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1)) "blswes" wrote: Here would be a basic example: Col. A Col. B Col. C Col. D Tree 40 Yes 1 Tree 45 Yes 1 Tree 45 Yes 1 Leaf 43 Yes 1 Leaf 44 Yes 1 What I'm looking for is unique entries that satisfy my criteria. So if I want unique Col. A entries that are 35 (Col. B), that have "Yes" (Col. C), and that equal 1 (Col. D), the answer is 2. But if I'm unable to get unique Col. A entries, then the answer with Sumproduct would be 5, which is not what I'm seeking. Does that help? Thanks in advance. "Sheeloo" wrote: Not sure what you want... Pl. provide sample data. "blswes" wrote: Hmmmm, doesn't seem to be returning the results I want. If I have 14 text entries of "House" that all meet the criteria that I set, I want the result to be 1 (unique entry). So I'm looking for a way (like with Sumproduct) to set multiple criteria for Col. A but also to identify how many unique entries satisfy those criteria since there are many duplicates. Does that help? "Sheeloo" wrote: You can use something like this in E2 =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. You can add more terms for other columns... "blswes" wrote: I am looking to count the number of unique text cells in Col. A that meet several criteria across Col. B-F. And I'll want to do this calculation numerous times with changing criteria (to fill out a large table). Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique entries with criteria | Excel Discussion (Misc queries) | |||
Counting unique values with criteria | Excel Worksheet Functions | |||
Counting Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Items with Multiple Criteria | Excel Worksheet Functions | |||
Counting Unique Values Given Criteria | Excel Worksheet Functions |