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​
6​	1 ​	TRUE​
6​	2 ​	TRUE​
6​	3 ​	TRUE​
6​	4 ​	TRUE​
6​	5 ​	TRUE​
6​	6 ​	TRUE​
2​	1 ​	TRUE​
2​	2 ​	TRUE​
1​	1 ​	TRUE​

<tbody> [TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

Alice​

[/TD]
[TD="bgcolor: transparent"]

10​

[/TD]
[TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

Irena​

[/TD]
[TD="bgcolor: transparent"]

15​

[/TD]
[TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

John​

[/TD]
[TD="bgcolor: transparent"]

20​

[/TD]
[TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

Alex​

[/TD]
[TD="bgcolor: transparent"]

30​

[/TD]
[TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

Marc​

[/TD]
[TD="bgcolor: transparent"]

15​

[/TD]
[TD="bgcolor: transparent"]

NEXT10​

[/TD]
[TD="bgcolor: transparent"]

100​

[/TD]
[TD="bgcolor: transparent"]

Josh​

[/TD]
[TD="bgcolor: transparent"]

10​

[/TD]
[TD="bgcolor: transparent"]

NEXT11​

[/TD]
[TD="bgcolor: transparent"]

70​

[/TD]
[TD="bgcolor: transparent"]

Monika​

[/TD]
[TD="bgcolor: transparent"]

45​

[/TD]
[TD="bgcolor: transparent"]

NEXT11​

[/TD]
[TD="bgcolor: transparent"]

70​

[/TD]
[TD="bgcolor: transparent"]

Alex​

[/TD]
[TD="bgcolor: transparent"]

25​

[/TD]
[TD="bgcolor: transparent"]

NEXT12​

[/TD]
[TD="bgcolor: transparent"]

30​

[/TD]
[TD="bgcolor: transparent"]

Dane​

[/TD]
[TD="bgcolor: transparent"]

30​

[/TD]
</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!