Excel- Count unique entry in different groups [Closed]

Report
-
 dmc -
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

3 replies

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 :((
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!