Put 1 as value for every unique set of values appears with multiple columns as criteria

Z

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
Hi, everyone. So, I am working on multiple columns of data that needs to be a part of a formula. Let's say I have columns A to E - each of the columns correspond to a certain type of data. Now, we have column F where we need the formula to be added so that each cell for each row shows the value "1" counting each time a unique set of values appear for columns A to E. Here's how I want it to look like:

Code: 
[TABLE="width: 471"]
<tbody>[TR]
[TD]Building Name[/TD]
[TD]Address[/TD]
[TD]State[/TD]
[TD]Status[/TD]
[TD]Property Code[/TD]
[TD]Count of Unique Values[/TD]
[/TR]
[TR]
[TD]Ashford[/TD]
[TD]Address 1[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]AS345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alpine[/TD]
[TD]Address 2[/TD]
[TD]VIC[/TD]
[TD]Closed[/TD]
[TD]AL879[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hampshire[/TD]
[TD]Address 3[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AB123[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Blackwood[/TD]
[TD]Address 4[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BL654[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Lytton[/TD]
[TD]Address 5[/TD]
[TD]VIC[/TD]
[TD]Active[/TD]
[TD]LY430[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]PE100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD]Address 7[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BR879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Redbank[/TD]
[TD]Address 8[/TD]
[TD]VIC[/TD]
[TD]Closed[/TD]
[TD]RE879[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Arcadia[/TD]
[TD]Address 9[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AR530[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]PE879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Ashford[/TD]
[TD]Address 1[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AS301[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alpine[/TD]
[TD]Address 2[/TD]
[TD]VIC[/TD]
[TD]Active[/TD]
[TD]AL879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Hampshire[/TD]
[TD]Address 3[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AB123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blackwood[/TD]
[TD]Address 4[/TD]
[TD]NSW[/TD]
[TD]Closed[/TD]
[TD]BL564[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lytton[/TD]
[TD]Address 5[/TD]
[TD]QLD[/TD]
[TD]Closed[/TD]
[TD]LY430[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]NSW[/TD]
[TD]Closed[/TD]
[TD]PE100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD]Address 7[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BR879[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, basically, I want the value "1" to appear in column F (Count of Unique Values) whenever status is "Active". I need the formula to disregard counting the values in different rows as a unique set of values if the status is "Closed". Cell value should be blank if status is closed.

I tried the formula below but it still counts the sets of values in a row even if the Status is "Closed":

Code: 
=IF(SUMPRODUCT(($D$2:$D2="Active")*($C$2:$C2=C2)*($A$2:$A2=A2)*($B$2:$B2=B2))>1,"",1)

Thanks!
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Hi, Aladin. Those 1s are desired values. I need them so when I "pivot" the table I can manipulate the data and provide me with the "count" of property codes (Column E) of buildings (Column A) in specific addresses (Column B - since buildings can have the same names but are located in different places), that are available in a certain state(Column C), and that are active (Column D).

Aladin Akyurek said:
Are those 1's desired values or the calculations of your own formula?
Click to expand...
 
Last edited:
Upvote 0
Zedrick13 said:
Hi, Aladin. Those 1s are desired values. I need them so when I "pivot" the table I can manipulate the data and provide me with the "count" of property codes (Column E) of buildings (Column A) in specific addresses (Column B - since buildings can have the same names but are located in different places), that are available in a certain state(Column C), and that are active (Column D).
Click to expand...

If so, why is the last occurrence of Lytton with Closed is 1?
 
Upvote 0
Ah, I see. You may ignore that. Nevertheless, the goal is still the same. Sorry about the mix up.

Try this instead:

Code: 
[TABLE="width: 471"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Building Name[/TD]
[TD]Address[/TD]
[TD]State[/TD]
[TD]Status[/TD]
[TD]Property Code[/TD]
[TD]Count of Unique Values[/TD]
[/TR]
[TR]
[TD]Ashford[/TD]
[TD]Address 1[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]AS345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alpine[/TD]
[TD]Address 2[/TD]
[TD]VIC[/TD]
[TD]Closed[/TD]
[TD]AL879[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hampshire[/TD]
[TD]Address 3[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AB123[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Blackwood[/TD]
[TD]Address 4[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BL654[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Lytton[/TD]
[TD]Address 5[/TD]
[TD]VIC[/TD]
[TD]Active[/TD]
[TD]LY430[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]PE100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD]Address 7[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BR879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Redbank[/TD]
[TD]Address 8[/TD]
[TD]VIC[/TD]
[TD]Closed[/TD]
[TD]RE879[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Arcadia[/TD]
[TD]Address 9[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AR530[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]PE879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Ashford[/TD]
[TD]Address 1[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AS301[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Alpine[/TD]
[TD]Address 11[/TD]
[TD]VIC[/TD]
[TD]Active[/TD]
[TD]AL879[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Hampshire[/TD]
[TD]Address 3[/TD]
[TD]NSW[/TD]
[TD]Active[/TD]
[TD]AB123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blackwood[/TD]
[TD]Address 4[/TD]
[TD]NSW[/TD]
[TD]Closed[/TD]
[TD]BL564[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lytton[/TD]
[TD]Address 5[/TD]
[TD]QLD[/TD]
[TD]Closed[/TD]
[TD]LY430[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD]Address 6[/TD]
[TD]NSW[/TD]
[TD]Closed[/TD]
[TD]PE100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD]Address 7[/TD]
[TD]QLD[/TD]
[TD]Active[/TD]
[TD]BR879[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Aladin Akyurek said:
If so, why is the last occurrence of Lytton with Closed is 1?
Click to expand...
 
Last edited:
Upvote 0
What version of Excel are you using?

Edit: Never mind the above question. Consider using a helper column to keep the formulas simpler and calculation overheads lower.
Both formulas copied down. The helper column, G, could be hidden once populated.

Excel Workbook
ABCDEFG
1Building NameAddressStateStatusProperty CodeCount of Unique Values
2AshfordAddress 1QLDActiveAS3451Ashford|Address 1|QLD|AS345
3AlpineAddress 2VICClosedAL879Alpine|Address 2|VIC|AL879
4HampshireAddress 3NSWActiveAB1231Hampshire|Address 3|NSW|AB123
5BlackwoodAddress 4QLDActiveBL6541Blackwood|Address 4|QLD|BL654
6LyttonAddress 5VICActiveLY4301Lytton|Address 5|VIC|LY430
7PerthAddress 6NSWActivePE1001Perth|Address 6|NSW|PE100
8BrisbaneAddress 7QLDActiveBR8791Brisbane|Address 7|QLD|BR879
9RedbankAddress 8VICClosedRE879Redbank|Address 8|VIC|RE879
10ArcadiaAddress 9NSWActiveAR5301Arcadia|Address 9|NSW|AR530
11PerthAddress 6QLDActivePE8791Perth|Address 6|QLD|PE879
12AshfordAddress 1NSWActiveAS3011Ashford|Address 1|NSW|AS301
13AlpineAddress 11VICActiveAL8791Alpine|Address 11|VIC|AL879
14HampshireAddress 3NSWActiveAB123Hampshire|Address 3|NSW|AB123
15BlackwoodAddress 4NSWClosedBL564Blackwood|Address 4|NSW|BL564
Mark 1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
404
zero269
Z
P
  • Question Question
Formula to count unique values based on multiple criteria, including 1 column where at least 3 of 5 criteria should be found?
2
Replies
12
Views
3K
jasonb75
J
S
  • Question Question
Counting Unique Values in Filtered List That Meet External Criteria
Replies
8
Views
3K
Fluff
Fluff
B
  • Question Question
Looking for Unique values in one column when match with criteria from two others using array formula
Replies
0
Views
455
BubbaUnited
B
S
  • Question Question
Countif Unique Text Value only based on multiple criteria
Replies
3
Views
939
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top