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

 

[Aladin Akyurek]

Dears;

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

thanks for all for cooperation & coordination;

Thanking you;
Regards;
_ZM

Great. Thanks for providing feedback.

 

[T. Valko]

Dears;

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

thanks for all for cooperation & coordination;

Thanking you;
Regards;
_ZM

You're welcome. Thanks for the feedback!

 

[zahid.majid]

Dear;

From this query suppose we get the result 2;
Now question is, if i want to see the result in the cell, instead of 2, it will give me result as

084276151\Zain Jordan ITS2 & TABS Integration -- Cell A2
091642061\Zain Opcos – BPM Implementation-- drag Cell A3

thanks in advance;
Regards;
_ZM

 

[T. Valko]

Dear;

From this query suppose we get the result 2;
Now question is, if i want to see the result in the cell, instead of 2, it will give me result as

084276151\Zain Jordan ITS2 & TABS Integration -- Cell A2
091642061\Zain Opcos – BPM Implementation-- drag Cell A3

thanks in advance;
Regards;
_ZM

What version of Excel are you using?

 

[Aladin Akyurek]

Dear;

From this query suppose we get the result 2;
Now question is, if i want to see the result in the cell, instead of 2, it will give me result as

084276151\Zain Jordan ITS2 & TABS Integration -- Cell A2
091642061\Zain Opcos – BPM Implementation-- drag Cell A3

thanks in advance;
Regards;
_ZM

If you are on Excel 2007 or later...

In A1 enter: #List#

A2, control+shift+enter and copy down:

=IFERROR(INDEX(TS!$F$4:$F$500,MODE(IF(TS!$F$4:$F$500<>"",
    IF(TS!$D$4:$D$500=TBC!$B$9,IF(TS!$E$4:$E$500=1,
    IF(ISNA(MATCH(TS!$F$4:$F$500,$A$1:A1,0)),
     MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0)*{1,1})))))),"")

Otherwise, on earlier Excel systems/versions:

A1, control+shift+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))

which is the earlier formula with the "~"& and &"" bits removed.

A2, control+shift+enter and copy down:

=IF(ROWS($A$2:A2)<=$A$1,
   INDEX(TS!$F$4:$F$500,MODE(IF(TS!$F$4:$F$500<>"",
    IF(TS!$D$4:$D$500=TBC!$B$9,IF(TS!$E$4:$E$500=1,
    IF(ISNA(MATCH(TS!$F$4:$F$500,$A$1:A1,0)),
     MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0)*{1,1})))))),"")

 

[zahid.majid]

Dears;

My MS EXCEL version is 2010;

Applied the formula and get the results as, only 1,

=IFERROR(INDEX(TS!$F$4:$F$500,MODE(IF(TS!$F$4:$F$500<>"",
IF(TS!$D$4:$D$500=TBC!$B$9,IF(TS!$E$4:$E$500=1,
IF(ISNA(MATCH(TS!$F$4:$F$500,$A$1:A1,0)),
MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0)*{1,1})))))),"")

when dragged down more cell, same result display; whereas we found the SUM frequency 2

Result as only 1
091642061\Zain Opcos – BPM Implementation

Desire result should be as the resultant of Frequency 2
091642061\Zain Opcos – BPM Implementation
084276151\Zain Jordan ITS2 & TABS Integration


Thanks;
Regards;
_ZM

 

[zahid.majid]

Dears;

Let me clarify you in this stage;


In case of earlier & current version the Ouput of the formula is remain same; Only appear 1 project code repeat the same in the next cell as well
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-outline-level: 2" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79 height=17 width=64>
thanks;
Regards;
_ZM


</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 2" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79 height=17>


</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

Dears;

My MS EXCEL version is 2010;

Applied the formula and get the results as, only 1,

=IFERROR(INDEX(TS!$F$4:$F$500,MODE(IF(TS!$F$4:$F$500<>"",
IF(TS!$D$4:$D$500=TBC!$B$9,IF(TS!$E$4:$E$500=1,
IF(ISNA(MATCH(TS!$F$4:$F$500,$A$1:A1,0)),
MATCH(TS!$F$4:$F$500,TS!$F$4:$F$500,0)*{1,1})))))),"")

when dragged down more cell, same result display; whereas we found the SUM frequency 2

Result as only 1
091642061\Zain Opcos – BPM Implementation

Desire result should be as the resultant of Frequency 2
091642061\Zain Opcos – BPM Implementation
084276151\Zain Jordan ITS2 & TABS Integration


Thanks;
Regards;
_ZM

Did you apply control+shift+enter?

 

[zahid.majid]

Dears;

Follow the same instruction as you said;
Let me do it again before clicking the send button;

sorry for all inconvenience;
it's totally my mistake, yes i confirmed your formula got the desired results;

Really great efforts exerted you & your team in the resolution of the issue;

Thanks again;
Regards;
_ZM

 

[Aladin Akyurek]

Dears;

Follow the same instruction as you said;
Let me do it again before clicking the send button;

sorry for all inconvenience;
it's totally my mistake, yes i confirmed your formula got the desired results;

Really great efforts exerted you & your team in the resolution of the issue;

Thanks again;
Regards;
_ZM

You are welcome.