Unique entries for average

[dmacmillan]

Hi,

This is a continuation of an earlier post as the two helpful respondents are offline ...

I am at this point:

=SUM(IF(FREQUENCY(IF(A7:A10000<>"",MATCH("~"&A7:A10000,A7:A10000&"",0)),ROW(A7:A10000)-ROW(A7)+1),1))

It functions well but I have to insert more conditions. Specifically:

1. Not only does A7:A100000 have to be "" but a particular type of entry ("NOJOB") must be excluded also. I tried IF(AND(A7:A10000<>"",A7:A10000<>"NOJOB"),MATCH ... without luck; and

2. Additional conditions in, say columns B, C and D, need to be accounted for.

Welcome assistance/comments.

Thank you in advance,
David

 

[Aladin Akyurek]

Hi,

This is a continuation of an earlier post as the two helpful respondents are offline ...

I am at this point:

=SUM(IF(FREQUENCY(IF(A7:A10000<>"",MATCH("~"&A7:A10000,A7:A10000&"",0)),ROW(A7:A10000)-ROW(A7)+1),1))

It functions well but I have to insert more conditions. Specifically:

1. Not only does A7:A100000 have to be "" but a particular type of entry ("NOJOB") must be excluded also. I tried IF(AND(A7:A10000<>"",A7:A10000<>"NOJOB"),MATCH ... without luck; and

2. Additional conditions in, say columns B, C and D, need to be accounted for.

Welcome assistance/comments.

Thank you in advance,
David

Try...

=SUM(IF(FREQUENCY(
   IF(A7:A10000<>"",
   IF(A7:A10000<>"NOJOB",
     MATCH("~"&A7:A10000,A7:A10000&"",0))),
      ROW(A7:A10000)-ROW(A7)+1),1))

If you would want to add an equality test involving the range in B, say, B = "JAD"...

=SUM(IF(FREQUENCY(
    IF(A7:A10000<>"",
    IF(A7:A10000<>"NOJOB",
    IF(B7:B10000="JAD",
      MATCH("~"&A7:A10000,A7:A10000&"",0)))),
       ROW(A7:A10000)-ROW(A7)+1),1))

Note 1. If you are not on Excel 2007 or later, you have to take the limit
for nesting levels into account.
Note 2. More IFs also mean degradation in performance. You can avoid this in part
by creating a range say in E that concatenates A, B, C, and D if appropriate to do so.
Note 3. For better performance, you could switch to SQL.

 

[dmacmillan]

Aladin,

Thank you for coming to the party once again. I had investigated your SQL solution but found the Array solution quick enough, certainly quicker than the SUMPRODUCT option in my earlier post. If nesting IFs markedly slows calculation, I will pursue SQL.

Kind regards,
David

 

[Aladin Akyurek]

Aladin,

Thank you for coming to the party once again. I had investigated your SQL solution but found the Array solution quick enough, certainly quicker than the SUMPRODUCT option in my earlier post. If nesting IFs markedly slows calculation, I will pursue SQL.

Kind regards,
David

You are welcome. Thanks for providing feedback.