Count Unique w/ Indirect Group Criteria

I want to do a modified version of what is detailed in this ExcelJet post:

https://exceljet.net/formula/count-u...-with-criteria

You can see my modification of this example in my attached spreadsheet. Basically, rather than filter by a specific project name as in the ExcelJet post, I want to filter by a specific project group. A project group can contain multiple different projects. When counting the unique name values for Group1, for example, it should include both Alpha AND Omega projects in the consideration. Here are the expected return values:

Group 1: 4 (Jim, Sandra, Carl, Sue)
Group 2: 4 (Jim, Carl, Sue, Ayako)

I am trying to achieve this with an INDEX/MATCH function that returns a specific named range to an INDIRECT function, but I get the N/A# error when I attempt this. Any help would be appreciated!

H7=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$6:$C$15,INDEX(G20:H21,,MATCH(H6,G19:H19,0)),0)),MATCH(B6:B15,B6:B15,0)),ROW(B6:B15)-ROW(B6)+1),1))

Control +shift+enter


only office 365


H7=COUNTA(UNIQUE(FILTER(B6:B15,ISNUMBER(MATCH(C6:C15,INDEX(G20:H21,,MATCH(H6,G19:H19,0)),0)))))

CARACALLA, I tried both of your formulas in H7 but didn't get any results? The first one returned 0 for both Group1 and Group2; the second one returned 1 for both Group1 and Group2.

Bo_Ry, these formulas seem to work -- thanks! If I wanted to add more criteria to the filter (for example, maybe by the "Month" and "Hour" columns), where in the formulas should I make adjustments?

Apologies for taking so long to get back to this -- I've been swamped at work and haven't had a chance to get back to this problem.

CARACALLA, thanks for the revision -- it seems to be working now! As I asked to Bo_Ry, if I wanted to add more criteria to the filter (for example, maybe by the "Month" and "Hour" columns), where in the formulas should I make adjustments? In my "real" formula there are multiple variables to filter by.

Attach the file, indicate which criteria you want to add and the corresponding results

Sorry about that; see attached. In this version we need to count by group, month, and hours. The month selection is a dropdown, but the hours selection is a free-response field. The count for hours should filter records greater than the number inputted. I have included three examples of expected results in the document.

H9=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$6:$C$15,INDEX(G20:H21,,MATCH(H6,G19:H19,0)),0)),IF(D6:D15=H7,IF(E6:E15>H8,MATCH(B6:B15,B6:B15,0)))),ROW(B6:B15)-ROW(B6)+1),1))

Control+shift+enter


L9=IF(L$6<>"",SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$6:$C$15,INDEX($G$20:$H$21,,MATCH(L$6,$G$19:$H$19,0)),0)),IF($D$6:$D$15=L$7,IF($E$6:$E$15>L$8,MATCH($B$6:$B$15,$B$6:$B$15,0)))),ROW($B$6:$B$15)-ROW($B$6)+1),1)),"")

Control+shift+enter

copy across

Thank you both -- this is working! However, I have one question about this part of the code:

Please Login or Register  to view this content.

In my example both Group 1 and Group 2 had the same number of projects in the group (in this case, two each). So when you use the area G20:H21, it's an equal block. But what if the the two groups are not equal? For example, if Group 1 had four projects and Group 2 had two? Would it be okay to select the same "block" area, even though Group 2 would include two blank spaces? This is part of the reason I was trying to use named ranges with INDIRECT: the size of each group is not certain, nor necessarily equal.

Dave, sorry for taking a few days to get back to you about this -- I've been swamped with work. While I don't need a list (in fact, I can't generate a separate list when seeking the count), I do like how you have utilized the two indirect group names in your solution. That offers some flexibility I wanted. Is there some way to combine the count formula in L9:N9 with the formulas in L10:N10, such that it only shows the count?

attach a file for problem post 11

Apologies; see attached. Notice that Group 1 has two projects, while Group 2 has three. I adjusted the expected results accordingly.

Dave, I feel dumb for not having thought of that, ha. I thought the UNIQUE formula couldn't be wrapped without spillover effects, but I was wrong. Should have tried before asking; sorry.

CARACALLA, thanks for your continued help with this. I ended up adapting your solution successfully to my "real" sheet.

Dave, your solution worked great in the example form I provided but for some reason could not get it working on my "real" sheet. The UNIQUE formula kept on generating a VALUE error. But I don't think it's a problem with how you solved it in the example sheet.

Thanks everyone for your help with this! I think I'll marked this solved now.