Count unique values in a range, using an indirect cell reference

1
votes

Hi I am trying to count the number of unique product codes in a range with one criteria applied using.

The formula so far is:

 =COUNTIFS(INDIRECT("'" & B7 & "'!" & B8 & ":" & B9),"*Germany*",INDIRECT("'" & O7 & "'!" & O8 & ":" & O9),"criteria for counting unique values")

I don't understand how to adopt the formulas that I have seen online to use the INDIRECT range reference I am using.

The range of unique values that need counting are product codes containing both alpha and numerical characters and the cell type is text.

Can someone please help me understand what I need to add to my formula?

Thanks.

2
excelcountuniqueexcel-indirect

2 Answers

1
votes

If there are no blanks, you can try:

=SUMPRODUCT((INDIRECT("'"&B7&"'!"&B8&":"&B9)="Germany")/COUNTIFS(INDIRECT("'"&O7&"'!"&O8&":"&O9),INDIRECT("'"&O7&"'!"&O8&":"&O9),INDIRECT("'"&B7&"'!"&B8&":"&B9),INDIRECT("'"&B7&"'!"&B8&":"&B9)))

This assumes your INDIRECT is correctly resolving to your desired address:

  • B7: Sheet name of first range
  • B8: Start Cell of first range
  • B9: End Cell of first range
  • O7: Sheet name of second range
  • O8: Start Cell of second range
  • O9: End Cell of second range
0
votes

This is the other way of doing it - adapted from Barry Houdini's answer and see also

=SUM(IF(FREQUENCY(IF(INDIRECT("'"&B7&"'!"&B8&":"&B9)="Germany",MATCH(INDIRECT("'"&O7&"'!"&O8&":"&O9),INDIRECT("'"&O7&"'!"&O8&":"&O9),0)),ROW(INDIRECT(O8&":"&O9)))>0,1))

You could add a test for blank cells if you wanted to

=SUM(IF(FREQUENCY(IF(INDIRECT("'"&B7&"'!"&B8&":"&B9)="Germany",IF(INDIRECT("'"&O7&"'!"&O8&":"&O9)<>"",MATCH(INDIRECT("'"&O7&"'!"&O8&":"&O9),INDIRECT("'"&O7&"'!"&O8&":"&O9),0))),ROW(INDIRECT(O8&":"&O9)))>0,1))

Is an array formula and must be entered with CtrlShiftEnter