Function/Formula in Excel to count Unique values matching criteria

[mando415]

Hi - I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.

I am banging my head over this and would appreciate any advise.

Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return

Thank you

 

[mjweber1]

I've been reading through this thread and others trying to find a solution but I've finally realized I need the help of an expert. My data is such:

Year:Customer#:OrderType
2011:CA1:Mail
2011:CA1:Store
2011:CA1:Online
2011:CA1:Mail
2011:CA1:Store
2011:CA3:Store
2011:CA3:Online
2011:CA4:Store
2011:CA2:Online
2011:CA2:Mail
2012:CA2:Mail
2012:CA2:Online
2012:CA3:Online
2012:CA1:Online
2012:CA1:Mail
2012:CA3:Store

I need to be able to do four things:

1. Count the unique number of customers for a given year (2011) for a given order type (Mail). (expected result = 2)
2. Count the unique number of customers for a given year (2011) for a multiple order types (e.g. Online or Mail). (expected result = 3)
3. Count the unique number of customers for all years for a given order type (Store). (expected result = 3)
4. Count the unique number of customers for all years for a multiple order types (e.g. Online or Mail). (expected result = 3)

Also to note that I have about 65000 rows in my data set.

Your help is greatly appreciated.

TIA!
Martin

 

[Domenic]

Let's assume that A2:C65000 contains the data:

1) Let E2 contain 2011, and F2 contain Mail, then try...

H2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A65000=E2,IF(C2:C65000=F2,IF(LEN(B2:B65000)>0,MATCH(B2:B65000,B2:B65000,0)))),ROW(B2:B65000)-ROW(B2)+1)>0,1))

2) Let E3 contain 2011, F3 contain Online, and G3 contain Mail, then try...

H3, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A65000=E3,IF(ISNUMBER(MATCH(C2:C65000,F3:G3,0)),IF(LEN(B2:B65000)>0,MATCH(B2:B65000,B2:B65000,0)))),ROW(B2:B65000)-ROW(B2)+1)>0,1))

3) Let F4 contain Store, then try...

H4, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(C2:C65000=F4,IF(LEN(B2:B65000)>0,MATCH(B2:B65000,B2:B65000,0))),ROW(B2:B65000)-ROW(B2)+1)>0,1))

4) Let F5 contain Online, and G5 contain Mail, then try...

H5, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(C2:C65000,F5:G5,0)),IF(LEN(B2:B65000)>0,MATCH(B2:B65000,B2:B65000,0))),ROW(B2:B65000)-ROW(B2)+1)>0,1))

The results should be as follows (starting at A2) ...

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]2011[/TD]
[TD="class: xl63, width: 64"]CA1[/TD]
[TD="class: xl63, width: 64"]Mail[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]2011[/TD]
[TD="class: xl63, width: 64"]Mail[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA3[/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA3[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA4[/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA2[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2011[/TD]
[TD="class: xl63"]CA2[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA2[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA2[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA3[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Online[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA1[/TD]
[TD="class: xl63"]Mail[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2012[/TD]
[TD="class: xl63"]CA3[/TD]
[TD="class: xl63"]Store[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps!

 

[Aladin Akyurek]

@mjweber1

Let A:C of Sheet1 house the data.

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Sheet1!$B$2:$B$17)-ROW(Sheet1!$B$2)+1

1. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF($A$2:$A$17=2011,
    IF($C$2:$C$17="mail",MATCH($B$2:$B$17,$B$2:$B$17,0)))),Ivec),1))

2. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF($A$2:$A$17=2011,
    IF(ISNUMBER(MATCH($C$2:$C$17,{"online","mail"},0)),
    MATCH($B$2:$B$17,$B$2:$B$17,0)))),Ivec),1))

3.Control+shift+enter, not just enter:

 =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF($C$2:$C$17="store",
    MATCH($B$2:$B$17,$B$2:$B$17,0))),Ivec),1))

4.Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),
    IF(ISNUMBER(MATCH($C$2:$C$17,{"online","mail"},0)),
     MATCH($B$2:$B$17,$B$2:$B$17,0))),Ivec),1))

Such calculations can be expensive over large ranges (65000 rows). If this is some kind of upper limit, you could opt instead for dynamic ranges. How this is done is shown in one of the replies in this very thread.

 

[mjweber1]

Domenic,

That worked very well, thank you! With so much data though is there now way around using an array formula? Each cell takes about 5 minutes to calculate and i have over a dozen calculations to make.

Thanks again!!!

 

[Domenic]

Actually, Aladin who offered you a solution as well commented about this very issue. Basically, if the actual size of the ranges are smaller, and the size will increase in time, convert your data into a Table and refer to the actual ranges in your formula. The ranges will automatically adjust as data is added/removed. An alternative to converting the data into a Table is to use dynamic named ranges.