countifs

G

go14344

New Member
Joined
Jun 17, 2014
Messages
28
i have a tables...
A B C
1 Employee Id Penalty Counter
2 TA329 A 1
3 TA329 A 2
4 TA329 A 3


How to formulate in cell C, to count how many penalty A in same Employee ID... like in c2 is 1, c3 is second time A so 2, and so on....
i use this formula in:
c2 =COUNTIFS($A$2:A2,[@employee Id],$B$2:B2,[@penalty])
c3=COUNTIFS($A$2:A3,[@employee Id],$B$2:B3,[@penalty])
c4=COUNTIFS($A$2:A4,[@employee Id],$B$2:B4,[@penalty])

but my problem is, when i add new row:
my c4 formula change automatic into this
c4==COUNTIFS($A$2:A5,[@employee id],$B$2:B5,[@penalty])
and
c5=COUNTIFS($A$2:A5,[@employee id],$B$2:B5,[@penalty]) ...

when i add new row again, it will be like this
c4=COUNTIFS($A$2:A6,[@employee id],$B$2:B6,[@penalty])
c5=COUNTIFS($A$2:A6,[@employee id],$B$2:B6,[@penalty])
c6=COUNTIFS($A$2:A6,[@employee id],$B$2:B6,[@penalty])..

and so on... any ideas why? thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Did'nt work.. still it will automatic change the formula...

my original formula before i add row O19 is:
O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B18,B18,$N$2:$N18,N18)

After adding row O19, O18 formula changed automatic (in red text) which is i dont know why...

O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B19,B18,$N$2:$N19,N18)
O19=COUNTIFS($B$2:$B19,B19,$N$2:$N19,N19)...

then i manual edit formula of O18 into this:
O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B18,B18,$N$2:$N18,N18)
O19=COUNTIFS($B$2:$B19,B19,$N$2:$N19,N19)...

when adding row O20, O19 formula will be change into this (below) but O18 is ok..

O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B18,B18,$N$2:$N18,N18)
O19=COUNTIFS($B$2:$B20,B19,$N$2:$N20,N19)
O20=COUNTIFS($B$2:$B20,B20,$N$2:$N20,N20).....

*everytime i add new row, the last row will be changed..
 
Upvote 0
here i upload the files and the link is in below...
https://www.dropbox.com/s/uvn1jb8jnzxylqk/Violation - Copy.xlsm?dl=0

i want my output will be like this when i add row 20:
O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B18,B18,$N$2:$N18,N18)
O19=COUNTIFS($B$2:$B19,B19,$N$2:$N19,N19)
O20=COUNTIFS($B$2:$B20,B20,$N$2:$N20,N20)

not like this:
O17=COUNTIFS($B$2:$B17,B17,$N$2:$N17,N17)
O18=COUNTIFS($B$2:$B18,B18,$N$2:$N18,N18)
O19=COUNTIFS($B$2:$B20,B19,$N$2:$N20,N19)
O20=COUNTIFS($B$2:$B20,B20,$N$2:$N20,N20)...

or everytime i add new row, my formula shoud be intact or will not change automatic.. try to add new row, then see the above row the formula will change by itself..
 
Upvote 0
I asked you "without reference to any formula"...

Your Violation # of Handbook is inconsistent: Most of the entries have a visual dot, created by Format Cells, but at least one a real dot (i.e. 8a.). That means trouble when you try to look up from within Violation. In what follows I did not tinker with this set up that you have.

NO TABLE version (thus: no structured references)

In E1 of Handbook enter the following header: CONCAT

In E2 of Handbook enter and copy down:

=$A2&"|"&$B2

In N2 of Violation enter and copy down:

=IF(OR($K2="",$L2=""),"",INDEX(Handbook!$D$2:$D$45,MATCH(K2&"|"&L2&"*",Handbook!$E$2:$E$45,0)))

In O2 of Violation enter and copy down:

=IF(N2="","",COUNTIFS($B$2:$B2,B2,$N$2:$N2,N2))

SAVE the workbook as NO TABLE VERSION.

Open the NO TABLE VERSION and save this again but this time as TABLE VERSION.

Select A1:N45 of Handbook.
Run Insert | Table --> This action creates a table, named Table1 (You can edit this name to something else.).

If so desired, change the formula in E2 of Handbook to:

=[@Under]&"|"&[@[Violation '#]]

Convert A1:O19 of Violation into a table by means of Insert | Table. (This action creates Table2. You can edit this name to something else.)

Change the formula of N2 into one that uses structured references. When done successfully, we get:

=IF(OR([@Under]="",[@[Violation '#]]=""),"",INDEX(Table1[Penalty],MATCH([@Under]&"|"&[@[Violation '#]]&"*",Table1[CONCAT],0)))

Keep the formula of O2 as is, that is, as:

=IF(N2="","",COUNTIFS($B$2:$B2,B2,$N$2:$N2,N2))

Note. Use IFERROR if you want to control the output of the Index/Match formulas for errors.
 
Upvote 0
ohh sorry, i miss the "without reference to any formula", ok will try later and let you know... tnxs..
 
Upvote 0
Aladin Akyurek said:
I asked you "without reference to any formula"...

Your Violation # of Handbook is inconsistent: Most of the entries have a visual dot, created by Format Cells, but at least one a real dot (i.e. 8a.). That means trouble when you try to look up from within Violation. In what follows I did not tinker with this set up that you have.

NO TABLE version (thus: no structured references)

In E1 of Handbook enter the following header: CONCAT

In E2 of Handbook enter and copy down:

=$A2&"|"&$B2

In N2 of Violation enter and copy down:

=IF(OR($K2="",$L2=""),"",INDEX(Handbook!$D$2:$D$45,MATCH(K2&"|"&L2&"*",Handbook!$E$2:$E$45,0)))

In O2 of Violation enter and copy down:

=IF(N2="","",COUNTIFS($B$2:$B2,B2,$N$2:$N2,N2))

SAVE the workbook as NO TABLE VERSION.

Open the NO TABLE VERSION and save this again but this time as TABLE VERSION.

Select A1:N45 of Handbook.
Run Insert | Table --> This action creates a table, named Table1 (You can edit this name to something else.).

If so desired, change the formula in E2 of Handbook to:

=[@Under]&"|"&[@[Violation '#]]

Convert A1:O19 of Violation into a table by means of Insert | Table. (This action creates Table2. You can edit this name to something else.)

Change the formula of N2 into one that uses structured references. When done successfully, we get:

=IF(OR([@Under]="",[@[Violation '#]]=""),"",INDEX(Table1[Penalty],MATCH([@Under]&"|"&[@[Violation '#]]&"*",Table1[CONCAT],0)))

Keep the formula of O2 as is, that is, as:

=IF(N2="","",COUNTIFS($B$2:$B2,B2,$N$2:$N2,N2))

Note. Use IFERROR if you want to control the output of the Index/Match formulas for errors.
Click to expand...

i try the above formula everything is fine and ok upto O19.... can you pls try add row O20, and check if O19 formula didnt change?... or my excel has a problem, because it will change the formula in O19, then it will say inconsistent formula
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
324
NoSparks
N
G
  • Question Question
Find Duplicate Values using a Criteria
Replies
2
Views
209
Eric W
Eric W
L
  • Solved
Number series based on multiple criteria
Replies
3
Views
207
SanjayGMusafir
SanjayGMusafir
Jeffrey Mahoney
  • Solved
LET Function Letting Me Down
Replies
7
Views
205
Jeffrey Mahoney
Jeffrey Mahoney
R
  • Solved
Round to nearest
Replies
4
Views
102
Joe4
Joe4

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

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