Frequency formula

[zahid.majid]

Dears;
I am facing an issue of FREQUENCY Formula, which is also available in the help of MS EXCEL;
=SUM(IF(FREQUENCY(MATCH(TS!$F$<WBR>4:$F$500,TS!$F$4:$F$500,0),<WBR>MATCH(TS!$F$4:$F$500,TS!$F$4:$<WBR>F$500,0))>0,1))

If i change the array size as 115 instead of 500, then i get some results;
What is the reason and how to rectify?


I need to SUM the frequency for a specific criteria
1) EMP#
2) Project Nature -- 1 (1,2,3,4,5,6,7,8,20)
3) Project codes as sample below
Project Nature Project Code\Name 1 091642061\Zain Opcos – BPM Implementation 20 Weekend 20 018901000\TC Group Management/Quality & Process related Acti

Thanks in advance;
Regards;
_ZM

 

[T. Valko]

Dears;
I am facing an issue of FREQUENCY Formula, which is also available in the help of MS EXCEL;
=SUM(IF(FREQUENCY(MATCH(TS!$F$<WBR>4:$F$500,TS!$F$4:$F$500,0),<WBR>MATCH(TS!$F$4:$F$500,TS!$F$4:$<WBR>F$500,0))>0,1))

If i change the array size as 115 instead of 500, then i get some results;
What is the reason and how to rectify?


I need to SUM the frequency for a specific criteria
1) EMP#
2) Project Nature -- 1 (1,2,3,4,5,6,7,8,20)
3) Project codes as sample below
Project Nature Project Code\Name 1 091642061\Zain Opcos – BPM Implementation 20 Weekend 20 018901000\TC Group Management/Quality & Process related Acti

Thanks in advance;
Regards;
_ZM

It's not real clear what you want to do.

You want to count unique entries but for what?

Note that it's more efficient to replace the second MATCH function with the ROW function:

=SUM(IF(FREQUENCY(MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0),ROW(TS!$F$4:$F$500)-ROW(TS!$F$4)+1)>0,1))<WBR>

 

[Aladin Akyurek]

Dears;
I am facing an issue of FREQUENCY Formula, which is also available in the help of MS EXCEL;
=SUM(IF(FREQUENCY(MATCH(TS!$F$<WBR>4:$F$500,TS!$F$4:$F$500,0),<WBR>MATCH(TS!$F$4:$F$500,TS!$F$4:$<WBR>F$500,0))>0,1))

If i change the array size as 115 instead of 500, then i get some results;
What is the reason and how to rectify?


I need to SUM the frequency for a specific criteria
1) EMP#
2) Project Nature -- 1 (1,2,3,4,5,6,7,8,20)
3) Project codes as sample below
Project Nature Project Code\Name 1 091642061\Zain Opcos – BPM Implementation 20 Weekend 20 018901000\TC Group Management/Quality & Process related Acti

Thanks in advance;
Regards;
_ZM

zahid

Try the following instead:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",
    MATCH("~"&TS!$F$4:$F$500,TS!$F$4:$F$500&"",0)),
      ROW(TS!$F$4:$F$500)-ROW(TS!$F$4)+1),1))

This counts the unique (distinct) items in the F-range of TS and it's robust against empty and/or blank cells plus special meaning chars that might occur around entries.

Would you elaborate a bit more on the other counts that you want?

 

[zahid.majid]

Dears;

Thanks for the prompt response;
My query is how to fit in the solution of two addition criteria;
1) EmpID
2) Project ID = 1
3) is available in your solution;

Thanks agains;
Looking forward your prompt response;
Regards;
_ZM

 

[T. Valko]

Dears;

Thanks for the prompt response;
My query is how to fit in the solution of two addition criteria;
1) EmpID
2) Project ID = 1
3) is available in your solution;

Thanks agains;
Looking forward your prompt response;
Regards;
_ZM

Can you post a small amount of sample data and tell us what result you expect?

 

[Aladin Akyurek]

Dears;

Thanks for the prompt response;
My query is how to fit in the solution of two addition criteria;
1) EmpID
2) Project ID = 1
3) is available in your solution;

Thanks agains;
Looking forward your prompt response;
Regards;
_ZM

A couple of questions...

What does the F-range house exactly?

What is the range EmpID stands for?

What is the range Project ID stands for?

Of which "object" do you need the distinct count? The earlier formula does a distinct count of the object of the F-range on TS.

 

[zahid.majid]

Dears;

=SUMPRODUCT(--(TS!$D$4:$D$500=TBC!$B$9),--(ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))),IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1))

TS!$D$4:$D$500=TBC!$B$9 --- EMP ID
ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))) ---- Project Nature
IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1)) -------------- Project code uniqueness

I hope the above clarification help you to find out a solution;

thanks;
Regards;
_ZM

 

[T. Valko]

Dears;

=SUMPRODUCT(--(TS!$D$4:$D$500=TBC!$B$9),--(ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))),IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1))

TS!$D$4:$D$500=TBC!$B$9 --- EMP ID
ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))) ---- Project Nature
IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1)) -------------- Project code uniqueness

I hope the above clarification help you to find out a solution;

thanks;
Regards;
_ZM

Try this array formula**.

=SUM(IF(FREQUENCY(IF(TS!$D$4:$D$500=TBC!$B$9,IF(TS!$E$4:$E$500=1,MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0))),ROW(TS!$F$4:$F$500)-ROW(TS!$F$4)+1),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells within the ranges.

 

[Aladin Akyurek]

Dears;

=SUMPRODUCT(--(TS!$D$4:$D$500=TBC!$B$9),--(ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))),IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1))

TS!$D$4:$D$500=TBC!$B$9 --- EMP ID
ISNUMBER(MATCH(TS!$E$4:$E$500,{1},0))) ---- Project Nature
IF(FREQUENCY(IF(TS!$F$4:$F$500<>"",MATCH("~"&TS!$F$4:TS!$F$500,TS!$F$4:TS!$F$500&"",0)),ROW(TS!$F$4:TS!$F$500)-ROW(TS!$F$4)+1),1)) -------------- Project code uniqueness

I hope the above clarification help you to find out a solution;

thanks;
Regards;
_ZM

Looks like you want a distinct count of items in F-range (Project codes) per EMP ID where Project Nature = 1... If so:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(
     IF(TS!$F$4:$F$500<>"",
     IF(TS!$D$4:$D$500=TBC!$B$9,
     IF(TS!$E$4:$E$500=1,
       MATCH("~"&TS!$F$4:$F$500,TS!$F$4:$F$500&"",0)))),
         ROW(TS!$F$4:$F$500)-ROW(TS!$F$4)+1),1))

Does this cover your objective?

 

[zahid.majid]

Dears;

Yes upto the Mark, got the desired results & objective;

thanks for all for cooperation & coordination;

Thanking you;
Regards;
_ZM