#1
|
|||
|
|||
COUNT TRIPLETs present in an array of values
The following formula: =SOMMA(SE(FREQUENZA(SE($A$3:$M$7=O3;RIF.RIGA($A$3: $M$7);SE($A$3:$M$7=P3;RIF.RIGA($A$3:$M$7);0));RIF. RIGA($A$3:$A$7)-1)=2;1;0))
manages to count PAIRS present in a given array of values … QUESTION: HOW can it be modified to count triplets instead of Pairs of values? Sample file attached. |
#2
|
||||
|
||||
Duplicate thread https://www.msofficeforums.com/excel...ay-values.html
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Sorry for the duplication ... I did it thinking that posting my thread in the Excel progranmming section was not appropriate ... so, since I did not find How to do it myself, can YOU please cancel the post in Excel Programming leaving it exclusively in the plain EXCEL Forum? Begging you for pardon for the inconvenience caused, thank you so much,
AP41 |
#4
|
|||
|
|||
Hi,
see the new formula: =SOMMA(SE(FREQUENZA(SE($A$3:$M$7=O3;RIF.RIGA($A$3: $M$7);SE($A$3:$M$7=P3;RIF.RIGA($A$3:$M$7);SE($A$3: $M$7=Q3;RIF.RIGA($A$3:$M$7);0)));RIF.RIGA($A$3:$A$ 7)-1)=3;1;0)) |
#5
|
|||
|
|||
Dear FJNS, thank you so much for your excellent fine working solution! As a not so much expert of matrix formulas I will now dedicate myself to study and try to understand the modifications yuo made! To you and this Forum thank you very much again.
|
#6
|
|||
|
|||
Hi,
I'm so glad, the new formula is working well. I also attach another workbook. The formula for cell V2 on worksheets (Singles, Pairs, Triplets, Quadruplets, Quintuplets, Sextuplets): =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);0);RIF.RIGA($A$2:$A$14)-1)=1;1;0)) =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);SE($A$2:$M$14=P2;RIF.RIGA($A$2:$M$14);0)); RIF.RIGA($A$2:$A$14)-1)=2;1;0)) =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);SE($A$2:$M$14=P2;RIF.RIGA($A$2:$M$14);SE($ A$2:$M$14=Q2;RIF.RIGA($A$2:$M$14);0)));RIF.RIGA($A $2:$A$14)-1)=3;1;0)) =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);SE($A$2:$M$14=P2;RIF.RIGA($A$2:$M$14);SE($ A$2:$M$14=Q2;RIF.RIGA($A$2:$M$14);SE($A$2:$M$14=R2 ;RIF.RIGA($A$2:$M$14);0))));RIF.RIGA($A$2:$A$14)-1)=4;1;0)) =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);SE($A$2:$M$14=P2;RIF.RIGA($A$2:$M$14);SE($ A$2:$M$14=Q2;RIF.RIGA($A$2:$M$14);SE($A$2:$M$14=R2 ;RIF.RIGA($A$2:$M$14);SE($A$2:$M$14=S2;RIF.RIGA($A $2:$M$14);0)))));RIF.RIGA($A$2:$A$14)-1)=5;1;0)) =SOMMA(SE(FREQUENZA(SE($A$2:$M$14=O2;RIF.RIGA($A$2 :$M$14);SE($A$2:$M$14=P2;RIF.RIGA($A$2:$M$14);SE($ A$2:$M$14=Q2;RIF.RIGA($A$2:$M$14);SE($A$2:$M$14=R2 ;RIF.RIGA($A$2:$M$14);SE($A$2:$M$14=S2;RIF.RIGA($A $2:$M$14);SE($A$2:$M$14=T2;RIF.RIGA($A$2:$M$14);0) )))));RIF.RIGA($A$2:$A$14)-1)=6;1;0)) I hope, it will be easier for you to understand the logic of the formulas. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
COUNT TRIPLETs present in an array of values | AP41-at-OfficeFORUM | Excel Programming | 1 | 10-27-2022 11:22 PM |
How To Make A Formula To Count Only 2 Values Between 01 To 100 | xrfsna | Excel | 3 | 05-12-2017 11:27 AM |
Count max of x values | timbrignall | Excel | 2 | 05-10-2016 07:45 AM |
How do you loop through an array with some empty values? | omahadivision | Excel Programming | 5 | 11-28-2013 10:49 AM |
How to count the frequency of data and also tally value from an array of Excel record | KIM SOLIS | Excel | 5 | 09-07-2011 09:01 AM |