1

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.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
D.Phillips
  • 13
  • 3

2 Answers2

1

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
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • 1
    I notice "*Germany*" has been changed to "Germany", this unfortunately doesn't count all instances, it has to be "*Germany*". When I tried to change it to "*Germany*" it brought up 0, why is this? – D.Phillips Mar 13 '17 at 10:21
  • Okay it has removed the wild cards for some reason, Germany should have wild cards in front and at the end of it. – D.Phillips Mar 13 '17 at 10:54
  • @DPhillips need a different algorithm to account for that. `ISNUMBER(SEARCH("GERMANY",...` should do it. But I don't have time to check it now. – Ron Rosenfeld Mar 13 '17 at 12:37
0

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

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • I have the same issue for the this as the above, the wild cards need to be in, as cells can have multiple countries in them. An example would be (Germany), (Germany, France, Belgium), (Belgium, Germany, Italy), ect. The brackets are there to separate the statements only.Thanks for your help. – D.Phillips Mar 13 '17 at 10:26