Excel- Count unique entry in different groupsClosed

Question

Hello,

I can't figure out how to write a formula to count unique entries in different groups.

My goal is to have a non-excel user to just paste the content of a worksheet ( monthly) and get the stats done automatically. I am not sure if this can be achieved by Pivot Table because I did not see it....... :|

I got this:
{=SUM(IF(Monthly_Transaction!F:F="DISTANCE",IF(FREQUENCY(IF(LEN(Monthly_Transaction!C:C)>0,MATCH(Monthly_Transaction!C:C,Monthly_Transaction!C:C,0),""), IF(LEN(Monthly_Transaction!C:C)>0,MATCH(Monthly_Transaction!C:C,Monthly_Transaction!C:C,0),""))>0,1)))}

and it worked. However, when I change the content of the reference sheet, this will not work. 

I will probably have to give up if I can't get this done in the next week. sigh

doruk · Accepted Answer

This is a special formula in excel. In order to update this formula you have to enter into cell with F2 and then Ctrl+Alt+Enter. This will update the formula however will automatise your work :((

dizzie · Answer

I found these 2 sites to be very useful - solved all my problems.  Hope they help you as well.  I sure thank 
Thanks to Roger Govier, who created this tutorial and sample file and xldynamic for the sumproduct examples.  Pretty much saved me from jumping :)

sumproduct 
http://xldynamic.com/source/xld.SUMPRODUCT.html


Tutorial:  using defined names; using INDEX and SUMPRODUCT:
https://www.contextures.com/xlNames03.html

dmc · Answer

If you are only sorting/re-ording the content of the reference sheet, then changing the last bit from ">0,1)))}" to ">0,0)))}" might solve the problem. 

However, if you are actually changing the data within the reference sheet, then yes the formula will use the updated data since it is tied to reference those cells.  

If you want to preserve the data before you update the reference sheet, then copy & paste special into a new column or the same column with the 'paste values" box selected. This will store the calculate values and they will not update.

Excel- Count unique entry in different groups

3 responses

Similar discussions

Subscribe To Our Newsletter!