Need my SUM IF Frequency Match formula to combine with cell references - ideas?

[acillatem]

Hi all

OK this shouldn’t be too hard but I am having trouble working it out.

Basically I have a long list of countries that give $ to another country. The spreadsheet includes only 3 different donor countries in column B.

I have a working formula that identifies the grand total of duplicate activities :

=SUM(IF(FREQUENCY(MATCH(E2:E61,E2:E61,0),MATCH(E2:E61,E2:E61,0))>0,1))

What I now want to do is add something to that formula so that it uses cell references for cells H5:H7 – that is, the name of the donor countries

I want my formula to use this sumifrequencymatch to identify duplicates, but at the same time use the cell reference to a specific donor

Array formula perhaps?!

My life will be much easier if solved, as I am dealing with 20+countries and hundreds of duplicate activities!

thanks awesome forum users =D!

Multiple Donors count duplicates.xlsx

[FDibbins]

Sometimes its better to use helper columns, to simplify formulas. See is this is something you can work with?

In F2, use this (the table should copy it down for you)
=IF(COUNTIF($E$2:E2,E2)>1,"",E2)

You can then use a simple =COUNTA(E2:E61) (I get 60 though, not 55 like you did?)
Then for the different countries...
=COUNTIFS($E$2:$E$61,"<>",$B$2:$B$61,H5)
copied down

[Tony Valko]

I'm not sure what you're wanting to do with this.

Here's my best guess...

This array formula** entered in I5 and copied down to I7:

=SUM(IF(FREQUENCY(IF(B$2:B$61=H5,MATCH(E$2:E$61,E$2:E$61,0)),ROW(E$2:E$61)-ROW(E$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[newdoverman]

I hope that this helps. I have counted the activities of each country, counted the number of duplicated activities for each country, counted the number of unique activities for each country and used Conditional Formatting to identify the duplicated activities in the table.

[acillatem]

Hi all - the wonders of excel!

all you you have solved my problem. This is excellent. I have given you all awesome reputation points

this makes everything a lot easier - I have 10 years worth of donor data to get through and being able to easily cell reference to count activities makes life soo much easier!!!

thanks again!

[Tony Valko]

You're welcome. We appreciate the feedback!

[FDibbins]

Happy to help and thanks for the feedback

[newdoverman]

Thanks for the feedback. I'm glad that you found something that works for you.