COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Welcome, everybody. And in this video session we come up with count it's formula, one of the most powerful formula in case you want to establish control systems in the data that you're working with. And soon you'll understand how this is going to benefit you. The first instance that we're going to take is a list of hypothetical party names TCS, IBM and Zenit. I would want to know that the word TCS, the name TCS, how many times it is appearing in the given list. Now, basic count formula count a formula will only talk about occurrences of anything in the given things, let's say eight occurrences important, but what we want is a very specific criteria based count, for which let me begin writing the formula count.

It's I go to the effects button which is Insert Function button. And here we begin our journey. Criteria one, let me pick up the cell, the one in light yellow, that is TCS criteria range one, since it's a pair value, I would step inside criteria range one and pick up the array of values. So IE six to 830. I'm going to press f4. That's how it goes.

As I can see from the sample answer, it says three, which means as I press OK, I get three, three indicating TCS is appearing three number of times, as I change the name to IBM, it still shows me three, but if I right zealot, and that is appearing two times, so a very basic level count if is the formula which counts the number of occurrences in a given range for the specified criteria or criteria. In fact, a lot of people prefer to write the count if formula or just in to the data that they are working with. So in this case, I'm going to put the count if formula under the heading occurrences, click on effects. Go to criteria one, pick up the input criteria one, the one just left to it. And then in the criteria range one, pick up the entire range of value, after which, of course, you're going to fix this entire array.

I press OK. down. So for TCS three, I will double click to get this formula copy pasted, and you will notice again Zenit, it says two. In fact, if I just change the value to TCS from IBM to TCS, you notice the one which are appearing for a number of times has the value for against them. So this was the basic usage of count ifs formula. And in fact, if you want to just add one more layer of complexity, let me also write that any ft. Any ft. to any STP mint. That Zenit, the companies in it through any SP mod, how much how many times it has given me the amount.

So I want to find out in the entire list how many Zenit company exists, not only that, amongst Zenit, the one which shows NFP, how many are there So what I'm going to do is I'm going to write the count is formula again, click on effects, open up all the boxes because I would need two pairs of criteria this time. Two criteria one, doesn't it. Criteria two. Notice I'm stepping inside the fourth box that says criteria to and, and using the cell Naft. Now once I've done so, the sample preview choose means that it against the second box. So I step inside criteria range one and choose the range of values from a six to a 13 pressing f4 to lock it.

I step inside the third box which says criteria range two. Now what was my criteria two it was any FP so let me choose same height from the column F. I can see the height the same is 63 f six to 830. I'm going to lock that okay. So it says any any ft appearing only one time and if I write TCS in EFT that is appealing tee times you can vouch for accuracy just by looking at the data and here it goes three times. So this was the two different application of count if formula. And to close the entire session.

Let me just quickly show you how instance number is formulated and shown on the theme was instance number. In a long list, I want to find out that this is appears first time, this is your second time this year appears third time. for IBM, it's going to be first time and second time towards the last end. So this we will adopt a similar strategy as what we have also adopted for some ifs formula to be right countifs. Click on effects. Go to criteria one, pick up this Yes.

But when you go to criteria range one, you pick up the same cell but add a colon to it automatically a six appears once again and make sure you lock only the starting point of this range. So eventually, the range Going to increase it should start with a six and is going to increase to 8789. As you copy paste it down. So when I do that, notice what you get for TCS. In fact, let me hide the other columns which are not going to be of any help right now. So here you go.

This is appealing first time, second time, third time and fourth time. I've been appearing first time and second time. You might be thinking, where would I need this? Well, I've seen people, they want unique ID codes to be generated from repeating IDs. So in this case, if I combine TCS with that of this number that we got this on our stream, you will be able to populate unique IDs based on column A and column C values TCS. One Two UCS three four iv 12012.

So this we often refer as a differential cumulative count In simple terms, it is the instance number. So there you go. That was a super trick from the counters formula

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.