Formulas: Count If the same value & Sequence Order

Laura J

New Member
Joined
Aug 31, 2018
Messages
6
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

 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
Code:
=COUNTIF($A$2:$A$10,A2)

In cell D2
Code:
=COUNTIF($A$2:A2,A2)

In cell G2
Code:
=SUMIF($A$2:$A$10,A2,$F$2:$F$10)=B2

Hope this will help
 
Last edited:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,619
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top