Excel Formula: Count Unique Values in a Row Based

问题:

I've been trying to get this one for quite a while and I've tried a few different approaches and can't get the result I'm looking for with any of them. I have a dataset similar to the below in Excel (apologize for the formatting, I can't yet post images):

          Manager 1     Manager 1       Manager 2        Manager 2
Issuer 1          0           0               0                  0  
Issuer 2        100         100               0                100
Issuer 3        100           0             100                  0
Issuer 4          0           0               0                  0

I'm trying to count the number of unique Issuers associated with each Manager ("associated" being defined as having a value > 0). Each Issuer is unique (only shows up once in the list) but each Manager can show up multiple times. I'm trying to get one number for each Manager. So, the results would be as follows:

Manager 1: 2 (not 3, because Issuer 2 shows up twice for Manager 1, and I'm looking for unique values so it would only be counted once)

Manager 2: 2

I can write a formula to count the total number of greater-than-zero results for each Manager, but not the total number of unique greater-than-zero results. I've tried variations of SUMPRODUCT and DCOUNT but I'm not getting the correct result. I could also write a macro for what I'm trying to achieve but I'd prefer a formula (partly for the challenge in seeing how it can be done). Any help is greatly appreciated!

回答1:

Let MRange be the 1x? range of manager names, and VRange be the range of number values.

If the label for the manager you want is in A1, get the unique number of issuers with:

{=SUM(N(MMULT(VRange,TRANSPOSE(N(MRange=A1)))>0))}

Be sure to enter using ctrl+shift+enter (Note that the sumproduct hack doesn't work in this case.)