CODEWITHSUNDEEP
excel
user127815
user127815

Reputation: 141

Count Unique Values with Multiple Criteria

I've been struggling with this for hours now, and I just can't figure out how to add a second criteria to my formula.

I have this table:

device # date plan used

5     12/2/2017    A

8     12/3/2017    A

9     12/4/2017    B

56    12/2/2017    B

588   12/5/2017    C

56    12/2/2017    C

I am trying to get the number of unique devices, that are on plan A and have the date 12/2/2017. Here is the formula I have now. 

=SUM(IF(FREQUENCY(IF(D2:D116005=G2,B2:B116005),IF(D2:D116005=G2,B2:B116005))>0,1))

I have successfully counted the unique devices with the criteria of being on 12/2/2017, but I just cannot figure out how to add additional criteria (i.e. on plan A). The G2 cell is where I am storing the date 12/2/2017 for comparison.

I have tried using IF(AND(.. but I can't seem to get it to work.

Could someone help me with this? Let me know if more information is needed.

Thank you, Staci

Upvotes: 1

Views: 135

Answers (4)

jeffreyweir
jeffreyweir

Reputation: 4834

No formulas required: Simply whip up a PivotTable of the data, put Device and Date in the Rows field, and drag Plan to the Values area, which will automatically be given the COUNT treatment by the PivotTable because it's a text field.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46401

Assuming plans in column F try this revised array formula:

=SUM(IF(FREQUENCY(IF(D2:D116005=G2,IF(F2:F116005="A",B2:B116005)),B2:B116005),1))

confirmed with CTRL+SHIFT+ENTER

Notice that it's not necessary to repeat the IF functions I the "bins array" argument of the FREQUENCY function

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

You can use multiplication to return a 1 (equivalent to TRUE) if both match:

IF((Table1[date]=TargetDate)*(Table1[plan_used]=TargetPlan),Table1[device_'#],"")

Putting it all together:

=SUM(IF(FREQUENCY(IF((Table1[date]=TargetDate)*(Table1[plan_used]=TargetPlan),Table1[device_'#],""),IF((Table1[date]=TargetDate)*(Table1[plan_used]=TargetPlan),Table1[device_'#],""))>0,1))

Remember this is an array formula so must be confirmed by holding down ctrl + shift while hitting enter

I used structured references and named cells, but you should be able to transfer to your cell references.

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

in column D, starting in cell D2, put this formula:

=if(and(B2=date(2017,12,2),c2="A",countif($A$2:A2,A2)=1),1,0)

then just sum that column to get your answer.

Upvotes: 0

Related Questions