Count of unique rows matching a criteria

[Manohar Kurapati]

Hi
I am working on an excel challenge in my office to find out the unique users doing a payment at a store. In the attached sample excel I am trying to find out using and Excel formula how many unique customers did a payment between two dates in a particular store id. For example for store id - 2453 number of unique customers count is 12.

Please can you anyone provide me bit of guidance on how to achieve this.

Thank you,
Manohar

 

[Chihiro]

In L7:
=SUM(IF(FREQUENCY(IF($A$2:$A$35="PAYMENT",IF($D$2:$D$35=K7,MATCH($E$2:$E$35,$E$2:$E$35,0))),IF($A$2:$A$35="PAYMENT",IF($D$2:$D$35=K7,MATCH($E$2:$E$35,$E$2:$E$35,0))))>0,1))

Enter as array (CSE). Copy down.

 

[Chihiro]

Just realized you asked for between 2 dates. For which dates are you looking for this?

 

[Luke M]

Hi Manohar,

Check this out.

I got the idea from Vijay's post here:
http://chandoo.org/forum/threads/combine-countifs-and-unique-function.2871/#post-13744

 

[Chihiro]

Alternate method.
In L7:
=SUM(IF(("PAYMENT"=$A$2:$A$35)*($D$2:$D$35=K7)*($C$2:$C$35>=$K$3)*($C$2:$C$35<INT($L$3+1)),1/COUNTIFS($A$2:$A$35,"PAYMENT",$E$2:$E$35,$E$2:$E$35,$D$2:$D$35,K7,$C$2:$C$35,">="&$K$3,$C$2:$C$35,"<"&INT($L$3))),0)

Enter as array(CSE)

EDIT: Changed INT($L$3) to INT($L$3+1)

 

[NARAYANK991]

Hi ,

This is an easy to remember method :

=SUM(IF($A$2:$A$35="PAYMENT",IF($D$2:$D$35=K8,1/COUNTIFS($A$2:$A$35,"PAYMENT",$D$2:$D$35,K8,$E$2:$E$35,$E$2:$E$35))))

entered as an array formula , using CTRL SHIFT ENTER.

1. The IF function contains the conditions which need to be fulfilled before the unique counting is done i.e.

The data in column A needs to be PAYMENT.

The store IDs in column D need to match the entered store ID in cell K8.

2. The COUNTIFS function contains these same conditions , of which there can be as many as you wish ; the portion highlighted in RED gives the range over which the unique counting is to be done.

Try it out the next time you have a similar requirement.

Narayan

 

[Deepak]

This might be useful too.

=SUMPRODUCT(--((MATCH($E$2:$E$35,$E$2:$E$35,0)*($A$2:$A$35="PAYMENT")*($D$2:$D$35=$K7))=ROW($A$2:$A$35)-1))

for more criteria just add like as bold part.

 

[srinidhi]

HI,

If you don't want lengthy formula, use the pivot table & then count formula.
Store id User ids Count of User ids
In Pivot table.
click Field Settings.Click the Layout & Print tab, and then select the Repeat item labels check box.
=COUNTIFS(A:A,E2,C:C,"=1")
Attached the excel, refer sheet 1. Hope this helps.

 

[Manohar Kurapati]

Thanks everyone for the quick suggestions. Very much helpful and I have learnt may new ways to use Sumproduct, Match and Frequency formulas. However, I forgot to mention that the data I am working on have 191098 rows. So when I changed the formulas to use the entire row $A:$A non of the above suggestions worked.

The only option which worked is Pivot table option by Srinidhi. Thanks Srinidhi for the suggestion. But by using the Pivot table option the file size has doubled.
Are there any other options you can suggest to keep the file size minimum? The file is already saved as .xlsb and before the Pivot the size was ~5MB after Pivot table it has increased to ~10MB.

Regards,
Manohar

 

[NARAYANK991]

Hi Manohar ,

See if this helps :

http://datapigtechnologies.com/blog/index.php/cut-the-size-of-your-pivot-table-workbooks-in-half/

Narayan

 

[Chihiro]

Another solution is to keep Raw data on separate sheet, use advanced filter operation to query data and perform your operation.

However, you will need a bit of VBA coding. See below for basic example.

Sub filterOther()
Dim wbCopyTo As Workbook
Set wbCopyTo = ThisWorkbook
 
    Workbooks.Open ("full path to raw data file here")
    wbCopyTo.Activate
    Workbooks("Raw_Data.xlsx").Sheets("Data").Range("A1:G53569"). _
        AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Sheet1!Criteria" _
        ), CopyToRange:=Range("A8:G8"), Unique:=False
    Application.DisplayAlerts = False
    Workbooks("Raw_Data.xlsx").Close
    Application.DisplayAlerts = True
End Sub

Set up your sheet where you want the desired data copied with exactly same Column Header as Raw Data table.