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

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

hi benishiryo -YOU ARE AN ABSOLUTE FORUM GURU, THANK YOU SO MUCH =D! it applied the array formula and dragged it down and my total sum now equals what I wanted. Thank you so much.

Hi benushiryo. I have a similar problem, this one regarding averageIFs function. I've tried and tried but cannot work it out.


What I need to do with this one is combine an AverageIFs function with one that find duplicate values in Column E.

I have an average if function that works:

=IFERROR(AVERAGEIFS($Z$2:$Z$19,$N$2:$N$19,">="&$B28,$N$2:$N$19,"<="&$C28,$B$2:$B$19,"="&F$22)," ")

However this one is calculating averages for ALL activities (z2:z19) and not considering the duplicate values in column E

I have worked out that there are actually 13 activities, not 18, through using the following:

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

What I need then is for my AVERAGEIFS function to understand the FREQUENCY/MATCH command – ie to average only the 13 activities, not the 18 which are duplicates.

Any ideas??

Spreadsheet attached ☺!!!!!!

thank you in advance; I can post to general forum if I don't hear from you!