• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel COUNTIF and COUNTIFS Formulas Explained

You are here: Home / Excel Formulas / Excel COUNTIF and COUNTIFS Formulas Explained
September 13, 2010 by Mynda Treacy

In this tutorial we’re looking at the COUNTIF and COUNTIFS Formulas, and we'll take a look at a couple of different applications for them.

Plus you can download a practice workbook, you'll find the file down below.

The COUNTIF/S functions work ALMOST in the same way as the SUMIF/S functions only they’re slightly simpler.  So if you haven’t mastered SUMIF/S yet be sure to check out our SUMIF/S tutorial too.

COUNTIF extends the capabilities of the basic COUNT function by allowing you to tell Excel to only COUNT items that meet a certain criteria.  New in Excel 2007 is the COUNTIFS function, which allows you to stipulate multiple criteria, hence the plural.

Enough explanation, let’s dive into an example as it’s easier to visualise.

COUNTIF Function

The function wizard in Excel describes COUNTIF as:

=COUNTIF(range,criteria)

Looks fairly simple and it is.  Let’s translate it into English now by applying it to an example. Say we wanted to count the number of times Dave appeared in column C of the table below.

COUNTIF Formula Explained

Translated our formula would read like this:

=COUNTIF(count the number of cells in column C, that contain 'Dave')

We could even create a table under the data to count the occurrences of each builder:

COUNTIF Formula explained

Our formula in cell C12 would be:

=COUNTIF(C2:C7,"Dave")

While the above formula is good, if we were to copy it to the rest of the summary table (cells C11 to C14) we would have to manually change the cell references and builder’s name to get the correct answers.

To avoid this manual intervention, we can use absolute references, which will speed up the process of copying the formula to the remainder of column C. 

With absolute references our formula would look like this:

=COUNTIF($C$2:$C$7,$B12)

 
The ‘$’ signs tell Excel that we don’t want the reference after the ‘$’ sign to change when we copy the formula.  For example, if we copied the formula into cell C13 it would read:

=COUNTIF($C$2:$C$7,$B13)

In the above formula we can see that the only reference that changed was $B12, which became $B13.

The best way to understand how this works is to try it for yourself. 

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook used in this example here. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Note: I used a basic example to illustrate how to use COUNTIF, but you could also achieve this count by builder using the subtotal tool in the Data tab. 

COUNTIFS Function

The function wizard in Excel describes COUNTIFS as:

=COUNTIFS(critera_range_1,criteria_1,criteria_range_2,criteria_2.....and so on if required)

Extending the previous COUNTIF example above, say we wanted to only summarise the data by builder for jobs in the South region.  We could use the COUNTIFS function, as it allows us to set more than one condition.

Here’s how the formula would be interpreted if we wanted to count the occurrences of Brian in column C, where jobs were in the South region:

=COUNTIFS(count the number of cells in column C if, they contain ‘Brian’ and, if in column B, they are also for the South region)

Note: Excel will only include the cells in column C in the count when both conditions (Brian & South) are met.

Using absolute references, our COUNTIFS formula in Excel cell C20 would read:

=COUNTIFS($C$2:$C$7,$B20,$B$2:$B$7,$B$17)
COUNTIFS Formula Explained

Try other operators

Just like the IF Statement and SUMIF formula, the COUNTIF and COUNTIFS are based on logic.  This means you can employ different tests other than the text matching (Brian & South) we’ve used above.

Other operators you could use are:

  • =             Equal to
  • <             Less Than
  • >             Greater Than
  • <=          Less than or equal to
  • >=          Greater than or equal to
  • <>          Less than or greater than

For example, if you wanted to count the jobs with an average > $300k the formula would be:

=COUNTIF($E$2:$E$7,">300")

Note: again I’ve used a simple example to illustrate this, but another way to achieve this summary table for each builder by region is to use a Pivot Table.

Want to Learn More Excel Formulas

Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂

Spread the Word

If you found this useful please share it with your friends and colleagues on Google+1, LinkedIn, Facebook and Twitter.

More Statistical Posts

Excel COUNT, COUNTA and COUNTBLANK Functions

Excel COUNT, COUNTA and COUNTBLANK Functions

Excel MIN MAX SMALL and LARGE Functions

Excel MIN MAX SMALL and LARGE Functions

Microsoft Excel Standard Deviation Functions

Microsoft Excel Standard Deviation Functions

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: statistical
Previous Post:sumif and sumifs formulasExcel SUMIF and SUMIFS Formulas Explained
Next Post:Excel VLOOKUP Formulas Explainedvlookup formula

Reader Interactions

Comments

  1. maxwell kosy

    September 8, 2022 at 2:52 am

    Fill in the output in F14:F44: amount of finished products times the value in I5.
    trying to find out the formula suitable for output of a finished product
    please help….
    I used =countif(E14:E44,”>I5″) and =countif(E14:E44, $I$5)
    would be expecting your reply.

    Reply
    • Mynda Treacy

      September 9, 2022 at 11:26 am

      Sounds like a question on a test. Please post your question on our Excel forum where you can also upload a sample file so we can see the data you’re working with and we can help you further.

      Reply
  2. Ching

    June 19, 2022 at 2:15 pm

    help!!!

    I need count if column A-Columb B=2

    Reply
    • Mynda Treacy

      June 19, 2022 at 5:32 pm

      Hi Ching,

      =SUMPRODUCT(--(A6:A11-B6:B11=2))

      Mynda

      Reply
  3. Manikandan

    October 21, 2021 at 10:12 am

    Can I use this formula for Attendance usage? I am trying to locate a specific word (or) in time in a cell (in column A) and assign a value out time to that cell in column B. Here is my formula, but the error message says I have too many arguments entered. Please help!

    Need for three contain – Present, Absent & Leave, how to use the formula

    =IF(AND(A2=””,B2=””),””,”P”,”)

    Reply
    • Mynda Treacy

      October 21, 2021 at 10:45 am

      The last ,” is redundant. Try:

      =IF(AND(A2=””,B2=””),””,”P”)

      Mynda

      Reply
  4. Asher Njihia

    April 20, 2020 at 5:15 pm

    Where are the COUNTIF formulae effects

    Reply
    • Mynda Treacy

      April 20, 2020 at 6:48 pm

      Hi Ahser, I’m not sure what you mean by this, sorry.

      Reply
  5. Autumn Turner

    January 29, 2020 at 7:45 am

    For some reason Excel will not let me count the number of occurrences of the word “Green” it works with Blue, Red, Yellow etc.

    This is what I am using:
    =COUNTIF(Q2:Q100, “Green”)
    Again, the exact same formula works for blue, yellow, and red.

    Additionally using =INDEX($A$2:$A$5, RANDBETWEEN(1,5)) where the list is (Green, Red, Blue, Yellow) the word green shows up as #REF!

    Is there something wrong with the word “Green”?

    Reply
    • Catalin Bombea

      January 29, 2020 at 10:09 am

      Hi,
      Check that cell for invisible chars like trailing spaces, new line chars (CHAR(10), CHAR(13) for example)
      Should work after you remove them.

      Reply
    • Philip Treacy

      January 29, 2020 at 10:11 am

      Hi Autumn,

      I can’t be sure without seeing your data – you could open a topic on the forum and supply your workbook.

      With COUNTIF, are you getting an error or is it just not counting all the occurrences of Green? If it’s the latter, are you sure all the cells actually contain the word Green? Perhaps there are leading or trailing spaces or some other characters in there?

      The INDEX formula is being given a 4 cell range but RANDBETWEEN is returning a number between 1 and 5. So if it returns 5, you’ll get a #REF from INDEX.

      Regards

      Phil

      Reply
  6. Nicolae

    December 31, 2019 at 3:14 pm

    Is there a way that i can use an excel formula to count how many calls are being dialed on my software phone?? Im currently running a call center and i want to keep count of how many calls my agents are making in an hour for 8 hours.

    Reply
    • Catalin Bombea

      December 31, 2019 at 3:23 pm

      Hi Nicolae,
      You can use a formula, but only if you have that data in excel. That software should provide usage logs that can be analyzed in excel, but I cannot say more without seeing how data looks like.
      You can upload a sample file to our forum.

      Reply
  7. Nadim

    October 26, 2019 at 2:35 pm

    I do traning for 8 topics. these training topics for all staff and suppose to expire yearly and show red colour. the problem i have cells dates sep 20 or oct 15 and showing green not expired.

    =COUNTIF(H7:O7,”>”&TODAY()-365)

    Reply
    • Mynda Treacy

      October 27, 2019 at 10:59 am

      Hi Nadim,

      I suspect the dates in those cells might be text, not proper date serial numbers that Excel recognises.

      Mynda

      Reply
  8. Malcolm

    September 18, 2019 at 11:14 pm

    Great information on Countif. I am trying to use it on an attendance sheet to count the number or absences, tardies, sick time etc for example that appear in a referenced ranged for each student. So i have a total tab then i have several sheets for each month. The formula i am using looks like this, =COUNTIF(‘Aug 2019:May 2020’!C2:AG2, “A”). The A refers to absent then i have T for tardy and so on. It works when I select one tab but when i enter a range of tab it it gives me a value error. I tried the SUMPRODUCT(COUNTIIF(INDIRECT but it is not efficient. Any suggestions please?

    Reply
    • Catalin Bombea

      September 19, 2019 at 1:49 am

      Hi Malcolm,
      Sounds like you have dozens of worksheets, that is not an efficient data structure, it will lead to more and more complex formulas.
      It’s best to summarize them with power query in this case.

      Reply
      • Malcolm

        September 19, 2019 at 5:05 am

        Thank you. I am in the process of trying that now. I will let you know how it goes.

        Reply
  9. KJ

    September 4, 2019 at 3:03 am

    HI, I have a question- in your image of “COUNTIF Function”,

    Builder ‘Dave’ did total 13 units.
    Excel C3 – “Dave” Excel D3 – “10”
    Excel C4 – “Dave” Excel D4 – “3”

    How can I show on the excel formula that Dave has completed “13” Units?
    With COUNTIF function, I can only make Dave has completed two (2) jobs, not units…

    Please kindly help!
    Thank you!

    Reply
    • Catalin Bombea

      September 4, 2019 at 3:46 am

      Hi KJ,
      Use SUMIF to add column D based on column C values, not COUNTIF.
      In E3: =SUMIF($D$1:$D$10,C1)
      Copy this down as needed.

      Reply
  10. Rachel

    August 2, 2019 at 6:02 am

    Hello,
    I am hoping you can help me. I have a spreadsheet where each project has its own tab, and then there is another tab labeled June that does some analysis off the 20 project tabs. I have a very large formula, and I’m hoping you have a better and shorter way to display this formula. I am using multiple countif formulas currently.

    Here is the formula:
    =COUNTIFS(‘P1′!$B$14:$B$38,June!A11,’P1’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P2′!$B$14:$B$38,June!A11,’P2’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P3′!$B$14:$B$38,June!A11,’P3’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P4′!$B$14:$B$38,June!A11,’P4’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P5′!$B$14:$B$38,June!A11,’P5’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P6′!$B$14:$B$38,June!A11,’P6’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P7′!$B$14:$B$38,June!A11,’P7’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P8′!$B$14:$B$38,June!A11,’P8’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P9′!$B$14:$B$38,June!A11,’P9’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P10′!$B$14:$B$38,June!A11,’P10’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P11′!$B$14:$B$38,June!A11,’P11’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P12′!$B$14:$B$38,June!A11,’P12’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P13′!$B$14:$B$38,June!A11,’P13’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P14′!$B$14:$B$38,June!A11,’P14’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P15′!$B$14:$B$38,June!A11,’P15’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P16′!$B$14:$B$38,June!A11,’P16’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P17′!$B$14:$B$38,June!A11,’P17’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P18′!$B$14:$B$38,June!A11,’P18’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P19′!$B$14:$B$38,June!A11,’P19’!$L$14:$L$38,June!$B$1)+COUNTIFS(‘P20′!$B$14:$B$38,June!A11,’P20’!$L$14:$L$38,June!$B$1)

    For project one it’s saying to look at the project 1 tab and count how may times the following two conditions are met: impacted group says clients, and the date is 6/3/2019. This formula does work as is. Then do that for every tab and add them together. However, scalability is an issue, as there could be upwards of 50 projects and the formula is only set up for 20. I know Excel has a limit on the number of elements/characters you can use in one formula.

    Can you help me figure out a way to shorten this formula down to a more manageable size? I’ve tried naming ranges which I can’t seem to get to work. Any help would be appreciated.

    Reply
    • Mynda Treacy

      August 3, 2019 at 3:13 pm

      Hi Rachel,

      When I see formulas like this it tells me that the data is in the wrong format and now you’re paying for it with these overly complicated formulas. Please read this tutorial on writing formuals effectively.

      The shorter way is to use Power Query to consoliate the data spread across separate sheets into a single table in a tabular layout. Then you can use a single COUNTIFS formula without any nesting required.

      I hope that points you in the right direction. If you get stuck, we have an Excel Forum where you can post questions and a sample Excel file so we can help you with a specific answer.

      Mynda

      Reply
      • Rachel

        August 7, 2019 at 7:50 am

        Mynda,

        You didn’t know it, but you were answering a question I’ve had for years. You have no idea the amount of time and frustration you’ve saved me. I knew it was inefficient and that I really needed the data combined into one sheet, I just did not know how. Thank you so much, Power Query is exactly what I’ve needed. Really appreciate you taking the time to answer.

        Thank you,
        Rachel

        Reply
        • Mynda Treacy

          August 8, 2019 at 11:56 am

          Glad I could help, Rachel 🙂

          Reply
  11. eman

    July 7, 2019 at 11:03 am

    I was not able to download the file, it says corrupt file.

    Reply
    • Catalin Bombea

      July 10, 2019 at 1:11 pm

      Hi Eman,
      Can you try right clicking the link and save link as?

      Reply
  12. Samantha Kingsley

    July 3, 2019 at 2:11 am

    I am trying to count how many time a certain txt shows up in a table;
    =COUNTIF(F2:F607,”Ready to Submit”) This formula works and gives me an amount however, this one =COUNTIF(F2:F600,”Payment Control”) gives me a 0 result even though it should be giving me a value besides 0. Can’t figure out why it’s not working as it works with the other column no issue.
    Thank you

    Reply
    • Catalin Bombea

      July 3, 2019 at 3:35 pm

      Hi Samantha,
      Check if column F has indeed “Payment Control”, without any extra spaces or invisible chars before or after the text.

      Reply
  13. Stephen

    April 26, 2019 at 6:56 am

    I am using the following formula:

    =COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,”=”&B76,Data!P$2:P$50249,”Freshman”)+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,”=”&B76,Data!P$2:P$50249,”transfer”)+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,”=”&B76,Data!P$2:P$50249,”readmit”)+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,”=”&B76,Data!P$2:P$50249,”Advanced Freshman”)

    To pull attendance information off of a spreadsheet at a college. My formula works perfectly for 2019 and 2018 but when I transfer it to the 3rd column in 2017 it does not provide me with any information (even after I update the dates). Do you know why it would bottom out after the 3rd column? Please let me know as I am definitely curious!

    Reply
    • Mynda Treacy

      April 26, 2019 at 3:53 pm

      Hi Stephen,

      Not without seeing the file. That said, it looks like you could replace this complex formula with a PivotTable.

      If you want to post your question and Excel file on our forum we can help troubleshoot.

      Mynda

      Reply
  14. Farooq

    April 2, 2019 at 9:00 pm

    I want to use “Countifs” for to count no. of receive quantities as per specific size. Can anyone help me

    Reply
    • Mynda Treacy

      April 2, 2019 at 9:35 pm

      Hi Farooq,

      Sure, we’d be happy to help. Please post your question and sample Excel file on our forum where we can provide you with a specific answer.

      Thanks,

      Mynda

      Reply
  15. Carol

    March 23, 2019 at 2:22 am

    Thank you so much Catalin, but that formula doesn’t provide the resolve I was looking for. I just want to drag the formula acrosss rows with only the number “1” changing to “2”, “3”, etc. Any further help is appreciated.

    Reply
    • Catalin Bombea

      March 23, 2019 at 4:06 am

      Hi Carol,
      In this formula:
      =COUNTIF($C3:$C363,Row(A1))
      Row(A1) will return 1 for the first cell with this formula. When you copy down this cell, the row number will increase, next cells will have: Row(A2)-this returns 2, Row(A3)-this returns 3 and so on.
      I guess you did not tested the formula? If you did, should work.

      Reply
  16. Carol

    March 22, 2019 at 8:35 pm

    =COUNTIF($C3:$C363,”1″) how do I copy this formula across rows to read =COUNTIF($C3:$C363,”2″), =COUNTIF($C3:$C363,”3″), etc? It will only copy =COUNTIF($C3:$C363,”1″) across the rows. Thank you.

    Reply
    • Catalin Bombea

      March 22, 2019 at 9:14 pm

      Hi Carol, try this:
      =COUNTIF($C3:$C363,Row(A1))

      Reply
  17. Habib Shaukat

    February 28, 2019 at 4:22 pm

    what should I do to count the same character for example “Y” in the same row of different columns in excel?

    Name Field1 Field2 Field3 Field4 Total Y Total N
    John Y Y N Y 3 1 \\COUNTS FOR JOHN
    Rose N N Y Y 2 2 \\COUNTS FOR ROSE

    Reply
    • Mynda Treacy

      February 28, 2019 at 4:33 pm

      Hi Habib,

      If you use relative references you can enter the first formula and then copy it down. e.g. let’s say your data starts in cell A1, the formula for the first row is:

      =COUNTIF(B1:G1,"Y")

      Mynda

      Reply
  18. Stephen Howe

    February 13, 2019 at 1:26 am

    I hope you can help, I’m looking to count the amount of dates that are Feb in a column, but I cant find the formula (the dates are in a separate sheet).

    Reply
    • Philip Treacy

      February 13, 2019 at 9:54 am

      Hi Stephen,

      You can use SUMPRODUCT, try this

      =SUMPRODUCT((MONTH(Sheet2!A1:A10)=2)*1)

      You’ll need to adjust the range reference of course.

      Regards

      Phil

      Reply
  19. Afia Tehmeen

    February 11, 2019 at 9:31 pm

    I have data for Personal Protective Equipment on one sheet fro all departments(i.e a format copied again and again with different numbers as per requirements of PPEs).I want to know how much shoes we need ,how much masks we need in total.Which command can fulfill this task?

    Reply
    • Mynda Treacy

      February 11, 2019 at 9:46 pm

      Hi Afia,

      It sounds like your data might not be in a tabular format, which may make it difficult to summarize as you describe. Please post your question and sample Excel file on our Excel forum so we can see the layout and help you further.

      Mynda

      Reply
  20. George Sie Williams

    January 6, 2019 at 3:04 pm

    I am using the Countifs formula but I want to return a blank value instead of zero if a certain cell was empty. Could anyone help.

    Reply
    • Mynda Treacy

      January 6, 2019 at 8:16 pm

      Hi George,

      You can use a custom number format to hide zeros returned by COUNTIF.

      Mynda

      Reply
  21. Mark Etchemendy

    October 18, 2018 at 4:42 am

    I have a spreadsheet with a row of dates (M6:M24) that I am trying to count how many dates in the row are before or equal to todays date ($P$3). (I know I don’t need to reference a cell for todays date but want to).

    My formula is: =COUNTIF(M6:M24,”<"&$P$3)

    My formula does what I want except when I apply a filter to a column. The total still shows everything not just what is visible. I think I need to use a SUMPRODUCT formula but don't know how to write it.

    Please help!

    Reply
    • Mynda Treacy

      October 18, 2018 at 9:38 am

      Hi Mark,

      SUMPRODUCT won’t ignore hidden or filtered rows either. In fact nothing will COUNTIF and ignore filtered rows. You’d be best to use a PivotTable. If you’re not sure how to create a PivotTable to count as you describe, please post your question on our Excel forum and upload a sample Excel file and we can show you.

      Mynda

      Reply
  22. Lauren Pettit

    October 6, 2018 at 7:33 am

    How do you find the unique value once you’ve located multiple rows for the same entry on a spreadsheet?

    =IF(COUNTIFS($A:$A,A2,$B:$B,$B2)>1, “Multiple Lines”, “”)

    There are multiple lines because each line can contain one or more indications (this report is for antibiotic use and it’s associated indication). So once the “multiple lines” are identified, how do you then count them as “1” to keep your numbers correct?

    If I have 10 lines for a dose of antibiotics and 4 of those lines are for one unique administration, then the total number of unique doses administered should be 7 vs 10. What other argument or function is needed to acheive this calculation without needing to concatenate my rows into 1 row. I have done this before and it is very time consuming with a couple of helper columns and formulas.
    Thanks for your help!!

    Reply
  23. Lauren

    October 6, 2018 at 5:05 am

    Hello,
    I am working with hospital data. I need to count how often a dose of a specific antibiotic was given and include the indications associated with that order. Some of the orders have multiple indications, so for every administration, there can be multiple lines of data (i.e. 3 lines of data for each specific indication, for each single administration).
    I am able to use the countifs to identify the duplicate rows using multiple columns, but how do I then count each administration as 1 when there may be 3 lines.

    Identify multiple lines =IF(COUNTIFS($A:$A,$A521,$B:$B,$B521)>1, “Multiple Lines”, “”)

    I’ve included a small sample size of my data. There is a unique order ID associated with an administration date and time, then the assigned diagnoses.

    Order ID Admin Date and Time Multiple Rows Indications of Use Default Dispense Code
    93466529 6/19/2018 7:32 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93466694 6/4/2018 20:24 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93466694 6/5/2018 4:19 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93468230 6/4/2018 21:24 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93468230 6/5/2018 5:48 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93471960 6/4/2018 15:41 PERIOPERATIVE PHARMACOPROPHYLAXIS Syringe Premix
    93476190 6/4/2018 22:49 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93476190 6/5/2018 6:00 PERIOPERATIVE PHARMACOPROPHYLAXIS IVPB Premix
    93476196 6/4/2018 14:45 Unit Dose
    93482117 6/5/2018 8:55 Unit Dose
    93482525 6/11/2018 11:31 PERIOPERATIVE PHARMACOPROPHYLAXIS Syringe Premix
    93489593 6/6/2018 15:03 Multiple Lines BACTEREMIA Syringe Premix
    93489593 6/6/2018 15:03 Multiple Lines UTI – UPPER Syringe Premix
    93489593 6/6/2018 21:55 Multiple Lines BACTEREMIA Syringe Premix
    93489593 6/6/2018 21:55 Multiple Lines UTI – UPPER Syringe Premix
    93489593 6/7/2018 6:17 Multiple Lines BACTEREMIA Syringe Premix
    93489593 6/7/2018 6:17 Multiple Lines UTI – UPPER Syringe Premix
    93489593 6/7/2018 14:07 Multiple Lines BACTEREMIA Syringe Premix
    93489593 6/7/2018 14:07 Multiple Lines UTI – UPPER Syringe Premix

    Reply
    • Mynda Treacy

      October 6, 2018 at 5:31 pm

      Hi Lauren,

      Thanks for your question. Please post it and a sample Excel file on our Excel Forum where we can give you a specific solution. It’s a bit tricky here in the comments as it’s difficult to tell where the columns are in your data above.

      Thanks,

      Mynda

      Reply
  24. Joe

    August 21, 2018 at 11:23 pm

    =COUNTIFS(‘Closed Tickets’!$A:$A,”Fee Schedule”,’Closed Tickets’!$Q:$Q,”x”,’Closed Tickets’!$U:$U,”>=4/1/2018″,’Closed Tickets’!$U:$U,”=B4″ (and B4 contains a date), the result returned is only a 0…no error statement. Thank you.

    Reply
    • Mynda Treacy

      August 23, 2018 at 11:02 am

      Hi Joe,

      The date criteria is not specified correctly, it should be:

      ...Closed Tickets'!$U:$U,">="&"4/1/2018"

      Note: the double quotes and ampersand between the >= and date

      Also, the last criteria should simply be B4 like so:

      ...Closed Tickets’!$U:$U,B4)

      Mynda

      Reply
  25. Kritymoy Boroowa

    August 6, 2018 at 6:09 pm

    I want to use CountIFS for a value of TIME
    count of number of cells where time value is less than 00:31:25 mins

    Reply
    • Catalin Bombea

      August 6, 2018 at 8:50 pm

      Hi Kritymoy,
      Try this formula:
      =COUNTIFS(A1:A10,”<"&TIME(0,31,25))

      Reply
  26. susanna

    July 25, 2018 at 11:48 pm

    can someone help with this formula

    =COUNTIFS(L4:L44,x,L4:L44,n)

    I’m trying to get a total number of 2 letters from a column

    Reply
    • Mynda Treacy

      July 26, 2018 at 9:44 am

      Hi Susanna,

      You need to wrap text in double quotes like so:

      =COUNTIFS(L4:L44,"x",L4:L44,"n")

      Mynda

      Reply
  27. Mansoor

    July 23, 2018 at 8:45 pm

    hi,

    i want to use COUNTIF formula in Excel 2007 insted of SUMIF like bellow mention

    =SUMIF(C7:C174, “Faisal”, F7:F174)

    is it posibble?

    Reply
    • Mynda Treacy

      July 23, 2018 at 9:35 pm

      Hi Mansoor,

      Try this:

      =COUNTIF(C7:C174, "Faisal")

      Mynda

      Reply
  28. saneesh

    July 20, 2018 at 2:32 am

    i want to split an excel cell by formula for example
    ” sa-neee-sss-h ” if this is in column A i want to split “sa” “neee” “sss” “h” in C,D,E & F columns. i want a gap in B column for entering a mark in that column.Is it possible??

    Reply
    • Philip Treacy

      July 20, 2018 at 9:19 am

      Hi Saneesh,

      Yes this is possible with the word ‘saneeesssh’ but that is very specific. If you want to split up other words you’ll need a generic formula.

      Can you post on the forum and provide more examples and a sample file.

      Regards

      Phil

      Reply
  29. Geoffrey

    July 10, 2018 at 4:38 am

    I need a program that can generate for me a criteria based on three issues in rows a,b and c for 20 categories as columns such that cell a will have 3 scenarios in each column and row produced to the 20 columns.how many cells contents will i have in total 20x3x3? or 20x3x3x3?
    which branch of maths is this?

    Reply
    • Catalin Bombea

      July 10, 2018 at 1:20 pm

      Hi Geoffrey,
      I guess it’s just applied logic. Not sure what you have as scenarios in A, B, C columns, is it a number, text?
      Try:
      =20&If(Len(A1)>0,”x3″,””)&If(Len(B1)>0,”x3″,””)&If(Len(C1)>0,”x3″,””)

      Reply
  30. mil

    July 8, 2018 at 4:03 pm

    =COUNTIFS(A3:J3;”>=E1″;A3:J3;”<C1")

    Reply
    • Catalin Bombea

      July 10, 2018 at 1:06 pm

      Thanks for the formula. What should we do with it?

      Reply
  31. Alison

    January 26, 2018 at 11:02 am

    Hi,

    I’m trying to create a Countif formula that would count the amount of words that begin with a letter in column B, of those rows, then count if the value in column I is equal to a number. I’ve tried: =COUNTIF(B2:B3517,”A*, I2:I3517, 35) . But it won’t recognize the formula and is giving me an error. Please help!

    Reply
    • Mynda Treacy

      January 26, 2018 at 11:36 am

      Hi Alison,

      Your formula has too many arguments. COUNTIF only has 2 arguments: range and criteria. e.g. it should be something like this:

      =COUNTIF(B2:B3517,”A*”)

      If you’re still stuck, please post your question and sample Excel workbook on our Excel forum here:

      Mynda

      Reply
  32. Shubh

    January 22, 2018 at 7:54 pm

    Hi,
    In my data I have grades {a,b,c,d} in column F and classes 1, 2, 3 in column A. I need to count number of observations with grades a or b and classes 1 or 2 or 3. I have tries this
    SUM(COUNTIFS(F:F,{“a”,”b”}, A:A,{1,2,3}))
    this showes wrong results
    then i have tried this
    SUM(COUNTIFS(F:F,”a”, A:A,{1,2,3}))+SUM(COUNTIFS(F:F,”b”, A:A,{1,2,3}))

    this gives the correct answer.
    What is wrong with the first formula.
    Regards
    Shubh

    Reply
    • Catalin Bombea

      January 22, 2018 at 9:13 pm

      Hi Shubh,
      First formula answers this question:
      In How many rows we have (“a” in column F AND “1” in column A) OR (“b” in column F AND “2” in column A)? (the 3 in second criteria array is useless, as there is no pair value in first array). So “a” is only associated with 1, “b” is associated only with 2, they have to be in the same row.
      Second version says:
      In How many rows we have (“a” OR “b” in column F) AND (1 OR 2 OR 3 in column A)?
      The second formula can be written as:
      =SUM(COUNTIFS(B:B,{“a”,”a”,”a”,”b”,”b”,”b”}, A:A,{1,2,3,1,2,3}))
      Each element from first constants array has only one correspondent value in second constants array, both arrays must have the same size.
      You can set the associations in any combination you want, for example you can find in how many rows there are “a” & 1 OR “c” & 3:
      =SUM(COUNTIFS(B:B,{“a”,”c”}, A:A,{1,3}))

      Reply
      • Shubh

        January 22, 2018 at 10:15 pm

        Thanks a lot Catalin.

        Reply
  33. Phil

    December 7, 2017 at 4:33 am

    Hi Mynda,

    Is it possible to count cells in a range that contain a number, only if a non adjacent cell also contains data? e.g. In the range A1:A10 cells A3,A7 & A10 contain a number and in the range B1:B10 cells B1, B3, B6 & B9 contain data. I want to count the number of cells containing data in A1:A10 but only if there is data in a cell one across and one up i.e. B2, B6 & B9. The answer should be 2. Thanks

    Reply
    • Catalin Bombea

      December 7, 2017 at 3:36 pm

      Hi Phil,
      Your data should start from row 2, not row 1, in order to use this formula:
      =SUMPRODUCT((A2:A11<>“”)*(B1:B10<>“”))
      The ranges should be equal, note that the second has a 1 cell offset.
      Catalin

      Reply
  34. Tom

    November 21, 2017 at 6:47 am

    Hi, I have a question regarding COUNTIFS. The simple boiled down version of what I am trying to do is…I am trying to use COUNTIFS to count the number of entries that the departure date is either blank or in the future (greater than today) and that is marked with an “X” in another column. There are several other renditions in the formula but if I can get this, I can get the rest. So far, I have…

    =SUM(COUNTIFS($C$2:$C$50,{“”;”>”&TODAY()},$E$2:$E$50,”X”))

    Excel won’t let me return out of the formula and highlights the quotation mark following the greater than symbol. Any suggestions as to what I am doing wrong? Thanks for the help.

    Tom

    Reply
    • Mynda Treacy

      November 21, 2017 at 8:51 am

      Hi Tom,

      You can’t have OR criteria in COUNTIFS. Every criteria is considered AND. Instead you can use SUMPRODUCT to hand ORs.

      =SUMPRODUCT((($C$2:$C$50="")+($C$2:$C$50>TODAY()))*($E$2:$E$50="X"))

      You can learn more about SUMPRODUCT here.

      Mynda

      Reply
  35. Marcy

    August 26, 2017 at 4:59 am

    I have a question-
    How can I get this =COUNTIF(E3:G3,”X”) to produce a result of 100% instead of 3% in cell H3? I am trying to get the percentage in cell H3 to change as each of the other cells is populated with “X” Am I using the wrong formula?

    Thanks

    Reply
    • Marcy

      August 26, 2017 at 7:46 am

      Just to add more info- it is now this =COUNTIF(E3:G3,”X”)+100-3 but I want the percent to start at 25% then go up from there as each cell has an X put into it. Right now it starts at 97% since there are only 3 cells its looking at

      Reply
      • Mynda Treacy

        August 26, 2017 at 6:19 pm

        Hi Marcy,

        Please post your question and a sample Excel file on our Excel Forum where we can see your question in context and give you a suitable solution.

        Mynda

        Reply
  36. Yourik Kazarian

    August 8, 2017 at 5:19 am

    Hey There

    I am trying to create a cell in G2 in which whatever value i put in C2,c3,c4, Etc… only counts if the date i put in B2,B3,B4, Etc… has passed.
    Currently this is what i have
    =(COUNTIFS(B2:B9,”<="&TODAY(),C2:C9,"”))
    However, this seems to only give me a value of 1 whenever i put any value in the C column.
    Is there a variation to this formula that will count the exact value i put into C?

    Reply
    • Mynda Treacy

      August 8, 2017 at 9:00 am

      Hi Yourik,

      COUNTIF or COUNTIFS will only count the dates in column B has passed today’s date. It won’t count values in column C. Are you sure you don’t want to SUM values in column C when the date in column B has passed?

      That would be SUMIF like this:

      =SUMIF(B2:B4,">="&TODAY(),C2:C4)

      Mynda

      Reply
      • Brian

        September 27, 2017 at 10:08 pm

        Hi Mynda,
        I’m trying to use a similar formula for a similar situation. What I’m trying to do is count the number of cells that have a date that falls within ” today() and today()+31 “. I’m trying to use
        =COUNTIFS(N13:N22,”>=”&TODAY(),N13:N66,”<="&TODAY()+31) which gives me the result of " #value ". This is based off of ' =COUNTIFS(N13:N22,"<="&TODAY()) " which I used to get one result and that's working just fine. Any guidance would be greatly appreciated. Thank you.

        Reply
        • Catalin Bombea

          September 28, 2017 at 2:58 am

          Hi Brian,
          The ranges should have equal ranges, in the first criteria range you have N13:N22, in the second you have N13:N66. If they have the same size, the formula should work.
          Catalin

          Reply
          • Brian

            September 28, 2017 at 6:42 am

            Wow I can’t believe I messed that up. Changing the 22 to a 66 though is now giving me a quantity of ” 0 “. I have a test worksheet set up so it should populate a 4. below is the adjusted formula along with cells N13 through N22.

            =COUNTIFS(N13:N22,”=&TODAY()”,N13:N22,”=&TODAY()+31″)

            15-Aug-17
            30-Sep-17
            30-Sep-17
            27-Mar-18
            10-Oct-17
            2-Nov-17
            12-Oct-17
            3-Nov-17
            3-Nov-17
            1-Nov-17

            (I’m relatively new to all of this, it took me about 2 weeks of trial and error and google to build up to here. changing cell color based on date, adjust dates with edate, half this formula to get dates that occur after today. 600+ cells 8 worksheets, and each cell a constantly changing date.)

          • Catalin Bombea

            September 29, 2017 at 2:58 am

            Hi Brian,
            It’s important where you put the double quotes.
            =COUNTIFS(N13:N22,”=&TODAY()”,N13:N22,”=&TODAY()+31″)
            Should be:
            =COUNTIFS(N13:N22,”=”&TODAY(),N13:N22,”=”&TODAY()+31)
            If the today formula is wrapped between double quotes, it will no longer be a formula, i’s just the formula name instead of the value used in criteria.

  37. Jerry

    July 5, 2017 at 2:35 pm

    Hi, I will need help.

    Rows/ Column

    A B C

    1) Team Situation Explanation
    2) Alpha Critical Fever
    3) Bravo Critical Breathing Difficulties
    4) Charlie Ok NIL
    5) Charlie Ok NIL
    6) Alpha Critical Fever
    7) Charlie Severe Diarrhea
    8) Bravo Severe Throat pain
    9) Charlie Severe Diarrhea
    10) Alpha Severe Ankle injury
    11) Bravo Severe Cough
    12) Charlie Severe Cough
    13) Alpha Severe Ankle Injury
    14) Bravo Severe Cough
    15) Bravo Severe Diarrhea
    16) Charlie Severe Diarrhea
    17) Charlie Severe Diarrhea
    18) Bravo Severe Throat pain
    19) Charlie Severe Throat pain

    I need to calculate the following below:

    Where X,Y & Z is the number generate from the above data and if the data is repeated, it should count as 1.
    Eg. Charlie Severe Case: Total explanation output should be 3 (throat pain, diarrhea, cough) and should not be 6 (total severe case).

    Alpha Critical Severe Ok
    Total Explanation X Y Z

    Bravo Critical Severe Ok
    Total Explanation X Y Z

    Charlie Critical Severe Ok
    Total Explanation X Y Z

    Do you know to make that formula?

    Reply
    • Catalin Bombea

      July 5, 2017 at 9:24 pm

      Hi Jerry,
      See this file from our OneDrive.
      If this is not what you wanted, please open a new topic on our forum, you will be able to upload there a sample file.
      Catalin

      Reply
  38. Palani K

    June 15, 2017 at 8:09 pm

    Hi
    I have 3 columns
    Col A= Jan, Feb, Mar, etc..
    Col B= Name
    Col C= Good, Excellent, Satisfactory.etc.

    I need to count No.of “Excellent” in Jan, Feb & Mar separately using Countif & if condition.
    Please help me how to use countif formula

    Reply
    • Catalin Bombea

      June 16, 2017 at 2:04 pm

      Hi Palani,
      Try:

      =COUNTIFS(A1:A10,"Jan",C1:C10,"Excellent")
      
      Reply
  39. Jennifer

    June 3, 2017 at 4:22 am

    How can I get a countifS to be blank if the value is 0

    Reply
    • Mynda Treacy

      June 4, 2017 at 1:22 pm

      Hi Jennifer,

      You can add that criteria to your formula e.g.:

      =COUNTIFS(range, "<>"&0)

      Mynda

      Reply
  40. a lakshmi

    April 28, 2017 at 6:18 pm

    how to find missing numbers in 1 to 20000 please tell me excel formulas

    Reply
    • Mynda Treacy

      April 28, 2017 at 7:03 pm

      Try this: https://www.myonlinetraininghub.com/find-missing-numbers-really-fast

      Or this: https://www.myonlinetraininghub.com/finding-missing-numbers-in-a-range-using-vba

      Reply
  41. vlad

    March 29, 2017 at 5:25 pm

    what do i do if i want to count Daves sales value and not the number of time he appeared in the sheet

    Reply
    • Mynda Treacy

      March 29, 2017 at 8:15 pm

      Hi Vlad,

      I think you mean ‘sum’ or add up Dave’s sales value. In which case you can use a SUMIF formula for that.

      Mynda

      Reply
    • Catalin Bombea

      March 29, 2017 at 8:19 pm

      Hi Vlad,
      What do you mean? A count is a count, no matter the column. Counting how many sales values we have for Daves will give the same number as counting how many times Daves is in that list. Maybe you want to add Daves’s values? Try SUMIF instead of COUNTIF. There is also a SUMIFS version.
      Catalin

      Reply
  42. sheilagh romero

    March 28, 2017 at 1:03 am

    What if I want to count everything in ONE column IF it = ‘pass’ OR IF it = FAIL ?
    Example:Column J6:J100 can contain Pass, Fail or can be blank.
    I want to count all the Pass and all the Fail

    I tried:
    COUNTIFS(J6:J100,”PASS”, “FAIL”)

    But it comes up with a count = 0.
    I have 4 = pass and 1 = fail so the count should be 5.
    What did I do wrong?
    thanks!

    Reply
    • Catalin Bombea

      March 28, 2017 at 3:15 pm

      Hi,
      COUNTIFS is used to apply different criteria to multiple columns, each criteria must be met in the same row for that row to be counted. COUUNTIFS works with an AND logic, it will not count a row if the criteria is “PASS” OR “FAIL” it will always consider “PASS” AND “FAIL”, and both those values cannot exist in the same time in the same cell.
      You should use 2 COUNTIF:
      =COUNTIF(J6:J100,”PASS”)+COUNTIF(J6:J100, “FAIL”)
      Catalin

      Reply
  43. Barbara Gonzalez

    January 22, 2017 at 4:44 am

    I need help in creating a formula to count only full shifts my hospitalist are working, and I don’t want the count to include Coverage shifts (phone or sick) how can I get Excel to total up only shifts (7p-7a) and not include BU (sick coverage) or CC (phone coverage) coverage in the totals?

    Reply
    • Catalin Bombea

      January 22, 2017 at 11:56 pm

      Hi Barbara,
      You have to upload a sample file on our forum. It will be very helpful if you prepare a sample data and some manual results, so we can understand exactly you situation.
      Catalin

      Reply
  44. Raj Kumar Sharma

    January 7, 2017 at 5:14 pm

    excel formulas if column d is zero full amount of c, if d is 1, 50% of c, if d is 2, 1/3 of c and if d is 3 or more 0% of c

    Reply
    • Catalin Bombea

      January 9, 2017 at 1:36 am

      Hi Raj,
      Try this one:
      =C1*IFERROR(INDEX({1,0.5,0.333},MATCH(D1,{0,1,2},0)),0)

      Reply
  45. Omer

    October 24, 2016 at 4:57 pm

    Hi,

    I am a basic excel user, I am handling an excel work book in 2016 version, I’ve got a query as follows:

    I have 9 worksheets in an excel book and a specific column in each sheet which displays a series of reference numbers as:

    GAD5-CDC-T2-349-230315-DWG-PP-STR-0114-0
    GAD5-MGM-T2-349-230315-DWG-PP-STR-0114-0
    GAD5-CDC-T2-349-230315-DWG-SD-STR-0114-0
    GAD5-MGM-T2-363-250315-DWG-PP-STR-0119-0
    GAD5-CDC-T2-363-250315-DWG-DD-STR-0119-0
    GAD5-CDC-T2-363-250315-DWG-BD-STR-0119-0
    GAD5-CDC-T2-259-51-050515-DWG-FD-S-0233-00
    GAD5-CDC-T2-259-51-050515-DWG-TD-S-0233-00
    GAD5-MGM-T2-259-51-050515-DWG-LD-S-0233-00
    GAD5-MGM-T3-119-25-DDS-ST-1568-02
    GAD5-MGM-T3-119-25-DDS-RT-1568-02
    GAD5-MGM-T3-119-25-DDS-OT-1568-02

    and so on, Please note: these numbers are not following any sequence. I want to count the number of times the second last series of number is appearing. In this Instance

    0114 is 3 times
    0119 is 3 times
    0233 is 3 times &
    1568 is 3 times

    I want to count this by a formula and get the result in any other cell as a counter. I’ll appreciate if you could help me in this please.

    Reply
    • Mynda Treacy

      October 25, 2016 at 9:58 am

      Hi Omer,

      You can use Text to columns to extract the second last series into another column. From there you can use PivotTables to count the instance of each number.

      If you get stuck please post your question with a sample file on our Excel Forum so we can show you what we mean.

      Reply
      • syediomer

        October 25, 2016 at 6:56 pm

        Hi Mynda,

        Thank you for your kind response. I have posted my question to Excel Forum, with the sample file, your further help will be much appreciated. Many Thanks.

        https://www.myonlinetraininghub.com/excel-forum/excel/text-to-columns-pivot-tables#p1317

        Reply
  46. Syed

    October 5, 2016 at 5:11 pm

    Hi I am a basic excel user, I am using excel 2016, my question is:

    I am using the sumproduct formula to get my results the formula is
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(AR9:AR4148,ROW(AR9:AR4148)-MIN(ROW(AR9:AR4148)),,1))*(AR9:AR4148=”A”))

    however in another column i want to use the same formula and I wan this to calculate the same way but the number not text, but it is not giving any result. For example I want the aforementioned formula to work like this
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(AR9:AR4148,ROW(AR9:AR4148)-MIN(ROW(AR9:R4148)),,1))*(AR9:AR4148=”1″))

    But it is not giving the correct answer, can you please help me.

    Reply
    • Catalin Bombea

      October 6, 2016 at 2:55 pm

      Hi Syed,
      (AR9:AR4148=”1″) will assume that the column is formatted as text, it’s searching for text strings, not numbers (when a number is between double quotes, it’s a text, not a number). If you have numbers, and the column is formatted as numbers, simply use:
      (AR9:AR4148=1) (without double quotes). Make sure you have the same data type in the formula and in that column. Or you can force that column to convert it to text, by adding a zero length string to that column:
      (“”&AR9:AR4148=”1″) This way, both sides of the equation will have the same data type.

      Reply
  47. Syed

    September 26, 2016 at 10:55 pm

    Hi I am a basic excel user, I am using excel 2016, my question is:

    I am using the formula =IF(X4030=””,TODAY()-W4030) to get the result but if the cell W4030 is blank the result is some strange value, I want excel to display nothing if the cell W4030 is blank, how can i fix this. Please help. Many Thanks

    Reply
    • Mynda Treacy

      September 27, 2016 at 2:30 pm

      Hi Syed,

      Just change your formula slightly:

      =IF(X4030="","",TODAY()-W4030)

      Mynda

      Reply
  48. Syed

    September 20, 2016 at 3:55 pm

    Hi I am new Excel user, I have an excel sheet in which 2 columns are dates 1 is date item is received and the other one is the date when item was sent back with comments, I want to have a third column which tells how many days the item was sitting with us, I am doing it in a simple way =W225-U225 by this i get my desired result, my concern is, a cell in W column will be empty till item is not responded and if it is empty the cell with the formula is showing some strange numbers. how can I fix this.

    Reply
    • Mynda Treacy

      September 20, 2016 at 4:33 pm

      Hi Syed,

      You can use IF to check whether the second date field is empty. If it’s empty you can tell Excel to return blank, otherwise do the calculation. Here is an example formula:

      =IF(B1="","",B1-A1)

      A1 contains your received date and B1 contains the date the item was sent back.

      Mynda

      Reply
      • Syed

        September 21, 2016 at 5:06 pm

        many thanks Mynda!

        Reply
  49. Darshan Patil

    August 11, 2016 at 12:29 pm

    Very good information to learn exel

    Reply
  50. Joe

    April 13, 2016 at 2:17 am

    Outstanding explanation, the “COUNTIFS” functions without any problems. However I need the formula to go one step further. I’m searching in two different columns (ranges) and have specified two different criterion, to this point no problems. The last and final step is to divide by a specified cell, if the cell has a “0” then I get the dreaded “#DIV/0”. Is there any way to get rid of the error “#DIV/0”?

    =COUNTIFS($M$8:$M$27,$B$74)/$B$8

    =COUNTIFS($M$8:$M$27,$B$74,$N$8:$N$27,$B$75)/$BP$7

    In the above examples, both produce “#DIV/0” if the last cell (B8 and BP7) have a “0” in the cell. Understand I can’t divide by 0, is it possible to get rid of “#DIV/0?

    Reply
    • Catalin Bombea

      April 13, 2016 at 4:52 am

      Hi Joe,
      You can Simply wrap your functions with the IFERROR function, then choose what you want to return in that case:
      =IFERROR(COUNTIFS($M$8:$M$27,$B$74,$N$8:$N$27,$B$75)/$BP$7,”Error Message here, or a number”)
      Cheers,
      Catalin

      Reply
  51. Ruth

    March 31, 2016 at 5:55 am

    Hi,

    Can you help with a formula please. I need to use conditional formatting to highlight cells when a number is repeated more than 3 times, where in another cell is the word No.

    I have managed the first part of the formula as =COUNTIF(A:A, A1,) > 3 but I can’t combine it with the 2nd condition. Below is an example of the data I’m using

    Staff Number Deselected
    95486245 Yes
    36874592 No
    48596314 No
    95486245 No
    36874592 Yes
    48596314 No
    95486245 No
    95486245 No
    36874592 Yes
    36874592 Yes

    So only the staff number 95486245 meets the criteria of appearing more than 3 times with ‘No’ in the deselected column.

    Thanks

    Ruth

    Reply
    • Catalin Bombea

      April 1, 2016 at 1:30 am

      Hi Ruth,
      You can use COUNTIFS, you can set lots of conditions this way:
      =COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,”No”)
      Catalin

      Reply
  52. John Collins

    March 31, 2016 at 1:35 am

    GREAT EXPLANATIONS BUT THESE DON’T DO WHAT I WANT. I HAVE A 15X2 NUMERICAL TABLE IN WHICH FOR EACH VERTICALLY ADJACENT PAIR OF CELLS, I NEED TO COMPARE THE VALUE OF THE LOWER CELL WITH THAT OF THE UPPER CELL. IF LESS THAN THEN COLOUR LOWER CELL GREEN, IF EQUAL TO THEN YELLOW, IF MORE THAN THEN GREEN. (THE COLOURS COULD BE FONT COLOURS OR FILL COLOURS). ANY HELP YOU CAN GIVE ME WOULD BE VERY GRATEFULLY RECEIVED.

    Reply
    • Catalin Bombea

      April 1, 2016 at 1:38 am

      Hi John,
      You have to take a look at this article: excel-conditional-formatting-with-formulas, you have to setup 3 conditional formatting rules, (one for each color), with very simple formulas: =a2=a1 for one color, =a2a1 for the third color.
      Cheers,
      Catalin

      Reply
  53. Alyssa Pease

    March 9, 2016 at 3:07 am

    I’m currently using the following DATEDIF formula to run a day counter:
    =DATEDIF(D2,TODAY(), “d”)
    It works fine but I’d like to combine it with a COUNTEDIF formula so that the total will stop adding once another field is closed out, i.e. I have final date in another cell, and once that date is filled out I want the counter to stop. Does that make sense?

    Reply
    • Catalin Bombea

      March 9, 2016 at 2:54 pm

      Hi Alyssa,
      Hard to tell without seeing your data structure and a sample of a desired result.
      Can you please upload a sample file to our Help Desk? (create a new ticket) It will be easier to provide a personalized answer.
      Cheers,
      Catalin

      Reply
  54. John R

    February 10, 2016 at 6:43 am

    Very helpful in understanding the countif and countifs functions. Thank you. However I’m trying to use the countif function and want to increment the criteria by one for each cell that I paste the formula in. Example: A1 has 2 in it, A2 has 2 in it, A3 has 1 in it. B1 has this foumula =COUNTIF(A$1:A$3, 1) in it. B2 has this formula =COUNTIF(A$1:A$3, 2) in it. Etc.

    Can you help me with this?

    Reply
    • Catalin Bombea

      February 10, 2016 at 2:58 pm

      Hi John,
      The easiest way is to use the ROW function, ROW(A1) will return the row number of the reference, use relative reference for the row number and it will increease when you copy down the formula:
      =COUNTIF(A$1:A$3, ROW($A1))
      If you want to copy the formula to the right, then use COLUMN function instead of ROW (column reference should be relative now):
      =COUNTIF(A$1:A$3, COLUMN(A$1))
      Catalin

      Reply
  55. Suresh N

    January 26, 2016 at 1:18 am

    I am using three criterias to be matched and then out of them need a counts between date range. I have below formula which gives one count considering matched items for three criteria and another for counts between range. But I am looking for ciombined results from this.
    =COUNT(IF(Sheet1!$H$2:$H$164=Sheet2′!S$2,IF(Sheet1!$G$2:$G$162=’Sheet2′!$Q3,IF(Sheet1!$B$2:$B$164=’Sheet2′!$P$2,sheet1′!$A$2:$A$173))))
    =countifs(Sheet1!$K$2:$K$162,”>=01/01/2016″,Sheet1!$K$2:$K$162,”<=01/31/2016")
    From the first formula we get few counts after that I want to filter them with between range.
    how do I can combine this formula
    Hope I will get results for it

    Reply
    • Catalin Bombea

      January 26, 2016 at 5:40 pm

      Hi Suresh,
      You can combine multiple criterias for a count using SUMPRODUCT:
      =SUMPRODUCT((Sheet1!$H$2:$H$164=Sheet2!S$2)*(Sheet1!$G$2:$G$162=Sheet2!$Q3)*(Sheet1!$B$2:$B$164=Sheet2!$P$2)*(Sheet1!$K$2:$K$162>=”01/01/2016″)*(Sheet1!$K$2:$K$162<="01/31/2016")) Catalin

      Reply
  56. Archie

    November 18, 2015 at 3:28 pm

    Hi!

    How about if I will count the Units of the Builder in the same date or month?

    Thank you,
    Archie

    Reply
    • Catalin Bombea

      November 18, 2015 at 4:20 pm

      Hi Archie,
      You can try this:
      =COUNTIFS(A2:A7,”>=01/03/2008″,A2:A7,”<01/04/2008",C2:C7,"Dave")
      This will count only entries for Dave, in March.
      Catalin

      Reply
  57. Dayan Montano

    November 18, 2015 at 3:38 am

    Hi,

    How can I modify the Countifs function to count the number of populated cells regardless of the value in criteria range1? I have multiple Customers and I just want to count them if criteria range2 contains a “D1″ code.

    =Countifs($C$2:$C$7,???,$B$2:$B$7,”D1”)

    Thanks

    Reply
    • Catalin Bombea

      November 18, 2015 at 4:46 am

      Hi Dayan,
      If you want a single criteria, use COUNTIF instead of COUNTIFS:
      =Countif($B$2:$B$7,”D1″)
      Catalin

      Reply
      • Dayan Montano

        November 18, 2015 at 5:33 am

        Thank you Catalin! I was probably not clear.

        If there is a customer name present in range1, I need to count it regardless of who the customer is, but only if range2 has the code D1. I found the following code for non-blank cells [“”&””].

        =COUNTIFS(Summary!U:U,””&””,Summary!Q:Q,”D1″)

        However my range1 is populated via a VLOOKUP and I think it’s counting even if the cell is blank. I am using the IFERROR(VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]),””) function to populate it. Any tips on how to fix it?

        Regards,

        Reply
        • Catalin Bombea

          November 18, 2015 at 4:12 pm

          You Should try this:
          =SUMPRODUCT((Summary!U:U<>“”)*(Summary!Q:Q=”D1″))
          It’s more reliable than COUNTIF.
          Cheers,
          Catalin

          Reply
  58. Archie

    October 28, 2015 at 5:56 pm

    Hi!

    In your formula it shown only how many names for Brian’s and name of Brian in the South. Please I want to know the formula to calculate the Units of Mr. Brian which I manually count is 13.

    Thanks

    Reply
    • Catalin Bombea

      October 29, 2015 at 2:52 pm

      Hi Archie,
      Try: =SUMIF(C2:C7,C3,D2:D7) where C2:C7 is the criteria range, C3 has the criteria, and D2:D7 is the range to sum. Keep in mind that the criteria range and the range to sum must have the same number of rows.
      Cheers,
      Catalin

      Reply
  59. Shankar

    September 25, 2015 at 7:30 pm

    this formula pulling up a wrong data.. Do you have any work around to resolve this.

    Reply
    • Mynda Treacy

      September 25, 2015 at 7:59 pm

      Hi Shankar,

      Not without seeing your data and formula. If you want to raise a Help Desk ticket and upload your workbook we can take a look.

      Mynda

      Reply
  60. Joshua

    July 31, 2015 at 7:08 am

    This has really helped me. And i think im getting the hang of it but a little stuck.

    Using your “keeping it simple” outline above can you help me with this?

    =COUNTIFS(count the VALUE of cells in column D if, in Column C they contain ‘Larry’ and, if in column A, they are also for the month of MAY)

    Using your image above, lets pretend for a moment Larry sold 10 units on May 5th and 8 units on May 22, the Answer in this statement would be 18

    Thoughts?

    I tried this
    =COUNTIFS(Sheet1!K:K,”6/30/2015″,Sheet1!B:B,”Larry”)+COUNTIFS(Sheet1!E:E,”=>1″)
    But that just counts all 6/30/2015 and all larrys.

    Reply
    • Mynda Treacy

      July 31, 2015 at 1:04 pm

      Hi Joshua,

      If you want to add up the units then you need a SUMIFS formula like this:

      =SUMIFS(D2:D7,C2:C7,"Larry",A2:A7, ">=01/05/2015",A2:A7,"<=30/05/2015")

      SUMIFS is explained here and SUMIFS referencing dates is explained here.

      I hope that helps.

      Kind regards,

      Mynda

      Reply
  61. Kate Alderley

    July 20, 2015 at 11:44 pm

    I’ve used the steps above to try and set up a simple overview sheet but I get an error:

    A value used in the formula is of the wrong data type

    The formula is:

    =COUNTIFS(Sept!D3:D500,”1st”, Sept!I3:L500,”Offer Accepted”)

    The data in the cells I’m looking at are all formatted as ‘general’

    Reply
    • Catalin Bombea

      July 22, 2015 at 2:56 am

      Hi Kate,
      The COUNTIFS function expects that each additional range must have the same number of rows and columns as the criteria_range1 argument (The ranges do not have to be adjacent to each other). The problem is that in your formula, criteria_range1 has 1 column D3:D500, but the second criteria_range2 has 4 columns: I3:L500. You have to add new criterias for each column: =COUNTIFS(Sept!D3:D500,”1st”, Sept!I3:I500,”Offer Accepted”, Sept!J3:J500,”Offer Accepted”, Sept!K3:K500,”Offer Accepted”, Sept!L3:L500,”Offer Accepted”)
      Note that only if each cell in I:L columns contains the criteria: ”Offer Accepted” will be counted. For example, if D3 contains “1st”, the row will be counted only if I3, J3, K3 and L3 contains ”Offer Accepted”.
      If you want a count if D3 contains “1st” and at least one of the I3, J3, K3, L3 cells has the second criteria, you need another approach, the countifs formula will not work.
      Cheers,
      Catalin
      Cheers,
      Catalin

      Reply
  62. Beric Boxall

    July 5, 2015 at 6:55 am

    I am trying to add exceptions to my formula =COUNTIF(G9:G45,”Yes”)/COUNTIF(G9:G45,””) but as the first exception is to ignore blank cells the second needs to be COUNTIF(G9:G45,”N/a”)

    This formula does not allow me to include the second exception.

    Reply
    • Beric Boxall

      July 5, 2015 at 7:14 am

      That should be =COUNTIF(G9:G45,”Yes”)/COUNTIF(G9:G45,””) and the next statement to add is COUNTIF(G9:G45,”N/a”)

      Reply
      • Mynda Treacy

        July 5, 2015 at 8:29 pm

        Hi Beric,

        I’m not following you, sorry. Is COUNTIFS what you’re after? If not please send us an example file via the help desk so we can see your question in context and an example of the desired result.

        Thanks,

        Mynda

        Reply
        • Beric Boxall

          July 6, 2015 at 3:27 am

          Unsure why it would not allow me to add the full formula, I was trying to include the for exceptions however I have now resolved this simply by taking the blanks away and no longer needing to account for them. Sometimes the obvious answer is just too obvious.

          Reply
          • Mynda Treacy

            July 6, 2015 at 9:01 am

            Hi Beric,

            It’s probably the HTML formatting interpreting the formula characters as HTML.

            Glad you figured it out. Next time try wrapping your formula in Pre tags e.g. <pre>=IF(etc….)</pre>

            Mynda

  63. Deronda

    June 28, 2015 at 11:39 pm

    My problem is simple but I cant seem to get it right.
    I have a column of Names and a column with Y or N
    i’m simply trying to create a formula that gives me the % of Y for that particular name

    =(COUNTIFs(C2:C16,”L.Woods”, (X2:X16,”Y”)

    I’m getting an error ..what am I doing wrong?

    Signed,
    Beginner Excel user

    Reply
    • Mynda Treacy

      June 29, 2015 at 11:50 am

      Hi Deronda,

      You need 2 COUNTIF formulas; the first counts the Y’s for that name and the second counts all the Y’s. You then divide one by the other to get the % of Y’s for that name:

      =COUNTIF(C2:C16,"L.Woods")/COUNTIF(C2:C16,"Y")

      Your formula references a range X2:X16. I’m not sure what is in this column or why it’s in your formula. If my formula above doesn’t work then please send us a sample file via the Help Desk so we can see your question in context.

      Thanks,

      Mynda

      Reply
  64. Sanjiv Patel

    May 28, 2015 at 6:59 am

    I am counting for instances where status is either Approved OR Implemented, AND Date Received by CAFGB is less than or equal to end of the month of a date in header in row 48 (which is 1st of that month) AND Validation Start Date is either NULL OR in the following month. I am using curly braces for OR condition – along with SUM.

    Following works, but it is too long:

    =SUM(COUNTIFS(Data[[#Data],[CAFGB Status]:[CAFGB Status]],{“Approved”,”Implemented”}, Data[[#Data],[Date Received by CAFGB]:[Date Received by CAFGB]],”<=" & EOMONTH(AG$48,0), Data[[#Data],[Validation Start Date]:[Validation Start Date]], "")) + SUM(COUNTIFS(Data[[#Data],[CAFGB Status]:[CAFGB Status]],{"Approved","Implemented"}, Data[[#Data],[Date Received by CAFGB]:[Date Received by CAFGB]],"” & EOMONTH(AG$48,0)))

    I was thinking that it should work with following shortened expression, but it doesn’t:
    =SUM(COUNTIFS(Data[[#Data],[CAFGB Status]:[CAFGB Status]],{“Approved”,”Implemented”}, Data[[#Data],[Date Received by CAFGB]:[Date Received by CAFGB]],”” & EOMONTH(AG$48,0)}))

    What am I doing wrong in the shorter version? What can I do to make it work?

    Reply
    • Catalin Bombea

      May 28, 2015 at 2:42 pm

      Hi,
      Can you please upload a sample workbook with your data structure used in this formula? It will be a lot easier to understand the situation and test the solution.
      Until then, you can try a general solution:
      =SUMPRODUCT(($A$1:$A$10={"Approved","Any"})*($B$1:$B$10<=EOMONTH(TODAY(),0))*C1:C10)
      Use our Help Desk system.
      Cheers,
      Catalin

      Reply
  65. Rachana

    November 13, 2014 at 1:36 am

    Hi Mynda,

    I using a formula =COUNTIFS(‘6-26-52 Weeks’!G4:G52,”=Mike”,’6-26-52 Weeks’!J4:J52,”=April”,’6-26-52 Weeks’!Q4:Q52,”=Y”‘6-26-52 Weeks’!R4:R52,”=True”)

    It doesnt count “Y” from Col Q of all “Mike” in col G with conditions that J should have “April” and Col R should be “True”. It doesnt even throw an error in excel either.

    please can you advise.

    Regards,
    Rach

    Reply
    • Catalin Bombea

      November 13, 2014 at 1:49 am

      Hi Rachana,
      Please upload a sample file so we can analyze it. Use our Help Desk to send us the file.
      Cheers,
      Catalin

      Reply
      • Rachana

        November 13, 2014 at 2:49 am

        Hi Catalin,

        I am using below formula to count all Ys of Mike in another sheet with conditions to check if Months column showing “April” and T/F column showing True. Please can you advise.

        =COUNTIFS(’6-26-52 Weeks’!G4:G52,”=Mike”,’6-26-52 Weeks’!J4:J52,”=April”,’6-26-52 Weeks’!Q4:Q52,”=Y”’6-26-52 Weeks’!R4:R52,”=True”)

        01/04/2014 31/03/2015
        Name Target date Completion date Target Met Months T/F
        Mike 01/01/2014 20/12/2014 Y January True
        Mike 01/02/2014 02/02/2014 N February False
        Jenny 01/03/2014 25/02/2014 Y March False
        Jenny 31/03/2014 01/04/2014 N March True

        Regards,
        Rach

        Reply
        • Catalin Bombea

          November 13, 2014 at 3:36 am

          Hi Rachana,
          The Months column is in fact a date value, formatted as “mmmm” to show only the month name. You cannot compare a value to a text string.
          Use
          =SUMPRODUCT((A3:A6="Mike")*(D3:D6="Y")*(F3:F6="True")*(MONTH(E3:E6)=1))
          to convert dates to months numbers.
          Catalin

          Reply
  66. janine

    September 20, 2014 at 5:13 am

    I have a spreadsheet where Column B contains the numbers either 1, 2, or 3. I want to count the rows that contain a “1” in Column B if there is a corresponding “x” in Column C or D or E and I only want to count it once (because it could contain an x in C and/or D and/or E). Can someone help me?

    Reply
    • Mynda Treacy

      September 21, 2014 at 10:06 am

      Hi Janine,

      You can use this formula:

      =SUMPRODUCT((B2:B7=1)*B2:B7*SMALL(IF(C2:E7="X",1,""),1))

      Entered with CTRL+SHIFT+ENTER as it’s an array formula.

      Kind regards,

      Mynda

      Reply
    • janine

      September 23, 2014 at 12:18 am

      Thanks for the help– but when I input the formula I get the error “a value used in the formula is of the wrong data type”

      janine

      Reply
      • Mynda Treacy

        September 23, 2014 at 7:02 am

        Hi Janine,

        Perhaps your ‘numbers’ in column B are actually text. You can tell by trying to SUM them. If you get an error then they’re text. You’ll need to convert them to numbers.

        If that doesn’t work then I’ll need to see your workbook which you can send me via the Help Desk.

        Kind regards,

        Mynda

        Reply
  67. ravikumar

    April 16, 2014 at 7:52 pm

    wow its super

    Reply
    • Mynda Treacy

      April 16, 2014 at 8:13 pm

      Cheers, Ravikumar 🙂

      Reply
  68. Robert

    March 12, 2014 at 2:14 pm

    Hi,

    I want to use countifs with a date range but subtract 7 days from the date range use to select my occurrencies.

    Thank you for your time 🙂

    Reply
    • Catalin Bombea

      March 12, 2014 at 5:05 pm

      Hi Robert,
      Can you upload a sample of your data, it will be useful for us to understand your situation. You can use our Help Desk: https://www.myonlinetraininghub.com/help-desk
      Catalin

      Reply
  69. Eric

    March 6, 2014 at 12:12 pm

    I’m so glad you still check this for comments, I have a weird issue.

    I’m using =COUNTIFS($O$1:$O$130,”=1*”,$R$1:$R$130,”Yes”) and it’s being picky about the O1:O130 range.

    If the value in O includes a letter e.g. 1711A it will get counted, however if the value in O does not contain a letter e.g. 1711 it does not get counted! What am I missing?

    Reply
    • Catalin Bombea

      March 6, 2014 at 2:08 pm

      Hi Eric,
      1711A is interpreted by excel as a text string. 1711 is seen as a number, with numbers you have to deal in a different way.
      Try:
      =SUMPRODUCT((LEFT($O$1:$O$130,1)=”1″)*($R$1:$R$130=”Yes”))
      It will work on both text and numbers.
      Catalin

      Reply
  70. Kyle

    March 2, 2014 at 6:55 pm

    Hi,
    My cells contain one of the four things listed below
    1) random dates
    2) the letter N
    3) the letter I
    4) N/A
    I’m trying to write a formula that will give me a percentage of the cells that contain dates out of the total number of cells that I have selected. I would also like to exclude from the percentage listed above all cells containing N/A. How would l do that?

    Reply
    • Catalin Bombea

      March 3, 2014 at 4:06 am

      Hi Kyle,
      Assuming that your range is in column A, starting from row 1, use this formula in cell B1:
      =CELL(“format”,A1) and copy it down as needed.
      The formula:

      =COUNTIF(B1:B8,"D*")/(COUNTA(A1:A8)-COUNTIF(A1:A8,"#N/A"))

      will give you the percentage needed.
      Catalin

      Reply
      • Kyle

        March 3, 2014 at 5:00 am

        The criteria listed above is all listed in the same column. So the individual cell in the column has one of the four criteria listed above. Here is a copy of one of the columns:

        Course title

        N/A
        N/A
        N/A
        N/A
        N
        N
        9-Dec-13
        N/A
        N/A
        N/A
        N/A
        N
        N/A
        N/A
        N
        N/A
        N/A
        N
        N
        N/A
        N/A
        N/A
        N
        N
        N/A
        N/A
        N
        N/A
        N/A
        N/A
        N
        N
        N/A
        N/A
        N/A
        N
        N/A
        N/A
        N/A
        N
        N
        N/A
        N/A
        N/A

        Reply
        • Catalin Bombea

          March 3, 2014 at 11:16 pm

          Hi Kyle,
          Please try the formulas already provided. If the problem is not solved, use our Help Desk System: https://www.myonlinetraininghub.com/help-desk to create a sample file and to provide more details on this problem.
          Thank you,
          Catalin

          Reply
  71. Nataly

    February 12, 2014 at 3:31 am

    Hello I’m doing the countif formulas correctly.
    I could swear to you but it’s telling me that the correct answer is 0.
    And I know it’s not, because there are certain products, there’s a few but it keeps telling me it’s 0.
    I have no clue why, but do you think you can help me with this? Please.

    Reply
    • Mynda Treacy

      February 12, 2014 at 8:50 am

      Hi Nataly,

      Sure we can help you. Please send your file with the offending formula to us via the Help Desk.

      Thanks,

      Mynda.

      Reply
  72. Sanjiv Hede

    October 3, 2013 at 8:35 pm

    Very Useful ones

    Thanx & Kind Regards

    Reply
    • Mynda Treacy

      October 4, 2013 at 11:17 am

      You’re welcome, Sanjiv 🙂

      Reply
  73. Jo Case

    August 28, 2013 at 6:34 pm

    Hi there

    I find your tutorials really helpful. However I’m stuck on a problem that I can’t seem to find a solution to. In the first example above consider you want to know the number of times Doug sold 8 units in the month of January. I have a similar table where I want to know how many decisions a staff member has made in a certain month. The month is found in the same date structure as in your table. As soon as I’m adding the MONTH function in, I’m struggling to find the solution. I’ve tried arrays, and the straight COUNTIFS. Any suggestions? Cheers, Jo

    Reply
    • Mynda Treacy

      August 28, 2013 at 7:30 pm

      Hi Jo,

      You need to give your COUNTIFS a date range that incorporates the month you’re counting. Here is a tutorial on SUMIFS using dates as critiera. The logic for the date range is the same for COUNITFS.

      Let me know if you get stuck.

      Kind regards,

      Mynda.

      Reply
  74. BAM

    June 5, 2013 at 3:22 am

    Hello

    I am working in a table with 9 columns and 50 rows. All cells in the table have COUNTIFS formulas based on the column identifier. I get accurate results from cells in all columns but the last 2 (8 and 9) and I’m not sure why. I feel that the formulas are designed correctly but only “0” is returned in all cells in the last two columns.

    Is something wrong with the formula? Is there a limit on COUNTIFS forumlas that can be applied in a worksheet? Have you seen/heard of this before?

    Column 1= COUNTIFS(Input!$P$4:P30,”=”&$C$6,Input!$G$4:G30,”=”&B9)

    Column 4=COUNTIFS(Input!$P$4:P30,”=”&$F$6,Input!$G$4:G30,”=”&B9,Input!$AC$4:AC30,”=”&”Major Issue”)

    Column 6= COUNTIFS(Input!$G$4:G30,”=”&B9,Input!$AC$4:AC30,”=Not Applicable-Issue has insignificant impact”)

    Column 8= COUNTIFS(Input!$G$4:G30,”=”&B9,Input!$AC$4:AC30,”=Not Applicable — Issue has no impact”)

    Column 9 = =COUNTIFS(Input!$G$4:G30,”=”&B9,Input!$AC$4:AC30,”Ÿ=Not Applicable — Other”)

    Reply
    • Mynda Treacy

      June 5, 2013 at 8:01 pm

      Hello again Tina,

      Looking at your formulas I would expect Columns 6, 8 and 9 to return zero. The last criteria in each formula is stated with an = inside the double quotes. Now it’s possible this is correct but I suspect not and this is why:

      Your formula in column 6 says count the values in G4:G30 that = what ever is in cell B9 AND where the values in AC4:AC30 contain the text =Not Applicable-Issue has insignificant impact

      i.e. the equals sign is in front of the text Not Applicable-Issue has insignificant impact like this:

      =Not Applicable-Issue has insignificant impact

      I suspect the formula should be more like this:

      =COUNTIFS(Input!$G$4:G30,"="&B9,Input!$AC$4:AC30,"Not Applicable-Issue has insignificant impact")

      If that’s not the problem, send me the workbook via the help desk and I’ll take a look.

      Kind regards,

      Mynda.

      Reply
  75. Tina

    June 5, 2013 at 12:00 am

    Hello-

    I have a table with 9 columns and 50 rows. All cells have countifs formulas that accurately represent the reference data with the exception of the last 2 columnns. The last 2 columns are not pulling in any data even though the countifs formulas are sturectured exactly the same.

    Is there a column limit per worksheet? Am I doing something wrong?

    Reply
    • Mynda Treacy

      June 5, 2013 at 7:54 pm

      Hi Tina,

      Perhaps you can send me the workbook via the help desk so I can see the data you’re working with.

      Kind regards,

      Mynda.

      Reply
  76. Haitham

    May 23, 2013 at 4:42 pm

    HI
    is it possible to use a function for count all the cells in a column except the cells those are “empty and have a dates”

    Thanks

    Reply
    • MikeBanawa

      May 23, 2013 at 8:09 pm

      Hello Haitham,

      Yes it is possible. You can use a combination of count formulas for your conditions.

      But first of all, does your data contain only letters or does it have numbers as well?

      I’ll assume your data contains only: Empty cells, Dates and Words/letters/Alphanumeric

      Let’s say that all of your data are in Column A1 to A10:

      =COUNTA(A1:A10) will count the number of cells in a range that is not empty
      =COUNT(A1:A10) will count the number of cells in a range that contains numbers.

      Since dates are numbers and COUNTA will not count empty cells you can combine both formulas by using a simple subtraction:

      =COUNTA(A1:A10)-COUNT(A1:A10)

      This formula will return the number of cells that only contains letters.

      you can also experiment on the formula =COUNTBLANK which literally counts blank cells in a range.

      If you want a bit more detailed explanation on the mentioned formulas, we have a blog post about Excel COUNT, COUNTA and COUNTBLANK Functions. Just follow this link: https://www.myonlinetraininghub.com/excel-count-counta-and-countblank-functions

      Hope this Helps. Thanks!
      Mike

      Reply
      • Haitham

        May 23, 2013 at 9:18 pm

        Thankd for your efforts!

        Reply
  77. Jenny

    May 15, 2013 at 9:47 am

    Very helpful page – Thank you! However, I do have a question…

    In cell [B12] I have the formula: =COUNTIF(B2:B8, A10)
    so count, if cells within the range B2-B8 (then C2-C8 etc) contain the value in A10, (then A11, then A12).

    I want to copy this formula across and down, so:
    [B13]=COUNTIF(B2:B8, A13) then [C13]=COUNTIF(C2:C8, A13) etc…
    [B14]=COUNTIF(B2:B8, A14) then [C14]=COUNTIF(C2:C8, A14) etc…
    [B15]=COUNTIF(B2:B8, A15) then [C15]=COUNTIF(C2:C8, A15) etc…

    However, when I copy across, the vertical range is correct but the reference cell also increments by one column:
    [C13]=COUNTIF(C2:C8, B13) then [C13]=COUNTIF(D2:D8, C13) etc

    How do I rectify this? It will be so long winded to type these in manually as I have a few to reference.

    Reply
    • Jenny

      May 15, 2013 at 9:55 am

      OMG Sorry – I think I misunderstood the $ thing – I tried it and it works. Fiddly but I am not sure Excel will copy correctly in both directions. I may just have to use =COUNTIF(B2:B8,$A$10) and =COUNTIF(B2:B8,$A$11) etc for all rows before copying across… Sorry to have bothered you!

      Reply
      • Mynda Treacy

        May 15, 2013 at 7:37 pm

        No problem, Jenny. Glad you figured it out yourself 🙂

        Reply
  78. Surender

    May 4, 2013 at 5:07 pm

    if there is multiples states and in adjacent is cities name how can i count in excel that one state have how many cities

    Reply
    • Mynda Treacy

      May 4, 2013 at 7:59 pm

      Hi Surender,

      Just count the number of instances of states.

      Assuming the cities aren’t repeated/duplicated anywhere.

      Kind regards,

      Mynda.

      Reply
  79. Paul

    April 16, 2013 at 9:47 am

    This was really well-explained. Thanks very much for your effort!

    Reply
    • Carlo Estopia

      April 16, 2013 at 3:03 pm

      Hi Paul,

      Thanks, on behalf of Mynda.

      Cheers,

      CarloE

      Reply
  80. Cary

    April 13, 2013 at 6:10 am

    Hello Mynda,

    Is it possible to use COUNTIFS in this fashion:

    COUNTIFS(b1:b20,criteria,n1:y20,criteria)

    I am getting a #value! error and believe that it must be because of the n1:y20 range. Am I only able to compare single columns?

    Thank you in advance for your time,
    Cary

    Reply
    • Carlo Estopia

      April 13, 2013 at 6:30 pm

      Hi Cary,

      Precisely. You might want to separate the ranges like COUNTIFS(b1:b20,criteria, n1:n20, criteria, y1:y20, criteria)

      Cheers,

      CarloE

      Reply
      • Cary

        April 14, 2013 at 2:12 am

        Thanks Carlo, I appreciate your time!

        Reply
  81. Rajesh

    April 11, 2013 at 4:45 pm

    Many Many Thanks. This is the greatest way to learn.

    Best Regards,

    Reply
    • Mynda Treacy

      April 11, 2013 at 6:31 pm

      Cheers, Rajesh 🙂

      Reply
  82. Gaurav Sahai Saxena

    April 9, 2013 at 9:15 pm

    Can you help me in understanding following formula

    =COUNTIFS(‘Tickets Tracking 2013’!$G$7:$G$71, 3, ‘Tickets Tracking 2013’!$H$7:$H$71, 2013) + COUNTIFS(‘Tickets Tracking’!$G$7:$G$73, 3, ‘Tickets Tracking’!$H$7:$H$73, 2013)

    Reply
    • Carlo Estopia

      April 10, 2013 at 12:57 pm

      Hi Gaurav,

      First, it has a sheet called Tickets Tracking 2013
      Second, In this sheet, you are counting if
      Ranges G7 to G71 has or equal to 3
      and
      Ranges H7 to H71 has or equal to 2013
      First Formula:COUNTIFS(‘Tickets Tracking 2013′!$G$7:$G$71, 3, ‘Tickets Tracking 2013′!$H$7:$H$71, 2013)
      Third, You are adding –see the plus (+) sign — the result in the second step (first formula) to the fourth step(second formula):
      Fourth, You are counting if
      Ranges G7 to G73 has or equal to 3
      and
      Ranges H7 to H73 has or equal to 2013
      Second Formula: COUNTIFS(‘Tickets Tracking’!$G$7:$G$73, 3, ‘Tickets Tracking’!$H$7:$H$73, 2013)

      Cheers,

      CarloE

      Reply
  83. Lelbron

    March 15, 2013 at 12:59 am

    Thanks! I needed this for work and your guide was what got me through it!!

    Reply
    • Mynda Treacy

      March 15, 2013 at 8:29 am

      Cheers, Lelbron. Glad I could help 🙂

      Reply
  84. Pam

    March 15, 2013 at 12:09 am

    Hi Mynda,

    You make everything sound so easy!!! So I have a spreadsheet that has dates from 7/1/12 – 2/28/13 and I am trying to count how many entries were made for each month but there were several entries that were made on each day, e.g. 5 on 7/2/12, 9 on 7/19/12, etc. but when I use the COUNTIFS formula of COUNTIFS(B2:B1400, “>=7/1/2012”,B2:B1400,”<=7/31/12") it seems to only be counting the dates once. How can I get it to count each entry instead of each date? THANK YOU in advance.

    Reply
    • Carlo Estopia

      March 15, 2013 at 9:46 am

      Hi Pam,

      Try to check your cell format. It might not be dates.
      You may send it also through HELP DESK so we can have a good look at it.

      Cheers.

      CarloE

      Reply
  85. Haitham

    February 28, 2013 at 10:04 pm

    Someone support me please,

    Can I use CountIFs formula with three column, one of these column has two criteria first criteria “On Air” and the second “RFT”

    it will appreciated if you know the answer

    Regards

    Reply
    • Carlo Estopia

      March 1, 2013 at 3:39 pm

      Hi Haitham,

      Formula:

      =COUNTIFS(A1:A10,"On Air",B1:B10,"RFT")
      

      result: 8
      rows 2 and 3 do not qualify.

      Please use the data below:

           A           B 
      1  On Air	RFT
      2  Ground	RFT
      3  On Air	QRS
      4  On Air	RFT
      5  On Air	RFT
      6  On Air	RFT
      7  On Air	RFT
      8  On Air	RFT
      9  On Air	RFT
      10 On Air	RFT
      

      Cheers.

      CarloE

      Reply
      • Haitham

        May 20, 2013 at 6:02 pm

        Many thanks CarloE
        What about the below issue, i used countifs but not work with two text creteia in the same column:

        North Done On Air
        South Waiting On Air
        South Done CW
        North Done RFT
        North Waiting On Air
        South Waiting RFT
        North Done On Air
        South Done RFT
        North Done CW
        South Done RFT

        =COUNTIFS(A1:A10,”North”,B1:B10,”Done”,C1:C10,”On Air”&”RFT”)

        Regards

        Reply
        • Mynda Treacy

          May 20, 2013 at 10:19 pm

          Hi Haitham,

          You can use SUMPRODUCT for counting with OR criteria:

          =SUMPRODUCT(($A$1:$A$10="North")*($B$1:$B$10="Done")*(($C$1:$C$10="On Air")+($C$1:$C$10="RFT")))

          Kind regards,

          Mynda.

          Reply
          • Haitham

            May 21, 2013 at 6:42 pm

            Thank you Mynda and God bless you

          • Mynda Treacy

            May 22, 2013 at 2:10 pm

            You’re welcome, Haitham 🙂

          • Haitham

            May 23, 2013 at 9:34 pm

            Hi Mynda,
            i hope u well

            kindly I used above formal which was very usful for me but when i replace the “=” by “” then it isnt work properly

            can you help please in case i need to use except “” as below formla but it gave me result 12 while it must be 7 :

            =SUMPRODUCT(($B$11:$B$194=”Darin”)*(($AC$11:$AC$194″”)+($AC$11:$AC$194″Waiting”)))

            Thanks

          • Mynda Treacy

            May 25, 2013 at 12:30 pm

            Hi Haitham,

            You have some double quotes after $AC$194 in your formula above. They need to be removed.

            If that doesn’t fix it then you can send me the Excel file and I’ll take a look.

            Kind regards,

            Mynda.

  86. HAMAD ONSO

    February 18, 2013 at 12:50 am

    bring me questions and answers in excel

    Reply
    • Carlo Estopia

      February 18, 2013 at 11:13 am

      Hi Hamad,

      Please send us your concerns through our HELP DESK.

      Cheers.

      CarloE

      Reply
  87. Ross

    January 24, 2013 at 11:11 am

    Hi Mynda,

    Really appreciate your clear instructions on the use of the excel formulas – thanks!
    However, when I apply the COUNTIFS formula to the following data I just receive a “0” all the time. Here’s the example data and the formula I’ve written (I’m trying to determine how many entries for each Location there are for each Process):

    Column B – Process (multiple entries of each of the following)
    Search
    Service
    Access

    Column E – Location (multiple entries of each of the following)
    T1
    T2

    Formula:- =COUNTIFS(E2:E1300,T1,B2:B1300,Service)
    As I mentioned, the formula continues to just provide “0”.

    Appreciate any help you can provide.

    Many thanks,
    Ross

    Reply
    • Carlo Estopia

      January 24, 2013 at 2:37 pm

      Hi Ross,

      First,

      Your formula’s criteria don’t have the double quotes(“) and equal(=) signs:
      =COUNTIFS(E2:E1300,”=T1″,B2:B1300,”=Service”)

      Second,

      It also looks as if you’re trying to provide two criteria for counting; that is,
      you likely want to satisfy that Location must be T1 and Process is Service.
      And looking at your Example, none would satisfy that. That is, If I understood it
      correctly that you’re trying to use this mock data of yours and there no entries after E3 and B4, respectively.
      I could see though that your formula reaches 1300.

      Please do clarify what are the criteria that you’re really trying to have here.
      Send your file to Help Desk for a complete picture of your scenario. Thus we can
      have a better look at it.

      Sincerely,

      CarloE

      Reply
  88. Subash

    January 22, 2013 at 8:13 pm

    Dear Mynda,

    First of all thank you for such a wonderful site which helps us so very tremendously.

    I have a workbook with several worksheets in it. In the worksheets in different columns I have a reference number. The reference number should not be more than 2 times. Say, once debit and once credit. It is never repeated on the same sheet. I want to use COUNTIFS formula but it always gives me 0, but if I use it only on one sheet it gives me 1, which is correct.

    For example in sheet “SUBASH” column “J:J” has the reference numbers and in sheet “MYNDA” column “J:J” has the reference numbers. In Sheet “MYNDA” in cell k2 if I put this formula COUNTIFS(J:J,J2) I get 1 which is correct, as the reference in J2 appears only once in this sheet in the column J:J. When I change the formula to COUNTIFS(J:J,J2,’SUBASH’!J:J,J2) I get the answer 0, where as it should be 2 as the value in J2 in MYNDA also appears in the column J:J in the sheet SUBASH (not necessarily in J2 in sheet SUBASH).

    If I get the correct answer, I should be able to drag the formula down so that I get the same result from J3, J4 and so on.

    Above I have mentioned only two sheets, but in my workbook I have many more.

    Kindly advise how should I go about getting the correct result.

    Thanks and best regards,
    Subash

    Reply
    • Subash

      January 22, 2013 at 9:25 pm

      Dear Mynda,

      I worked my way out by using the SUMPRODUCT((J:J=J2)+(’SUBASH’!J:J=’MYNDA’!J2)) this formula gives me the correct answer 2. But since I have many sheets, have to write the formula for 20 times in each sheet, would appreciate if there is a shorter formula to capture the data from all the sheets.

      Thanks and best regards,
      Subash

      Reply
      • Carlo Estopia

        January 23, 2013 at 4:48 pm

        Hi Subash,

        I would suggest that you send your file through Help Desk so I could fully understand what’s going on.

        It’s good that you have worked around the problem; however, I need to see the data and the formula and the logic of it so I can help you with it.

        Sincerely,

        Carlo Estopia

        Reply
    • Carlo Estopia

      January 23, 2013 at 4:57 pm

      Hi Subash,

      I have replied with your recent message.

      Anyway, please consolidate all your concerns and send me your file through Help Desk with the mock data and the results or formulas you want to achieve.

      Sincerely,

      CarloE

      Reply
  89. Jhosel

    January 18, 2013 at 6:13 am

    I’m find it hard to combined countifs with left formula. Below is my scenario:

    Total Age (column A)
    37
    35
    35
    35
    28
    28

    Queue (Column B)
    2S0096
    1S0096
    1S0096
    1S0096
    1S0096
    1S0096

    Column I need to count all 30 above from column A that is starting with 1S because I need to count all Aged items from 2S separately.

    i tried the formula below but it’s not working:

    =COUNTIFS(A3:A10,”>=30″,B2:B5,LEFT(B2:B5,2))

    Hope you can help me with this. 🙂

    Reply
    • Carlo Estopia

      January 18, 2013 at 10:27 pm

      Hi Jhosel,

      Your formula is good except for the LEFT FUNCTION part. I hope you’ll agree if we would avoid that complication and instead use a wildcard search.
      here it is:

      =COUNTIFS(A2:A7,”>=30″,B2:B7,”=2S*”)

      result : 1

      =COUNTIFS(A2:A7,”>=30″,B2:B7,”=1S*”)

      result : 3

      For more on COUNTIFS

      Sincerely,

      CarloE

      Reply
      • Jhosel

        January 30, 2013 at 8:12 am

        Thanks much with this Carlo! i never knew it could be this simple. ;p This will help me a lot with all the reports that I’m doing. thanks again and keep it up! 🙂

        Reply
        • Carlo Estopia

          January 30, 2013 at 7:09 pm

          Hi Jhosel,

          In behalf of Mynda and Philip, I say you’re welcome.

          It’s our pleasure to make our visitors satisfied as much as possible.

          Sincerely,

          CarloE

          Reply
  90. Kay

    January 5, 2013 at 6:05 am

    Hi Mynda,
    I hope you can help me, I’ve spent hours on this already!!! I need to count the total number of job titles contained in a spreadsheet that do not equal Manufacturing, Agency Manufacturing or Agency Indirect.

    Any advice you can give me will be very gratefully received.

    Best regards, Kay

    Reply
    • Mynda Treacy

      January 5, 2013 at 10:07 am

      Hi Kay,

      Assuming your job titles are in cells A1:A5 you can use this formula:

      =SUMPRODUCT((A1:A5"Manufacturing")*(A1:A5"Agency Manufacturing")*(A1:A5"Agency Indirect"))

      Kind regards,

      Mynda.

      Reply
  91. ABRAHAM

    November 29, 2012 at 4:03 pm

    EXAMPLE
    A1 B1
    ABB 15451
    EBB 15454
    ABB 159685
    EBB 8754
    ACC 8759654
    ABB 54875

    I need to have the total of abb ie 230011 in one cell.

    please advise the formula

    Reply
    • Mynda Treacy

      November 29, 2012 at 7:30 pm

      Hi Abraham,

      You can use this formula:

      =SUMIF($A$2:$A$7,"ABB",$B$2:$B$7)

      Kind regards,

      Mynda.

      Reply
      • ABRAHAM

        November 29, 2012 at 7:48 pm

        YOU SAVED MY LIFE!!! THANKS A LOT

        Reply
  92. ABRAHAM

    November 29, 2012 at 3:32 pm

    hi thanks for the formulas. i need one more formula: if a certain colum contains different names and the colum next with numbers and i want the total sum of those numbers according to the names then what is the formula? please advise

    Reply
  93. Nitin G

    November 19, 2012 at 3:05 pm

    The explanation part is really Good.
    Thanks for sharing the knowledge.

    Reply
    • Mynda Treacy

      November 19, 2012 at 6:11 pm

      🙂 You’re welcome, Nitin.

      Reply
  94. Karen

    November 15, 2012 at 2:21 am

    Tried a COUNTIFS formula and it is not returning a number just a value error.

    =COUNTIFS($AC$10:$AC$2832,”EAP”,$B$10:$B$2831,B2839)

    Will the formula work if some of the cells in column AC are blank?

    Reply
    • Mynda Treacy

      November 15, 2012 at 12:01 pm

      Hi Karen,

      I your second range B10:B2831 is a different size to the first range AC10:AC2832. You need to make them both the same size.

      Kind regards,

      Mynda.

      Reply
  95. Trish

    November 3, 2012 at 3:16 am

    Hi, wow…thank you for your time here on your website. You explain things perfectly! I recently decided I wanted to learn what excel is all about instead of feaing it and saying “no I don’t like excel”. I am trying to learn how to track expiring certificates at work. Exp: DL from John Doe expires 10/25/2016; next cell would read how many days left until expiration. I also saw how it could be have a color with it. It could turn yellow within 30 days of expiration and red once the day has come and passed.
    Again thank you for your time, I hope you can point me in the right direction :)!

    Reply
    • Mynda Treacy

      November 4, 2012 at 8:09 pm

      Hi Trish,

      Thank you for your kind words 🙂

      You can use the DATEDIF function to calculate the number of days between two dates (your expiry date and today’s date).

      You can then use Conditional Formatting to detect if the date is <= 30 days and colour the cell yellow. I hope that helps. Kind regards, Mynda.

      Reply
  96. Elijah

    September 5, 2012 at 5:09 am

    how can i count a column but stop counting if data goes to another column on excel

    Reply
    • Mynda Treacy

      September 5, 2012 at 1:45 pm

      Hi Elijah,

      If your data goes into another column then it won’t be in the range of the first column and therefore won’t be counted. I must be missing something? Perhaps you can send me an example.

      Kind regards,

      Mynda.

      Reply
      • RKM

        October 30, 2012 at 7:02 pm

        Hi Mynda ,
        I just want display the count of two strings belonging to same column
        For Ex: Two strings are “Blue” and “Green” I want formula for excel 2007 which counts sum of recurrence of these two strings
        Please help me out.
        King Regards,
        RKM

        Reply
        • Mynda Treacy

          October 30, 2012 at 7:43 pm

          Hi RKM,

          Can you please tell me how your data is laid out, or even better, send me an example by logging a ticket on the help desk.

          Kind regards,

          Mynda.

          Reply
          • RKM

            October 31, 2012 at 7:25 pm

            Dear Mynda,
            Sure,
            As I am not able to raise ticket, I will explain you my problem here only,
            See, my data is arranged in following way:
            Cell Location Strings
            A1 Ram
            A2 John
            A3 John
            A4 Ram
            A5 Jack
            A6 Ram

            I just want formula which give me total count of Ram and John.
            Means I want to know What is the sum of John and Ram.
            Here we can simply count it as Ram+John=5
            I require formula which display count=5

          • Mynda Treacy

            October 31, 2012 at 8:17 pm

            Hi RKM,

            =SUMPRODUCT((B1:B6=”John”)+(B1:B6=”Ram”))

            More on SUMPRODUCT here.

            Kind regards,

            Mynda.

          • RKM

            November 1, 2012 at 2:29 pm

            Thanks! Mynda.

          • saeed

            January 4, 2013 at 4:54 am

            hi
            plz can you help me .
            i want a formula to write text or number in column A ( example ) and a number in column B then it will paste the text in next column ( 2 or 4 ) according to number i write .
            for example :
            in column A ( welcome ) and in column B ( 3) so i want ( welcome ) to appear in column C,D & E ( ONCE ONLY) .
            AND IF I write 4 then in C,D,E & F .

          • Mynda Treacy

            January 4, 2013 at 8:42 pm

            Hi Saeed,

            That’s a good question. Here’s a formula you can use in cell C1 then copy across to D, E and F:

            =IF(B1="","",IF(COLUMNS($C$1:C1)<=$B$1,$A$1,""))

            Kind regards,

            Mynda.

  97. Cate

    July 31, 2012 at 1:52 am

    Wow! Wonderfullly explained.

    Reply
    • Mynda Treacy

      July 31, 2012 at 7:58 am

      Thanks, Cate. Glad we could help 🙂

      Reply
  98. Abby

    July 11, 2012 at 5:09 am

    SO HELPFUL! Thank you so much for posting this.

    Reply
    • Mynda Treacy

      July 11, 2012 at 9:43 am

      Cheers, Abby 🙂

      Reply
  99. Dee

    May 1, 2012 at 8:50 pm

    goodmorning, thanks for the reponse. it is still not working.

    The formula needs to meet both criteria 1) wrong status AND 2) CPSUB OR B7SUB OR E9SUB.

    It is saying to try DCOUNTA when you click on the function .
    I really appreciate your help.
    Have agreat day,
    Dee

    Reply
    • Mynda Treacy

      May 2, 2012 at 10:19 am

      Oh, sorry.

      Try:

      =SUM(COUNTIFS(‘RAZ00302′!R:R,”WRONG STATUS”,’RAZ00302’!T:T,{“CPSUB”,”B7SUB”,”E9SUB”}))

      Enter formula with CTRL+SHIFT+Enter as it is an array. You should be able to see curly brackets at either end of the formula when viewed from the formula bar. Like this:

      {=SUM(COUNTIFS(‘RAZ00302′!R:R,”WRONG STATUS”,’RAZ00302’!T:T,{“CPSUB”,”B7SUB”,”E9SUB”}))}

      Note: when you copy and paste the formula from the web page you may need to type in the double quotes and apostrophes, as sometimes they are italicised on the web and this won’t work in Excel.

      If that doesn’t work I must be missing something so please send me your file by completing a Help Desk ticket on the contact us page.

      BTW; I’m not sure what you mean by “It is saying to try DCOUNTA when you click on the function”. But hopefully the above formula will work now.

      Kind regards,

      Mynda.

      Reply
      • Dee

        May 2, 2012 at 11:05 am

        hey Mynda,
        This is work related so sending the document would probablly be a bad idea. I am sorry. It now works better but is ignoring the last 2 criteria :B7SUB E9SUB. I am so grateful for all your help. You sure do know a bunch about formulas.
        have a good night
        Dee

        Reply
        • Mynda Treacy

          May 2, 2012 at 12:00 pm

          I understand. Usually what people do is send me an edited version of their Excel file thus removing any sensitive information. It’s always an option if you get stuck.

          Reply
          • Dee

            May 3, 2012 at 5:56 am

            hi. was unable to get it to work and could not get file to upload.

            It counts the first item in the array but not the last 2. B7SUB E9SUB .
            I guess I’ll just keep trying. appreciate your trying tohelp.
            Dee

          • Mynda Treacy

            May 3, 2012 at 8:39 pm

            How big is your file? You could email it to me at website@myonlinetraininghub.com if you want to have another go!

  100. alaiksander

    March 16, 2012 at 7:56 pm

    Hi, I have a question on countifs.
    My data will be to count information like age (between 7-15), and its city.
    =COUNTIF(A:A;”<=15")-COUNTIF(A:N7356;"<7") gives me the correct total of age between 7-15

    But when I entered

    =COUNTIFS(A:A;COUNTIF(A:A;"<=15")-COUNTIF(A:A;"<7");A:A;B)

    excel 2007 gave me error. Will it be possible to count it this way? thanks.

    PS:
    B = a cell contains city name

    Reply
    • Mynda Treacy

      March 16, 2012 at 10:16 pm

      Hi,

      You need to do it like this:

      =COUNTIFS(A:A,">=7",A:A,"<=15",B:B,"City Name")

      Kind regards,

      Mynda.

      Reply
      • alaiksander

        March 17, 2012 at 12:43 am

        Thanks for your quickest reply, It really means a lot of hours to me.

        Reply
        • Dee

          May 1, 2012 at 6:29 am

          hello,
          I have a formula problem.
          I need to count 2 columns, with 2 different criteria, on a different spreadsheet, and have the results end on my last page for graphing purposes.
          =COUNTIFS(‘RAZ00302’!R:R,”WRONG STATUS”)
          Thia is tracking one of the columns on the razo302 sheet but I also need to track column t. 3 items in column t need to be picked: CPSUB, B7SUB, AND E9SUB
          PLEASE ADVISE ASAP—I AM SO EXCITED THAT i FOUND YOUR WEBSITE.
          thank you!!!

          Reply
          • Mynda Treacy

            May 1, 2012 at 9:24 am

            Hi Dee,

            Thanks for your kind words.

            Try:

            =COUNTIFS(‘RAZ00302′!R:R,”WRONG STATUS”)+SUM(COUNTIF(‘RAZ00302′!T:T,{“CPSUB”,”B7SUB”,”E9SUB”}))

            The part in curly brackets {} is an array. Go here for more on Excel array formulas.

            I hope that helps.

            Kind regards,

            Mynda.

  101. Gunnar

    March 14, 2012 at 12:45 am

    I rephrase my previos question to this: Why do you need a sumproduct function at the start of the formula?

    Reply
    • Mynda Treacy

      March 14, 2012 at 9:30 pm

      Hi Gunnar,

      The SUMPRODUCT formula is an array formula (although you don’t have to enter it using CTRL+SHIFT+ENTER), and when used with COUNTIF and INDIRECT as I have done above for Mitchel, it returns the COUNT of multiple sheets and multiple criteria.

      If you’re having difficulty counting across multiple sheets then an easier solution is to put a COUNTIFS formula on each sheet and then SUM those results. If you put your COUNTIFS formula in the same cell in each sheet then summing them is quick and easy.

      To SUM the same cell on multiple sheets:

      1. In the cell you want your summary type =SUM(
      2. Click on the first sheet tab and select the cell you want to sum.
      3. Hold down the SHIFT key and click on the last sheet.
      4. Press ENTER

      Excel will sum the same cell for every sheet between the first sheet you selected and the last.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  102. Gunnar

    March 13, 2012 at 11:09 pm

    Dear Mynda,
    I am trying to use your formula abouve for the countif on several sheets, but can’t get it to work, even if I copy it into an identical workbook.
    Can you explain a bit more in detail the indirect function part of the formula, and why you use the type of signs you do:
    “ ‘ ” & ” ‘ !B:B”
    Thanks!

    Reply
    • Mynda Treacy

      March 14, 2012 at 9:17 pm

      Hi Gunnar,

      I suspect when you copied the formula from the website into Excel the double quotes ” are not formatted correctly. In the webpage above they are italicised for some reason. Try typing them into Excel again. They should then be a regular font and your formula should work.

      Alternatively you can download the workbook (see links in the post above) and use the example in the file.

      The INDIRECT function, as explained here creates a reference to a cell or range of cells. When referencing other sheets you need to format the reference with apostrophes.

      For example references to other worksheets must be formatted like this:

      =INDIRECT(” ‘your_sheet_name’!H34″)

      Text in double quotes is interpreted by Excel as text (as opposed to an operator or other criteria).

      The ampersand (&) joins that text together. So Excel reads ” ‘ ” & “!B:B” as (note spaces inside double quotes are for clarity. In Excel you would leave the spaces out):

      ‘!B:B

      Effectively ignoring the double quotes and ampersand. You can read more about joining text together in my CONCATENATE tutorial.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  103. Katie

    January 21, 2012 at 6:51 am

    Hi,

    I am trying to write a countifs that says if column b is in a certain date range, count it, which I have written, but I need a second criteria that says if column has one of five location names (city, state), then count it.

    Is it possible to write a countif with an “or” criteria imbedded?

    I have countifs(…..E:E,OR(‘Location’!B1,’Location’!D1,’Location’!E1,’Location’!G1,’Location’!H1))

    But that didn’t work. I have also tried writing out the location names instead of selecting them from a list, but that didn’t work either…

    Any ideas?

    Reply
  104. Katie

    January 12, 2012 at 10:03 am

    I am trying to write a countifs formula to say if anything in this column is between these dates, count it. Here’s what I have so far =countifs(Q:Q, and(“=1/1/2012”), ….etc)

    I want it to say if the contents in column Q are between jan 1 and jan 31, 2012, then count it.

    Any advise? Thanks!

    Reply
    • Mynda Treacy

      January 12, 2012 at 8:15 pm

      Hi Katie,

      I find dates used in criteria a bit frustrating and so I tend to use the serial number version of the date in my formula rather than typing in the text version. e.g.

      =COUNTIFS(Q:Q,”>=40909″,Q:Q,”<=40939")

      Where 40909 is the serial number for 1 Jan 2012, and 40939 is the serial number for 31 Jan 2012.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
      • Katie

        January 13, 2012 at 2:18 am

        Great, thank you!

        Reply
  105. Jeff

    December 3, 2011 at 3:59 am

    Hi Mynda,
    I am creating a dashboard report using Excel 2010. I am using Conditional formatting and a countcolor functions on the sheet – these all work perfectly. I am having issues with my countif(s)? and hope you can help.

    My range (I8:AM8) represents individual project milestones being “Complete” or “N/A”. I want my following countif to total the count of “Complete” and “N/A” milestones and divide that total by the total number of milestones.

    =COUNTIF(I8:AM8,”Complete”)/31
    HOW do I add the “N/A” to this formula? I’ve looked on-line for an hour and asked several folks without luck.

    Cheers,
    Jeff

    Reply
    • Mynda Treacy

      December 3, 2011 at 9:30 pm

      Hi Jeff,

      Thanks for your question.

      There are a few ways you could do this. Here are two:

      1. If you want to use COUNTIF then you’d need to add two COUNTIF functions together and then divide by 31

      So, your formula would look like this:

      =(COUNTIF(I8:AM8,”Complete”)+COUNTIF(I8:AM8,”N/A”))/31

      2. Or you could use the SUMPRODUCT function like this:

      =SUMPRODUCT((I8:AM8=”COMPLETE”)+(I8:AM8=”N/A”))/31

      Click here for more on SUMPRODUCT.

      If your total number of milestones is variable (i.e. the number 31) you could use a COUNTA function in it’s place to count all cells in the range I8:AM8. So your formuls would look like this:

      =(COUNTIF(I8:AM8,”Complete”)+COUNTIF(I8:AM8,”N/A”))/COUNTA(I8:AM8)

      and

      =SUMPRODUCT(((I8:AM8=”COMPLETE”)+(I8:AM8=”N/A”)))/COUNTA(I8:AM8)

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  106. Ibraheem

    September 24, 2011 at 3:56 am

    Thank you very much Mynda for considering my question. I had got the correct results watching your other helpful hints on Youtube and hence did not check for your response and sorry for the same. But now I come across another problem because my boss wants to consider End Dates of employee as well. Suppose he joins 01 September 2011 and his End Date is 31 December 2015, what could be the changes that should be made in my formula. Please make the necessary changes in this one (=COUNTIFS(‘Master List’!$E$3:$E$3210,’Manpower Plan’!$C5,’Master List’!$K$3:$K$3210,’Manpower Plan’!$B5,’Master List’!$N$3:$N$3210,”<="&$TE$2,'Master List'!$O$3:$O$3210,TE$4,'Master List'!$AC$3:$AC$3210,$E$3).

    Reply
    • Mynda Treacy

      September 24, 2011 at 6:12 pm

      Hi Ibraheem,

      Remember, each criteria you add to a COUNITFS function is considered AND. e.g.

      =COUNTIFS(critera_range_1,criteria_1, AND criteria_range_2,criteria_2…..and so on if required)

      So, to modify your formula you just need to add another reference to your criteria range (the Master List) and the criteria of <=31 Dec 2015 like this:

      =COUNTIFS(‘Master List’!$E$3:$E$3210,’Manpower Plan’!$C5,’Master List’!$K$3:$K$3210,’Manpower Plan’!$B5,’Master List’!$N$3:$N$3210,”<="&$TE$2,'Master List'!$O$3:$O$3210,TE$4,'Master List'!$AC$3:$AC$3210,$E$3,’Master List’!$K$3:$K$3210,<=TE$5)

      Where TE$5 contains the date 31 Dec 2015.

      I hope this makes sense. If not you can send me your Excel workbook and I can send you a specific solution. Just complete a ticket on the help desk which you'll find a link to on the contact us page.

      Kind regards,

      Mynda.

      Reply
      • ibrahim

        July 16, 2013 at 5:07 pm

        i want to add multipale column in countifs formulas…?

        Reply
        • Mynda Treacy

          July 17, 2013 at 1:24 pm

          Hi Ibrahim,

          You can set your range to more than one column. e.g:

          =COUNTIFS(A1:B10,"your criteria 1",C1:D10,"your critiera 2")

          I hope that helps. If not please send your workbook to me via the help desk so I can see an example of your data.

          Kind regards,

          Mynda.

          Reply
  107. Ibraheem

    September 15, 2011 at 3:25 pm

    Hi Mynda,

    I have been watching your helpful tutorials in excel these days and have been using them satisfactorily to process our company’s 5 year Manpower Cost Plan.

    I have columns by Name September 2011, October 2011….

    I want to place under each month no. of employees who joined before that month.

    Can you help how COUNTIFS work in this?

    Thanks.

    Reply
    • Mynda Treacy

      September 16, 2011 at 8:16 pm

      Hi Ibraheem,

      Thank you for your question. If I understand correctly you should be able to just use a COUNTA function (assuming your columns have employee names. but use COUNT if they’re listed as numbers). And assuming your month labels start in column A (jan), and you had already populated columns A-D (Jan-Apr), with D (Apr) being the current month, and you wanted to know the number of employees who joined from Jan – Mar your formula would be:

      COUNTA($A:$C)

      Note: this will sum the whole of columns A & C. If you’ve got cells in these columns that you don’t want to sum then you’ll need to put the exact range in e.g. COUNTA($A$2:$C$500)

      I hope this helps you out.

      Kind regards,

      Mynda.

      Reply
      • Riyaz Iqbal

        March 26, 2013 at 4:18 pm

        Hi Mynda,

        I tried this for example for practice and encountered 2 problems.

        1. COUNTA($A:$C) counts my headers (names of months too). Is there some way I can count entries in the whole columns except headers?

        2. What if I want to maintain a running count of employees? I tried OFFSET function but wasn’t able to get it right. I added a “Employee count” column to the right.

        COUNTA(A: column to the left of Employee count) is what I tried. For “Column to the left of Employee count” I tried OFFSET(D:D,0,-1,height,1) I am facing problems with height here. I don’t know how many employee entries will be there in future.

        How can I solve this height issue when I know that the column I am interested in is of dynamic height?

        Regards,
        Riyaz

        Reply
        • Carlo Estopia

          March 26, 2013 at 10:17 pm

          Hi Riyaz,

          Why don’t you send your file via HELP DESK
          so we can have a good look at it.

          Cheers.

          CarloE

          Reply
  108. Kaushal Verma

    June 10, 2011 at 1:26 am

    I have prepared My excel in this manner :
    Column A Column B Column C Column D ……….
    ORDER # OPERATOR QC Int. Error External Error
    22120205 Nancy Chikie – –
    22120207 Alex Kaushal 1 –
    And so on……..

    I have kept this list under one sheet named as order logged
    Now I have prepared another sheet to find their daily productivity
    Column A Column B Column C Column D
    S.No Name 1 June 2 June ……….
    1 Nancy 25 20
    2 Alex 16 30

    I have made a use of this formula to calculate =COUNTIF($’ORDERS LOGGED’.$B$2:$B$1000;B2)
    So i would get the count of file Alex has prepared with a help of this formula which calculate data from order logged sheet.

    ????? But Now there are few order where in there are sub files in it say 22120207 ……is a one order but it consist of 7 other files ( with certain changes in same type) in it and Alex work on these too under the same order but his count of order is consider as one only….

    when he worked on 8 files in total under one order.Which proves the report is incorrect.

    So what can be done??????????

    I tried this…….
    Column A Column B Column C Column D ……….
    ORDER # OPERATOR QC Int. Error External Error
    22120207 Alex Kaushal 1 –
    Alex
    Alex
    Alex
    Alex
    Alex like this kept on added his name but this is tedious….which helps to get correct count but I want to know some short way….as there is situation when in one order there are 100 files as well….Hope this will make more sense to the kind of difficulty I have.

    I would really appreciate & would be glad to receive your help.

    Regards,
    Kaushal Verma.

    Reply
    • Mynda

      June 10, 2011 at 3:33 pm

      Hi Kaushal,

      Please send me the file so I can see your data.

      To do this go to the Contact Us page and log a ticket. You can upload your file here.

      Cheers,

      Mynda.

      Reply
  109. Kaushal Verma

    June 8, 2011 at 5:39 am

    Hi Mynda,

    I want to know about this \ suppose there are 7 members..A/B/C/D…..
    20 files are shared between them. Now I able to know how many file are prepared by C lets say 5 but if in these 5 files, there were 2 files with Copy Change, so in total he worked on 7 files. Will then this Count-if formula will work or what would the alternative?
    Names
    A 4
    B 6
    c 5 two were having sub file (copy change file).

    =COUNTIF($C$2:$C$7,$B12) this help me to know that c appears 5 times but does not give me correct productivity of him ?

    Please help.

    Thanks,
    Kaushal Verma.

    Reply
    • Mynda

      June 8, 2011 at 10:55 am

      Hi Kaushal,

      Thanks for your question. I’m not clear as to whether you want Excel to track this data (whether C prepared, or copy/changed a file) or are you keeping track and entering the data and then just needing to count it?

      I’ll assume the latter:

      Firstly in order for Excel to count the data, you need to record it in a format it can read. If you want to use the COUNTIF then you would need to lay your data our something like this:

      Column A  Column B   Column C
      Name        File Name     Action (prepare, copy, change)
      C             xyz.xlsx     prepare
      A             abc.xlsx     prepare
      C             abc.xlsx     change

      You could then use a COUNTIFS function:

      =COUNTIFS(A1:A3,”C”)

      This would count every instance of “C” in the range A1:A3.

      If this isn’t what you meant please go to ‘contact us’ and log a ticket on the help desk with your file so I can see exatly what you mean.

      Kind regards,

      Mynda.

      Reply
  110. Mynda

    June 7, 2011 at 9:33 am

    Hi Glen,

    I’m not 100% clear on how your data is laid out but I’ll give it a shot:

    Can you not use a COUNTIF like this?:

    in an empty cell in your workbook =COUNTIF(H$3:H$13,”Jan”)
    and in another cell in your workbook =COUNTIF(H$3:H$13,”Feb”)

    If that doesn’t ‘stop it hurting’ please log a ticket on the help desk and upload your file or an example of your data so I can see exactly what you’re trying to do.

    Kind regards,

    Mynda.

    Reply
    • Lamech

      November 22, 2012 at 6:35 pm

      i have a similar challenge; COUNTIF(J5:J106,”Left”)
      but unfortunately it does not count all the texts in that range it counts only 1/3 of the total what might be the problem?

      regards

      Reply
      • Mynda Treacy

        November 22, 2012 at 11:20 pm

        Hi Lamech,

        I’m not sure but unless the the text in column J only says ‘left’ then it should count them all.

        If there is other text with the word ‘left’ then you can append wild cards to pick up anything with ‘left’ in the cell like this:

        =COUNTIF(J5:J106,"*Left*")

        More on Excel wildcards here.

        Kind regards,

        Mynda.

        Reply
  111. Glen

    June 7, 2011 at 5:18 am

    I amy trying to simulate count distinct using a formula I found this morning on google: =SUMPRODUCT((H$3:H$13\)/COUNTIF(H$3:H$13,H$3:H$13&\))

    I need to vary this such that I get distinct counts by month. Column A has the month as Jan, Feb…
    =SUMPRODUCT((H$3:H$13\)/COUNTIFs(H$3:H$13,H$3:H$13&\,G$3:G$13,=&A3)) does not exactly give me what I need. Column H is a series of names. I want a count of distinct names in Jan, another count in Feb….

    Any thoughts?

    Please make it stop hurting…. I’ve been at it for 5 hours now, trying various combinations of functions in varying orders with no joy…

    Glen

    Reply
  112. mitchell piedad

    April 17, 2011 at 8:28 am

    I am using the formula countifs as used in the second example, my trouble is when I am trying to get a total covering more than one sheet. Each sheet layout is the same but receive #value error on previous attempts. I am expecting to get the total over 30 sheets.

    Reply
    • Mynda

      April 17, 2011 at 4:04 pm

      Hi Mitchell,

      You need to use SUMPRODUCT to do a 3d COUNTIFS across multiple worksheets. Using the example in my COUNTIFS tutorial above:

      =SUMPRODUCT(COUNTIFS(INDIRECT(“‘”&$G$1:$G$4&”‘!B:B”),”Central”,
      INDIRECT(“‘”&$G$1:$G$4&”‘!C:C”),”Doug”))

      Where G1:G4 contains a list of your sheet names. You will need to put these sheet names somewhere in the worksheet you want your COUNT, and then amend the range in the formula above to suit.

      You can also replace “Central” and “Doug” with cell references but remember to drop the “” and just enter the cell reference by itself.

      Note: the reference to the column B:B and C:C assumes you want to count the whole column. If not just enter the exact range e.g. B2:B7 and C2:C7.

      Alternatively, insert a separate COUNTIFS formula on each sheet, and then SUM the result on each sheet. e.g. =SUM(‘sheet 1:sheet 30’!D2) where D2 is the cell that contains the COUNTIFS formula on each sheet.

      I hope one of these helps. Let me know how you go.

      Regards,

      Mynda.

      Reply
  113. Mynda

    November 18, 2010 at 9:05 pm

    The following question was sent in to me by Helen, but I thought I’d post my reply here for all to see:

    Q. I want to count how many cells contain values between two numbers eg -0.5 and -0.25, How can I do this in excel? I use the countif function for specific values but I don’t know how to count between specific values.

    A. In Excel 2007 You can use the COUNTIFS formula as follows:

    =COUNTIFS(A2:A14,”>-0.5″,A2:A14,”<-0.25") In prior versions of Excel there are various ways to accomplish this, but probably the simplest is an array formula as follows =SUM((A2:A14>-0.5)*(A2:A14<=-0.25)) you have to press CTRL+SHIFT+ENTER all together to enter the SUM formula as an array. When you do this correctly the formula looks like this in the formula bar: {=SUM((A2:A14>-0.5)*(A2:A14<=-0.25))} I won't go into the logic of the array formula here, other than to say that when a SUM formula is entered as an array it gives you a COUNT instead of a SUM. Mynda.

    Reply
  114. jose aviles

    November 4, 2010 at 3:22 am

    hi, I am trying to use excel with xlite soft phone. I am unable to find the formula to connect excel with xlite.. to better explain myself, i use excel to contact customers for business… i need excel to direct itself after i double click on the cube to xlite… and xlite calls for me, instead of me cpoy and past the numbers.. thanks… could some please send me the formula or an excel that already has the formula activated..

    Reply
    • Mynda

      November 4, 2010 at 7:53 pm

      @jose Thanks for your question. I did a bit of Googling and found this post that offers a solution to your question. I hope it’s what you’re looking for.

      http://forums.counterpath.com/viewtopic.php?f=3&t=11767&start=0

      Reply
  115. weighty

    September 27, 2010 at 2:49 pm

    gonna send this to my mom

    Reply
    • Mynda

      September 29, 2010 at 8:29 pm

      I hope she finds it useful @weighty

      Reply
  116. John MCc

    September 17, 2010 at 1:24 pm

    thanks. This kinda stuff is really hard for me but you explained it really well.

    Reply
    • gilmour

      September 21, 2010 at 3:35 pm

      Thanks John, glad that we are able to explain it clearly.

      Reply
    • synkstar

      November 26, 2010 at 5:09 am

      very useful for my current work

      Reply
  117. Anil kumar Pandey

    December 8, 2015 at 5:28 pm

    Hi Sir Please solve my problem
    code 41, code 37 in a single column and in front next single column get A1, A2 i want to count how many code 41 A1 what formula used.
    example given below:
    SUB MRKGRD
    37 92 A2
    41 80 A2
    37 93 A1
    37 90 A2
    41 93 A1
    41 77 B1
    41 80 A2
    41 82 A2
    37 82 B1
    41 72 B1
    41 72 B1

    Reply
  118. Catalin Bombea

    December 8, 2015 at 9:12 pm

    Hi Anil,
    You can try this formula:
    =SUMPRODUCT(($A$2:$A$12=37)*($C$2:$C$12=”A2″))
    Change 37 and A2 used in this formula and the ranges to your specific data.
    Catalin

    Reply
  119. Anil kumar Pandey

    December 10, 2015 at 6:38 pm

    Hi Sirji

    Thanks for Reply
    Your
    =SUMPRODUCT(($A$2:$A$12=37)*($C$2:$C$12=”A2″)) is ok but my problem as it is.

    i want to know for example : code is 37 in front of row A1 or A2 . When put formula answer show.

    code 37 A1 is one. please give next formula . Thanking you

    Reply
  120. Catalin Bombea

    December 11, 2015 at 10:15 am

    Hi Anil,
    You can try this file from our OneDrive folder: Unique Count.
    Hopefully it answers your questions.
    Cheers,
    Catalin

    Reply
  121. Anil kumar Pandey

    December 11, 2015 at 1:13 pm

    Hi Sirji
    First i would like to Thanks.
    You give reply my Question.

    Reply
  122. Anil kumar Pandey

    December 11, 2015 at 4:45 pm

    Hi Sir Again Pandey.
    Your formula is excellent. Why doesn’t your formula get copied.
    SUB MRK GRD Unique Count
    37 92 A2 37A2 37A1 1
    41 80 A2 41A2 37A2 2
    37 93 A1 37A1 37B1 1
    37 90 A2 37A2 41A1 1
    41 93 A1 41A1 41A2 3
    41 77 B1 41B1 41B1 3
    Yes ok it is not it is not
    Unique or count it is not done.know what to do. it is last. thanking you sirji

    Reply
  123. Catalin Bombea

    December 11, 2015 at 2:59 pm

    You’re wellcome 🙂

    Reply
  124. Catalin Bombea

    December 11, 2015 at 9:05 pm

    The formula in Unique column is an array formula, should be entered with Ctrl+Shift+Enter, not just Enter after editing the formula.
    Catalin

    Reply
  125. ANIL KUMAR PANDEY

    December 14, 2015 at 4:46 pm

    Ok Sir G. Thanks a lots. if i have any problem then i again ask question. Thanks

    Reply
  126. ANIL KUMAR PANDEY

    December 14, 2015 at 4:51 pm

    Sirji (Array formula sheet sent me) for my knowledge.

    L.E.:
    Hi Sir ji i want to result for given below table. which formulas help we can get how many 41 code get A1 grade or more 37,48 and 63 same. please help me. you can do easily.

    Cod MRK GRD
    41 95 A1
    37 67 C2
    48 75 B2
    37 67 C2
    37 73 C1
    41 48 C2
    41 68 B2
    37 72 C1
    41 54 C1
    54 59 D1
    54 64 C2
    48 85 A2
    54 63 C2
    37 63 C2
    41 42 D1
    48 83 A2
    41 52 C1
    41 59 C1
    Advance Thanks

    Reply
  127. Catalin Bombea

    December 14, 2015 at 8:28 pm

    Hi Anil,
    Please use google translate to provide clear details on what you want: https://translate.google.com/#hi/en/
    You already have the OneDrive file with the formulas and a functional solution, it’s not clear what you want, take your time to write the specifications in your own language.
    Thank you for understanding
    Catalin

    Reply

Trackbacks

  1. RealTime - Questions: "In excel: count number of times when a value appears in one column and the cell next to it is less than 2?" says:
    December 1, 2010 at 10:54 pm

    […] […]

    Reply
  2. Excel 2007 SUMIF and SUMIFS Formulas Explained says:
    September 29, 2010 at 8:34 pm

    […] Then check out our other conditional logic articles IF Statements Explained and COUNTIF and COUNTIFS Formulas Explained. […]

    Reply
  3. Excel 2007 – IF Statement Explained says:
    September 22, 2010 at 1:27 pm

    […] If you like the IF statement, take a look at our articles on SUMIF and SUMIFS Formulas Explained and COUNTIF and COUNTIFS Formulas Explained. […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x