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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
Please try to post a sample along with the expected results for that sample without reference to any formula.
 
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

J
  • Question
Formula to find date and return column number
Replies
2
Views
148
jc352
J
J
  • Question
Formula to move information to calendar
Replies
0
Views
74
jc352
J
E
  • Solved
Find "nth" input, whilst using a filter
Replies
2
Views
118
ellison
E
Chris_010101
  • Solved
Annual appraisal score formula
Replies
2
Views
197
Chris_010101
Chris_010101
R
  • Question
Applying a formula to a subset of rows
Replies
3
Views
121
rodqntr
R

Forum statistics

Threads
1,214,652
Messages
6,120,747
Members
448,989
Latest member
mariah3

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