Formulas: Count If the same value & Sequence Order

[Laura J]

Good afternoon,

May I please ask for your help with 3 formulas? Below is a tabwith sample data. The highlighted columns are the ones requiring the formulas.
Unfortunately this seems to be beyond my excel knowledge

Formula 1 (Cntl Cnt - column C):
I would like to have formula to count lines with the same GroupID. However there will be a lot more group IDs than this sample.

Formula 2 (Seq – column D):
I would like to have sequence number within the one Group IDbased on how many names is included or how many times particular Group ID isentered

Formula 3: (Check point – column G):
Formula to check – if Contribution (G) per Name within one GroupID equals to the Sum Up (column B) per Group. TRUE or FALSE

A​	B​	C​	D​	E​	F​	G​
Group ID​	Cntl Amt​	Cntl Cnt​	Seq​	Name​	Contribution​	Check point Cntl Amt per Group ID = Sum of Contributions​
NEXT10​	100​	6​	1 ​	Alice​	10​	TRUE​
NEXT10​	100​	6​	2 ​	Irena​	15​	TRUE​
NEXT10​	100​	6​	3 ​	John​	20​	TRUE​
NEXT10​	100​	6​	4 ​	Alex​	30​	TRUE​
NEXT10​	100​	6​	5 ​	Marc​	15​	TRUE​
NEXT10​	100​	6​	6 ​	Josh​	10​	TRUE​
NEXT11​	70​	2​	1 ​	Monika​	45​	TRUE​
NEXT11​	70​	2​	2 ​	Alex​	25​	TRUE​
NEXT12​	30​	1​	1 ​	Dane​	30​	TRUE​

<tbody> </tbody>

Many & Many thanks for your help

 

[James006]

Re: Formulas: Coutn If the same value & Sequence Order

Hello,

Do you mean your three formulas should appear in Columns H,I,J ... ?

Edit : you meant replace C,D,G ...no ? have to learn to read ...!!!

In cell C2

=COUNTIF($A$2:$A$10,A2)

In cell D2

=COUNTIF($A$2:A2,A2)

In cell G2

=SUMIF($A$2:$A$10,A2,$F$2:$F$10)=B2

Hope this will help

 

[Aladin Akyurek]

Re: Formulas: Coutn If the same value & Sequence Order

In C2 enter and copy down:

=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2)

In D2 enter and copy down:

=COUNTIFS($A$2:A2,A2)

In G2 enter and copy down:

=SUMIFS($F$2:$F$10,$A$2:$A$10,A2)=AVERAGEIFS($B$2:$B$10,$A$2:$A$10,A2)

 

[nanndu141]

Use below formula for any range

C2 should be =COUNTIF(A:A,A2)
D2 should be =COUNTIF($A$2:A2,A2)
G2 should be =IF(SUMIFS(F:F,A:A,A2)=B2,"TRUE","FALSE")

hope it works with you..!

Regards,
Nandu

 

[Laura J]

Thank you all!
It's working very well much appreciated!