COUNTIF - Avoiding duplicates

[arthuro2021]

Hi, I'm currently trying to sum a dataset that has duplicates due to a upstream data issue. I cannot get the 'unique' function to work for me. In the attached screen shot I can show you the current formula I have applied in cell H9. The problem with my result is that I'm summing the results twice. How do I add in a criteria to only sum up the items shown in column D once. E.g. per my current formula structure the item in column D '278. - Ent 671 Bank (WBC)' is being included in my sum formula twice for the category in column C 'WIB' as it appears on two lines. I need the formula to only report the value once against category 'WIB' instead of twice because its on two lines. How can I add this criteria into my formula?

 

[Peter_SSs]

I would recommend not using whole column references in a formula like that. For example, you are asking Excel to check over a million cells in column C in case they contain the H7 value. Just choose a range that will surely be big enough to cover any data that you are likely to have.

See if something like this does what you want.

=SUM(UNIQUE(FILTER(D8:E1000,(B8:B1000=H6)*ISNUMBER(SEARCH(H7,C8:C1000)),0)))

 

[arthuro2021]

Hi, the sum formula worked great. thankyou. I also need to count the number of unique records and I tried adjusting the formula and ranges but the results are not correct. Would be be able to advise what I have done wrong from the attached screenshot?

 

[Peter_SSs]

Bit hard to tell for sure from an image as we cannot test with that but since column D contains text, not numbers, COUNT would not work on that column.
Try replacing the COUNT function with the ROWS function.

If that does not work, can you copy/paste values from that worksheet to the same cells in a blank worksheet and post a mini-sheet here using XL2BB and also tell us what answer you expect and why?

BTW, good to see that you have restricted the row ranges in your attempted formula.

 

[arthuro2021]

Hi, tried the function ROWS and that worked for the rows with a value to count but returned a 1 for items that should be 0. Strange.
Have uploaded the spreadsheet with my attempt as requested.

Count Formula Issue.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Legal Entity	(Multiple Items)
2	Line of Business	(All)
3	Counterparty Line of Business	(All)
4	Reporting Classification	>= 1M AND <10M
5
6	Sum of Actual YTD								Total Sum	Total Count
7	Account Classification	FinancialStatement_IG	Join LOB	Tag	Tag Actual Balance YTD	Total			Balance Sheet _>= 1M AND <10M	Balance Sheet _>= 1M AND <10M	Expected Result
8	Balance Sheet	Balance Sheet _>= 1M AND <10M	NZ BankingNZ Banking	4301. - 582 vs588 entity	1,283,575	1,283,575		Specialist Businesses	- 7,718,151	4	4	TRUE
9		Balance Sheet _>= 1M AND <10M	Specialist BusinessesWIB	278. - Ent 671 Bank (WBC)	- 7,444,953	15,471,831		Business Bank	-	1	0	FALSE
10		Balance Sheet _>= 1M AND <10M	Specialist BusinessesWIB	3488. - SGML Bank account - (SGB)	5,630,963	27,641,552		Consolidation	-	1	0	FALSE
11		Balance Sheet _>= 1M AND <10M	Specialist BusinessesWIB	3618. - AUD Nostro/Vostro between Pacific Banking and WBC Sydney	- 3,046,616	8,002,196		Consumer Bank	-	1	0	FALSE
12		Balance Sheet _>= 1M AND <10M	Specialist BusinessesWIB	374. - WLIS Cash balances	- 2,857,545	17,406,212		Group Business Unit	-	1	0	FALSE
13		Balance Sheet _>= 1M AND <10M	Treasury & Structured FinanceWIB	3281. - Offshore Nostro accounts held by NY	- 6,656,414	613,292		Chief Operating Office	-	1	0	FALSE
14		Balance Sheet _>= 1M AND <10M	WIBNZ Banking	25. - Nostro/Vostro between NZ Wellington (Vostro) and WBC Sydney (Nostro). Variance relates to timing difference.	- 1,492,257	40,618,625		Treasury & Structured Finance	- 6,656,414	1	1	TRUE
15		Balance Sheet _>= 1M AND <10M	WIBNZ Banking	3940. - Calypso v Midas NZD B/S	8,376,175	50,258,196		WIB	- 7,490,646	7	7	TRUE
16		Balance Sheet _>= 1M AND <10M	WIBSpecialist Businesses	278. - Ent 671 Bank (WBC)	- 7,444,953	- 22,916,784		NZ Banking	8,167,494	3	3	TRUE
17		Balance Sheet _>= 1M AND <10M	WIBSpecialist Businesses	3488. - SGML Bank account - (SGB)	5,630,963	- 22,010,589
18		Balance Sheet _>= 1M AND <10M	WIBSpecialist Businesses	3618. - AUD Nostro/Vostro between Pacific Banking and WBC Sydney	- 3,046,616	- 11,048,812
19		Balance Sheet _>= 1M AND <10M	WIBSpecialist Businesses	374. - WLIS Cash balances	- 2,857,545	- 20,263,757		=COUNT(UNIQUE(FILTER(D$8:D$1000,(B$8:B$1000=$J$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),0)))
20		Balance Sheet _>= 1M AND <10M	WIBTreasury & Structured Finance	3281. - Offshore Nostro accounts held by NY	- 6,656,414	- 7,269,706		=ROWS(UNIQUE(FILTER(D$8:D$1000,(B$8:B$1000=$J$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),0)))
21		Balance Sheet _>= 1M AND <10M	WIBWIB	25. - Nostro/Vostro between NZ Wellington (Vostro) and WBC Sydney (Nostro). Variance relates to timing difference.	- 1,492,257	- 42,110,882
22		Balance Sheet _>= 1M AND <10M	WIBWIB	3940. - Calypso v Midas NZD B/S	8,376,175	- 41,882,021
23	Profit and Loss	Profit and Loss_>= 1M AND <10M	NZ BankingNZ Banking	467. - NZ Incorporations	- 1,318,679	- 1,318,679
24		Profit and Loss_>= 1M AND <10M	WIBWIB	4711. - SYD and WLG translation diffs 213017 vs 205097	1,898,075	1,898,075
25	Grand Total					- 5,627,675
Sheet1 IG

Cell Formulas
Range	Formula
I8:I16	I8	=SUM(UNIQUE(FILTER(D$8:E$1000,(B$8:B$1000=$I$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),0)))
J8:J16	J8	=ROWS(UNIQUE(FILTER(D$8:D$1000,(B$8:B$1000=$J$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),0)))
L8:L16	L8	=K8=J8

 

[Peter_SSs]

OK, I wasn't sure if you would have any zero counts. Try this instead

=IFNA(ROWS(UNIQUE(FILTER(D$8:D$1000,(B$8:B$1000=$J$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),NA()))),0)

 

[arthuro2021]

OK, I wasn't sure if you would have any zero counts. Try this instead

=IFNA(ROWS(UNIQUE(FILTER(D$8:D$1000,(B$8:B$1000=$J$7)*ISNUMBER(SEARCH(H8,C$8:C$1000)),NA()))),0)

yes it worked, fantastic stuff Peter_SSs. Thankyou and have a great weekend!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.