Combining COUNTIFs with SUM IF FREQUENCY MATCH to work out a total

[acillatem]

Hi all =)

noob to the forum (but not to excel!)

I've tried to be clear but let me know if more detail is required! Attached spreadsheet shows the problem.

I am trying to combine a COUNTIFS function, which identifies a number of activities per sector, with a SUM IF Frequency MATCH formula, which identifies duplicate activities.

So I want this:

=COUNTIFS($N$2:$N$19,">="&$B30,$N$2:$N$19,"<="&$C30,$B$2:$B$19,"="&G$23)

To combine with this

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

So that I can get activity counts per sector (N column, have cell references to identify sectors) and then add them all up and get the right ‘total of activities (13, cell B22)

You can see in cell B21-B22 what the count is (18) and the real count excluding duplicates (13). The 13 is what I am after.

Then look at my sum in G53. This gives me 18, because the formulas above are not considering these duplicates. I need the formula above to have the combined countif and sumiffrequency to get the right total sum (13)

hope you can help !!!!

THANKS