how do i count the number of contracts associated with a gl?

[bigdan]

How do I find the number of unique contracts that are associated with a GL?

Please see sample data.

For ABC the answer should be 1. For DEF it should be 3. For GHI it should be 2.

I would've thought a pivot table would do the trick. It does not. In a pivot if I put GL in rows and Count of Contracts in Values, it gives me 3 for ABC rather than 1. So it's giving me the number of rows associated with ABC, as opposed to the number of UNIQUE rows.

Can anyone advise?

GL	Contract
ABC	123
ABC	123
ABC	123
DEF	411
DEF	511
DEF	611
GHI	559
GHI	459

 

[Fluff]

How about

=SUM(--(FREQUENCY(IF($A$2:$A$100=D2,$B$2:$B$100),$B$2:$B$100)>0))

This needs to be confirmed with Ctrl Shift Enter, rather than just enter

 

[Sektor]

PowerQuery
Assuming data is in A1:B9 range (it's a table called "DATA"):

let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GL", type text}, {"Contracts", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"GL"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

 

[bigdan]

How about

=SUM(--(FREQUENCY(IF($A$2:$A$100=D2,$B$2:$B$100),$B$2:$B$100)>0))

This needs to be confirmed with Ctrl Shift Enter, rather than just enter

Thanks! This kinda sorta works but not exactly. I made a small table of all the GLs in my worksheet, which was a list of 10. Next to each of them I used your formula to see how many unique contracts relate to them. The total number of unique contracts was given as 2166. But the total number of rows in the workbook is 2680. I would've expected your formula to give me a higher number, since one contract could be in 2 different GLs, not a lower number. So I looked at an individual GL level. With the first one, this formula gave me 243 contracts. I counted 246. So it's quite close. Not sure where the inaccuracy is coming from. But it is there unfortunately. In another example this formula gave me 122 contracts, I counted 127.

 

[bigdan]

PowerQuery
Assuming data is in A1:B9 range (it's a table called "DATA"):

let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GL", type text}, {"Contracts", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"GL"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

I'm sorry I'm not familiar with PowerQuery. I appreciate the help though!

 

[Fluff]

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jasonb75]

But it is there unfortunately. In another example this formula gave me 122 contracts, I counted 127.

I would hazard a guess that there is more likely a discrepancy in the data than a problem with the formula (as appears also to be the case in your previous thread).
See mini sheet below for 2 formula methods, note that there is a difference between the results of my formula in column E and @Fluff's formula in column F if there is a GL with an empty cell for contract.

Book1
	A	B	C	D	E	F
1	GL	Contract
2	ABC	123		ABC	1	1
3	ABC	123		DEF	3	3
4	ABC	123		GHI	2	2
5	DEF	411		JKL	0	1
6	DEF	511
7	DEF	611
8	GHI	559
9	GHI	459
10	GHI	459
11	JKL
Sheet4

Cell Formulas
Range	Formula
E2:E5	E2	=SUM(IFERROR(1/COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,$B$2:$B$100),))
F2:F5	F2	=SUM(--(FREQUENCY(IF($A$2:$A$100=D2,$B$2:$B$100),$B$2:$B$100)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.