This is very simple if you are using Excel 2007. You have standard excel function Countifs which lets you do the count based on multiple criteria.
But if you are using Excel 2003 this becomes tricky as you do not have any standard function that lets you do this. However you can do it using Pivot Tables. Sometimes you just need the count based on multiple criteria to be put into a report and using Pivot tables for such report may not be a viable option.
In such scenarios you need to go for a workaround using excel formula. Considering you have a data table like this and you want to know the number of customers in North Area serviced by Sales Man by name of Ram.
If you have Excel 2007 then it very simple just use
=COUNTIFS($B$13:$B$28,"North",$C$13:$C$28,"Ram") and it will give you count result 3.
For Excel 2003 users My favorite for such kind of calculation is Sumproduct function and you can do it with the help of following formula.
=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram"))
This formula will give you result as 3 customers.
You can add as many conditions here but be sure that the height of the range is same for all ranges mentioned in this formula
This actually is a workaround for another method array formula.
=SUM(($B$13:$B$28="North")*($C$13:$C$28="Ram")) confirmed with CTRL+Shift+Enter
Once you confirm this formula with CTRL+Shift+Enter it will add {} to the formula which will be visible in the formula bar only.
Look at the screen cast below to know the difference it make to the normal formula once confirmed as CSE formula. Look for the addtional {} added to formula
Download file having countif multiple conditions formula
You may find it difficult to enter array formula that is why I suggest you to go for SUMPRODUCT method.
To know more about array formulas you can read
I have a column of invoice numbers in an Excel 2003 file. In the next column, I want to do something like a VLOOKUP to see if there is a PDF file of the same name as the invoice number in a certain directory on the network. Can you do a comparison of a text from an Excel cell to names of PDF files.
ReplyDeleteHello John
ReplyDeleteWelcome to my blog...
Vlookup will be possible if you have file names in also in your excel file. So you will need to get the list of file names from your file directory on the network. Excel macro for getting file name is given below
Sub ListFiles()
F = Dir("C:\filepath\*.PDF")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub
You will need to change C:\filepath to your actual path on network. On running this macro you will get the list of PDF file names in excel.
Now I hope doing a Vlookup will be easy.
Let me know if you need to know more about this.
Thanks.
I have a spreadsheet which is attempting to count multiple criteria on another tab within the same spreadsheet.
ReplyDeleteIf the name of the spreadsheet being referenced is "Test", how does that change the COUNTIF formula for 2003 and 2007?
Thank you in advance.
Hello Paymon
ReplyDeleteWelcome to my blog. Considering the same data as in blog post above. But this time data in on sheet named "Test". The formula in this case will be
=SUMPRODUCT((Test!$B$13:$B$28="North")*(Test!$C$13:$C$28="Ram"))
This formula will work in Excel 2003 and 2007 both.
However if you have Excel2007 than you can use following formula also
=COUNTIFS(Test!$B$13:$B$28,"North",Test!$C$13:$C$28,"Ram")
Thanks
Yogesh Gupta
alot of thanks yogesh
Deletehi yogesh, plz help me in this..
ReplyDeleteNames
raam
raam
raam
shaam,raam
shaam,raam
raam
now i need count of rows containing raam i.e.6
and count of rows containing shaam i.e.2
@karandeep,
ReplyDeleteYou can do this with the COUNTIF function...
=COUNTIF($A$1:$D$100,"*raam*")
=COUNTIF($A$1:$D$100,"*shaam*")
Just change the range as needed (I used absolute references in case you planned to copy the formul). Oh, and notice the use of the asterisks... they are wildcards standing for zero or more characters at their location within the text. If you want to put the names in cell rather than hard coding them into the text, you would concatenate the cell with the asterisks (let's assume the name is in E1)...
=COUNTIF($A$1:$D$100,"*"&E1&"*")
Mr. Gupta,
ReplyDeleteTHANK YOU.
I am bookmarking your site and will be back often.
Sincerely,
Chip Dempsey
@Rick - Thanks for your response to Karandeep
ReplyDelete@Chip Dempsey - Welcome to my blog, you can also subscribe to email updates and feed for regular updates on new posts.
Regards//Yogesh Gupta
Thank you for having this page! I have been struggling with the task of counting instances of words with multiple criteria. This post really made it clear what I need to do! Now I'm done with my task right quick!
ReplyDelete~Nicolle
Hi Yogesh, can u pls tell me on how to count multiple opbects in a cell?
ReplyDeleteHi Yogesh,
ReplyDeleteYour blog is excellent and very helpful who are learning excel.
Kindly help me with the formula for the following:
I'm using the following formula to calculate time =SUMIF(H21:H266,AG2,I21:I266) where AG2 is a parameter. To count the no. of times a parameter occurs using =COUNTIF(H21:H266,AG2).
But the problem is this calculation is based on single unit, individuals filling the sheet are required to select the parameter everytime if there is a repeatition.
Is there any formula where we can specify numbers 1-50 (dropdown) for units and they can just select the parameter put across the no. of units for it.
Kindly let me know your contact details for further discussion.
Thanks & Regards,
Gauri R.
Hi Yogesh
ReplyDeleteYour multiple formula SUBPRODUCT works fine with alpha and not for numerics. Please help me to do same formula for numerics.
Many Thanks
Shabry
Hi Yogesh,
ReplyDeleteSorry its SUMPRODUCT, not SUBPRODUCT
Hi Shabry
ReplyDeleteThis formula works with all type of data. Let me know how are you using it. It will be better if you can send your file to me along with formula you have used. Pls send file to
yogesh at yogeshguptaonline dot com
Regards//Yogesh Gupta
days name status
Delete1 tom done
2 tom pending
3 tom done
4 john pending
Hi yogesh, i need two formula.
1.i need to count how many pending with john
2.and how many pending with john for morethan 2 days.
Could you please help me. Kindly send your answer to my mail id "kannibest@gmail.com"
Hi Yogesh,
ReplyDeleteI'll send the file by email.
Thanks
Shabry
Hi Yogesh,
ReplyDeleteI have created a spreadsheet with four Columns where i have Task that has a drop down(Training,Productivity,Break and so on.)Start Time, End time, Total time spent.
Now in a cell i want to calculate total number of hrs spent on particular task.
Could you suggest me on this..
Regards
Ganesh
Hi Ganesh
ReplyDeleteYou can use sumif formula using task and total time spent column. Send me your file in case you need help with the formula.
Regards//Yogesh Gupta
How could i get this to work
ReplyDelete2 columns
A B
"First Name", "Last name"
John Smith
John Carry
Bob Laurance
John Apple
Now I want to use countifs to get a count of all John's in column A but only if column B is Smith "or" Apple?
Thanks
@Yossi,
ReplyDeleteGive this formula a try...
=SUMPRODUCT((A1:A100="John")*((B1:B100="Smith")+(B1:B100="Apple")))
Of course, you can put the names in cells and then use cell references instead of hard coded names in the formula if you want.
Rick Rothstein (MVP - Excel)
This comment has been removed by the author.
ReplyDeleteHi Yogesh,
ReplyDeleteI'm having a little trouble with my formula, and I have tried to use your Sumproduct method. I have Excel 2003.
Here it is:
=SUMPRODUCT(('\\filename\file\file1\Inspections\Inspection LOG\[Master Inspection LOG.xls]NBT'!$B:$B="Site Visit")*('\\filename\file\file1\Inspections\Inspection LOG\[Master Inspection LOG.xls]NBT'!$D:$D,"<="&DATE(2010,7,9)))
I'm looking for "Site Visit" in column B, and want the inspection date to be earlier than July 9, 2010.
Can you please help?
Thanks!
Ivy
Hi Ivy
ReplyDeleteI suggest that you replace "<="&DATE(2010,7,9) with <=DATE(2010,7,9). I hope the formula will work after that.
Since dates are numeric numbers you do not require to convert them into string/text for comparision purpose.
Regards//Yogesh Gupta
Hi Yogesh,
ReplyDeleteHope Yogesh hope you can help with this. I've tried using Sum with Vlook-up but didn't help.
I want to do conditional sum on the value based in column A. For example I've got 4 columns; Col A with name and Col B to Col D with number values. For all values corresponding to Name "A" in Col A i want to sum numbers in Col B to Col D. Can you pls help me with this. Pls see example below if that provides any further clarity:
Name Value 1 Value 2 Value 3
A 8 8 8
A 2 7 3
A 4 9 1
B 4 8 7
B 3 7 2
C 1 7 4
A 2 1 7
B 2 3 6
B 4 6 4
C 5 4 9
C 3 9 8
Many thanks for your help in advance.
@Amita,
ReplyDeleteAre you looking for a **single** formula that gives the grand total of the cells in B:D for each row in which Column A cells contain the letter "A"? If so, try this formula...
=SUMPRODUCT((A1:A100="A")*(B1:B100+C1:C100+D1:D100))
Rick Rothstein (MVP - Excel)
Hi Yogesh,
ReplyDeleteI was looking for the "countif multiple options" and stumbled upon your blog after going through several other solutions and this is where I stopped.
Thank you for sharing.
Martin
Hi Yogesh,
ReplyDeleteGreat site! I have Excel 2003. I want to match dollar items in one column that are same amt where one is a credit and one is a debit (like balancing a checkbook). I am using this formula to find matches =COUNTIF($C$2:$C$1200,C2)and it is working great to find matching dollar amts in Column C. However, I want to add one more criteria. Column D is either Debit or Credit. I only want to match Debits with Credits. Currently, if I have 100.00 in C2 and 100.00 in C3, it will count as 2, regardless of what is in Column D2 and D3. I would like a formula to count only if D2 and D3 are NOT equal. So if C2 is 100.00 and C3 is 100.00 and D2 is Debit and D3 is Credit, that would be a match. Or D2 is a Credit and D3 is a Debit, also a match. But if D2 and D3 are both Debits, that is not a match. If D2 and D3 are both Credits, not a match. Can you please help? Thank you.
Dear Yogesh.
ReplyDeleteCan you please help me?
I have a spreadhseet and i am using multiple sheets. I am using one sheet as a database, client information, name address etc, and another sheet to capture contact/activity information.
I have already created the formual
=COUNTIF('Mary Contact'!$C$2:$C$3000,'Basic Database'!C3)to count how many times a client is contacted (data inputted on one sheet to be counted on the main database)the formula recognises the client reference and counts how may times it is inputted on the second sheet.
I now want to create another formula (in a different column) which does the same as the above FIRST and then count if another if column states DNA. Meaning it checks for the client ref then counts the DNA against that ref.
I really hope you can help with this. I have tried different variations had no luck.
I hope this meakes sense!?
Many thanks
I have a spreadsheet with almost 103,000 rows and 28 columns of data from which I'm creating multiple pivot tables.
ReplyDeleteThe rows in Column 'A' contain four values, "Big", "Sky", "Lake", "Lodge". Column 'B' contains a Customer Number, a six-digit code. Column 'C' contains the formula =COUNTIF($B$2:$B$10300,$B2), giving me a count each Customer Number. This is great, I now know if that Customer Number is a one-time or repeat customer.
The problem is, I now need to know if that Customer Number is a repeat customer of "Big", "Sky", "Lake", or "Lodge". In other words, I need to know if they were a customer of at least two of those four.
This formula does not work:
=PRODUCT(COUNTIFS(A$2:A$10300,{"Big","Sky","Lake","Lodge"},B$2:B$10300,B2))>0
Is there a formula that can return something simple as "True" or "False" - anything that will allow me to pull/sort in a pivot table - for this criteria.
Make sense?
Any/all help is appreciated...
Hi Yogesh
ReplyDeleteIs it possible to use to conditions in the Sumproduct formula you have mentioned? ie
=SUMPRODUCT(($B$13:$B$28="North OR South")*($C$13:$C$28="Ram"))
If Ram exist for two regions (North and South) I want to return the value. I used "OR" here, I know this is not correct. Can you please help me with the correct syntax.
Thanks
Ram.M
Hi Yogesh,
ReplyDeleteI have three columns I need to count:
Column A: "Player"
Column B: "Play:
column C: "Yards"
The first two are simply counts based on criteria.
The A column will be a player's number.
The B column will be "r" for run, or "p" for pass or "x" for nothing attributed to the player
The C column will be the sum of the first two criteria in the "yards" column.
Thanks,
Hi Yogesh,
ReplyDeleteHow to use un-need symbol/formula ; if we want to exclude some criteria while using countifs.
sometime when i export my data from my software in excel then i insert subtotal for date wise total..bt excel cant show summary of subtotal. i have most important of summary of data for work..so plz. say abt this prblm.
ReplyDeleteHI I NEED TO COMPARE ALTERNATE CELLS WITH COUNTIF PLS HELP
ReplyDeleteEXAMPLE with column : A5 , C5, E5 or rows : c3,c5,c7,c9
COUNTIF SHOULD NOT INCLUDES B5, D5 & F5 or c2,c4,c6 ETC..
Hi! I need to count with 3 different conditions. All data is in one sheet and I need to count the numbers of cases assigned to Person A (condition 1), type of cases assigned to Person A (condition 2) within a month (condition 3).
ReplyDeleteGreatly appreciate if you can help with this.
Thanks much!
Hi,
ReplyDeleteI need to count the number of Pass and number of Fail for a given date for the list below:
Pass 12/12/2011
Pass 12/13/2011
Fail 12/12/2011
Fail 12/13/2011
Pass 12/12/2011
Pass 12/12/2011
Please advise on how to achieve this. I tried with this...=SUM((A16:A24="Passed")*(B16:B24="12/13/2011")), but not working....please help !
=COUNTIF(A2:A7,"pass")
ReplyDelete=COUNTIF(A2:A7,"fail")
Hi,
ReplyDeletethe formula given by you =COUNTIF(A2:A7,"pass")
=COUNTIF(A2:A7,"fail") only gives the number of pass/fail....how to get the number of pass for a given date ?
Regards,
Sanjay.
sir i want 1 to 100 numbers i want to below 40 to 30 give me formula
ReplyDeleteHi i have a table with 2 columns columns a customer name and column b id customer i want to creat a type that search on column a the name customer and give me in column c a text (the name of my saler)
ReplyDeletehi yogesh,
ReplyDeleteCan you help me out by sharing your thoughts about the viability of using range based criterion like '>10and <12' alongwith countif ?
Koushik Ghosh
hi i have 3 A,B&C colums with different value & name. i want to count to equal & < value of Colume B to C related of Colume A. example is below for your reference.
ReplyDeleteQ:count how many Fish (Colum A) value of B is equal & < to C.
A B C
Fish 1 1
Cat 1 1
Fish 2 1
Bird 1 1
Fish 1 1
i want to count below columes value with out repeated same value.for example 123 is write in Colum A 3times & 245 write 2 times & 356 only 1 time & so on.... but i want that which value repeated or not. its count 1. & we take total count of said Colum in Last cell.
ReplyDeleteColum A
123
123
245
245
123
356
This comment has been removed by the author.
ReplyDeleteThanks a Lot Sir
ReplyDeleteI am Looking for the Output with no of cells filled in the below mentioned formula in 2007 Excel. But need to apply the formula in 2003 Excel. Kindly suggest the way forward.
ReplyDeleteCOUNTIFS(C:C,"3/1/2012",D:D,"<>")
Tried in SUMPRODUCT Formula but not working....
Sir
ReplyDeleteI want to Calculate interest @2% P.M on Late Payment from Customer on Every invoices.Grace Period Should be 30 Days.Invoices Should be adjusted on FIFO Basis with Payment.Can you Help me for this.I will be grateful for this.My E mail id is abhishekp12in@gmail.com
Thanks
Abhishek
Dear Yogesh,
ReplyDeleteI want to counting in two condition for ex. =countif(a1:a5,"2<>3") in between two number but its not working, so please suggest me. my Email ID is personal.mahesh33@gmail.com
Dear Yogesh,
ReplyDeleteI want to counting in two condition for ex. =countif(a1:a5,"2<>3") in between two number but its not working, so please suggest me. my Email ID is personal.mahesh33@gmail.com
Dear Yogesh
ReplyDeleteCol.A (date) Col.B (Plan) Col.C (Code of Salesman)
20111213 149 80001603
20111213 149 80001603
20111130 91 80001709
20111130 165 80001709
20111130 91 80001709
20111130 91 80001709
20111130 165 80001709
20111129 805 80001709
20111129 805 80001709
20111129 805 80001709
20111129 91 80001709
20120131 802 80001725
20120124 91 80001904
20120124 91 80001904
20120124 91 80001904
20120118 91 80001904
20120118 91 80001904
20111031 805 80001904
20111121 805 80001904
20120130 91 80001904
20111202 165 80001904
20110628 179 3449177
20110928 179 3449177
20111031 14 3449177
20110425 165 3868177
20110425 75 3868177
I want to count the number of salesman (excluding duplicates) who sold a particular plan (say 165) between 20110425 to 20111223. Kindly suggest me a formula. My Email ID is ratheeajit.singh@gmail.com
I have one I need solving.
ReplyDeleteI have 4-columns that I want to search for a list of possible matches.
Here's a sample:
Here's the column, I want to search...
A B C D
1 -95A0 -95A0 NONE 11H3C
2 -95A0 -95A0 NONE 11F3Q
3 -95A0 -95A0 NONE 62E3E
4 -90G0 -90G0 NONE
I want to countif (or whatever) everycell above for any cell that has the following...
A B
10 11B 46P
11 11E 46S
12 11F 47S
13 11G 48A
Example: A12 (11F) is found in D2 (11F3Q)--this should count as 1.
Any suggestions?--Much appreciated in advance.
Addendum: I would like to avoid a long formula. It seems to work well on individual cell criteria [ex: =COUNTIF(BB2:BD586,BD631&"*")], but I would like to do something like this: =COUNTIF(BB2:BD586, BC591&"*":BD631&"*"), which of course doesn't work this way.
DeleteHello--
ReplyDeleteI am desperate for a formula that will find matches when the first 11 digits in a cell equal the last 11 digits in a cell, in the same column. Like this:
Cell A
12345678911445555
78145612345678911
I hope you can help. Thanks!
=IF(LEFT(A1;11)=RIGHT(A1;11);"11 char in beginning and end match"; "No match")
DeleteYou'd like also to keep the cell format as Text, since numbers will convert your long number to exponential presentation and there formula will not work.
Hi!
ReplyDeleteNeed help with COUNTIF or COUNTIFS, I have a data where I need to count the no. of entries in a column which are for a particular month, say for March so what should be the formula for this.
Thanks & Regards,
Pritish Kamal
Hi!
ReplyDeleteNeed help with COUNTIF or COUNTIFS, I have a data where I need to count the no. of entries in a column which are for a particular month, say for March so what should be the formula for this.
Thanks & Regards,
Pritish Kamal
Hello Yogesh, can you help me figure out why I cannot get this to work?
ReplyDelete=COUNTIF(I2:R11,">=B2")-COUNTIF(I2:R11,">=C2")
If I enter the formula in this fashion, it works fine, but now when I want to take the value from a cell that can change.
=COUNTIF(I2:R11,">=0")-COUNTIF(I2:R11,">=12")
Pls enter it like this to work
Delete=COUNTIF(I2:R11,">="&B2)-COUNTIF(I2:R11,">="&C2)
You need to join the text string with the cell reference rather than just putting it in the text string. The way you have used it excel does not recognized it as cell reference.
Hello Yogesh,
ReplyDeleteCan you please Help me Regd this........
Conversion of Numeric to Character in Excel 2003 without writing any code by applying only formula.
Regards,
Ram Prasad
Hi Ram
DeleteNot clear about your request, can you give some example of what do you need.
Thanks
Small Note: SUMPRODUCT formula will not work for entire column.
ReplyDeleteExample: =SUMPRODUCT(($B:$B="North")*($C:$C="Ram"))
The above formula will give a error.
Instead use a cell range as suggested by Yogesh.
=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram"))
Thanks.
Hello Yogesh,
ReplyDeletecan you help me...
What is the formula for my situasion:
eg:
A A A A A - 5A (summaries: all A)
A B A A A - 4A 1B (summaries: at least 1B)
A B B B B - 1A 4B (summaries: at least 1B)
A B B A C - 2A 2B 1C (summaries: at least 1C)
B C D B B - 3B 1C 1D (summaries: at least 1D)
E E E E E - 5E (summaries: all E)
What is the formula to count:
all A - 1 person
at least 1B - 2 person
at least 1C - 1 person
at least 1D - 1 person
all E - 1 person
and so on
Hi
ReplyDeleteI have data for 5 Teams - A,B,C,D,E and each team has 10 members. Now I would like to have a formulae to calualate, No of present "P" within each team. No of members who have >7:30, <7:00>7:30 staffed hours ..
Regards
Guru
MIS
.Thanks Yogesh,
ReplyDeleteIt's so helpful!!!
Regards,
Divy
Hi Dears
ReplyDeletehow can I use countifs with three column on of them has two text criteria
i tried this one but it not work
=COUNTIFS(H2:H223,COUNTIF(H2:H223,"RFT")+COUNTIF(H2:H223,"On air"))
appreciate your kind support
Hi Dears
ReplyDeletehow can I use countifs with three column on of them has two text criteria
i tried this one but it is not work:
=COUNTIFS(H2:H223,COUNTIF(H2:H223,"RFT")+COUNTIF(H2:H223,"On air"))
appreciate your kind support
Try This
Delete=COUNTIFS(H2:H223,{"RFT","On air"})
Hello,
ReplyDeleteI am using this formula:
=COUNTIF(J31:K31,"*A*")+COUNTIF(J31:K31,"*P*")
I also have PP as a value and it is counting this. I only want to count the P. Help???
Use This Formula
Delete=COUNTIFS(Attendance!J31:K31,{"*A*","*P*"})
Thanks a lot. I was struggling with multiple criteria function for the last 3 days. Since I am using MS Office 2003, I was unable to use the "COUNTIFS" function.
ReplyDeleteI sought help from here and it really worked for me.
Thanks a lot again.
DATE RECEIPTNO AMOUNT CASH/CHE/D.D/RTGS
ReplyDelete1-Sep-14 AK-290 29,513.00 CHEQUE
1-Sep-14 AK-291 5,000.00 CASH
1-Sep-14 AK-295 2,000.00 CASH
1-Sep-14 AK-296 658,694.00 CHEQUE
HI SIR
SUM FROM DATE WISE SUM (CASH CHE DD RTGS)