How to count unique distinct values from a column where values in other column exists ?

**Daniel Tou** · 03-16-2017, 06:30 AM

Hi ,

I have the below table and i want a formula who can do this :

count unique distinct values from column A ( id terminal ) where column B ( valoarea totala tranzactii ) > 0 + count unique distinct values from column A where column B > 0 and column E ( Grila Comerciant ) not null + count unique distinct values from column A where column D ( Denumire MCC ) = "Electric parts/Equipment" and column F ( Comision Administrare Lunar ) > 0
divide to
count unique distinct values from column A ( id terminal ) where column G ( Status Cont ) = "Activ"

For know i have this formula to count uniques distict values of column A ( ID Terminal ) : = SUMPRODUCT((K2:K1000<>"")/COUNTIF(K2:K1000,K2:K1000&""))

Im stuck , please help pe solve this.

Id Terminal Valoare Total Tranzactii Tip Terminal Denumire Mcc Grila Comerciant Comision Administrare Lunar Status Cont
31095000 2,542.77 POS Electric parts/Equipment NU 25.00 Activ
03115600 144.95 POS Grocery stores,supermarkets NU 39.00 Activ
31386000 0.00 POS Hotels,Motels,Resorts-Lodgin NU 0.00 Activ
31299000 11,870.40 POS Jewelry stores - watches NU 10.00 Closed
31023000 5,970.72 POS Jewelry stores - watches NU 10.00 Closed
31132000 3,350.00 POS Travel Agencies and Tour Op NU 0.00 Closed
31133000 0.00 POS Travel Agencies and Tour Op NU 0.00 Closed

test.jpg

**loginjmor** · 03-16-2017, 10:28 AM

Hi -

First, it would be helpful if you upload a sample of your actual spreadsheet (not an image). From the reply window, pick the Go Advanced button at the lower right. Scroll down to the hyperlink Manage Attachments. Then, browse to a copy of your file that has any sensitive information removed, and select Upload.

Second, you will need a list of unique Id Terminals. Maybe you already have that, I don't know. From you picture, all of the Id Terminals are unique. There are no repeats I can see.

Third, it's a simple matter of using SUMPRODUCT, or COUNTIFS, whichever you are more comfortable using. I personally prefer SUMPRODUCT.

If you provide a sample spreadsheet, I can help you some more.

**Daniel Tou** · 03-16-2017, 01:25 PM

Thank you.

**Daniel Tou** · 03-17-2017, 01:57 PM

So , i corrected what i need the formula to do according to the atach . Please help me with this !

count unique distinct values from column D ( id terminal ) where column E ( valoarea totala tranzactii ) > 0 + count unique distinct values from column D(id terminal ) where column E > 0 and column C ( Grila Comerciant ) not null + count unique distinct values from column D where column B ( Denumire MCC ) = "Hotels,Motels,Resorts-Lodgin" and column G ( Comision Administrare Lunar ) > 0
divide to
count unique distinct values from column D ( id terminal ) where column F ( Status Cont ) = "Activ"
minus count unique distinct values from column A(id terminal ) where columna A (Tip Client )='PFA'

**loginjmor** · 03-17-2017, 02:28 PM

Hi -

I am not sure of all the math you are wanting, so I just put together the searches in each column of the attached spreadsheet and you can add or subtract or divide columns as you choose.

The first thing was to develop a list of unique Terminal Id's. I used this array formula to do that:

=INDEX($D$2:$D$49, MATCH(0, COUNTIF($I$1:I1, $D$2:$D$49), 0))

Since this is an array formula, from the formula editing window you must press Ctrl-Shift-Enter to engage the array functionality. If you did it correctly, Excel will add curly braces {} to the formula.

Basically, MATCH finds the first instance where COUNTIF doesn't find a repeated number in column I, compared to Column D. Basically, this returns the row number to INDEX, which pulls the next, unique ID number.

Once you have the list of ID numbers, then it's just a matter of using SUMPRODUCT to match all of your various conditions for each ID number.

For example, the first case where we have a unique Terminal ID (Column I) AND Column E > 0 looks like this:

=SUMPRODUCT(($D$2:$D$49=$I2)*($E$2:$E$49>0))

Hope this helps.

**Daniel Tou** · 03-17-2017, 05:01 PM

Thank you for the solutions but i need to solve this in one formula.

For example i have the below formula that counts the distinct values in D2 , and it`s what i want.

=SUMPRODUCT((D2:D1000<>"")/COUNTIF(D2:D1000,D2:D1000&""))

But i don`t know how to continue to count the distinct values from column D with different conditions in from other columns.
For example i tried : =SUMPRODUCT((D2:D1000<>"")/COUNTIF(D2:D1000,D2:D1000&"")*($E$2:$E$49>0)) but does not work

Can u help me ?

**loginjmor** · 03-17-2017, 05:09 PM

Hi -

One problem with your formula is all the arrays have to be the same size. So when you multiply by E2:E49, that's not the same number of cells as D2:D1000. So that will generate an error. Try changing the last part to E2:E1000 and see if that helps.

**daddylonglegs** · 03-17-2017, 05:21 PM

Try this formula, array entered

=SUM(IFERROR((E2:E1000>0)/(D2:D1000<>"")/COUNTIFS(D2:D1000,D2:D1000,E2:E1000,">0"),0))

You can add other conditions but they need to be in both parts of the formula, e.g. if you want the above and also column Z to equal "x" then you can amend like this:

=SUM(IFERROR((E2:E1000>0)/(D2:D1000<>"")/(Z2:Z1000="x")/COUNTIFS(D2:D1000,D2:D1000,E2:E1000,">0" ,Z2:Z1000,"x"),0))

**Daniel Tou** · 03-17-2017, 05:23 PM

It worked.Thank you for all your support.I will ask you more if i encounter issues.For now i`m happy that i can continue to solve this.

**loginjmor** · 03-17-2017, 05:25 PM

Thanks for the rep! Don't forget to mark your thread as SOLVED per the instructions at the bottom of this post.

**Daniel Tou** · 03-18-2017, 08:01 PM

Is there a better alternative than SUMPRODUCT , a faster one ? The formula below is taking to much time to process or is crashing.On 1000 rows is relatively ok,

=((SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($O$2:$O$60000>0)))+(SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*(O2:O60000=0)*($J$2:$J$60000<>"NU")))+(SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($I$2:$I$60000="TAX PAYMENTS")*(AN2:AN60000>0))))/((SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($AL$2:$AL$60000="Activ")))-(SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($AL$2:$AL$60000="Activ")*(G2:G60000="PJ_CLOSED")))-(SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($AL$2:$AL$60000="Activ")*(AP2:AP60000<>"")))-(SUMPRODUCT((K2:K60000<>"")/COUNTIF(K2:K60000,K2:K60000&"")*($AL$2:$AL$60000="Activ")*(AM2:AM60000<>""))))

**loginjmor** · 03-19-2017, 05:48 PM

Hi -

You can try using SUMIFS instead of SUMPRODUCT. It has a little less computational overhead and should improve speed.

The other thing you can do is instead of repeating the COUNTIF(K2:K60000,K2:K60000&"") over and over again in your formula, just do it once in a hidden cell somewhere and reference that into your formula. For example, if you put COUNTIF(K2:K60000,K2:K60000&"") in cell AZ1, and hid that column, then your formula would look like this:

=((SUMPRODUCT((K2:K60000<>"")/AZ1)*($O$2:$O$60000>0)))+(SUMPRODUCT............ and so on.

That alone will cut 6 or 7 iterations of the same formula and should improve speed quite a bit.

Hope this helps.

How to count unique distinct values from a column where values in other column exists ?

LinkBack

Thread Tools

Rate This Thread

Display

How to count unique distinct values from a column where values in other column exists ?

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Re: How to count unique distinct values from a column where values in other column exists

Thread Information

Users Browsing this Thread

Similar Threads

2-Column List of Unique Distinct COMBINED Values

Count unique values in one column based on conditions and unique values in another column

[SOLVED] Count Distinct Values in One Column Based on Value in A Different Column

[SOLVED] How to count distinct values in a column

Summing up values in a column corresponding to unique distinct values in another column

How to count distinct values in a column, categorized by values in another column.

return all distinct unique values to another sheet if it exists in the other worksheets.

Bookmarks

Bookmarks

Posting Permissions