Video extract as seen on YouTube, plus extended tutorial below.

Click the Full Screen button on the player to watch it in HD.

In this Microsoft Office 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 download a practice workbook so you can learn by reverse engineering the formulas used in this example. Note: this is an Excel .xlsx workbook. Please ensure your browser doesn’t change the file extension on download.

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

The COUNTIF extends the capabilities of the basic COUNT formula by allowing you to tell Excel to only COUNT items that meet a certain criteria.  New in Excel 2007 is the COUNTIFS formula, 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:

The function wizard in Excel describes the COUNTIF Formula 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. Download the workbook used in this example here.
Note: this is an Excel .xlsx workbook. Please ensure your browser doesn’t change the file extension on download.

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

COUNTIFS

The function wizard in Excel describes the COUNTIFS Formula 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 formula, 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 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 Statment 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 formula, 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 using the Google+1, LinkedIn, Facebook and Twitter buttons.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 133 comments… read them below or add one }

ravikumar April 16, 2014 at 7:52 pm

wow its super

Reply

Mynda Treacy April 16, 2014 at 8:13 pm

Cheers, Ravikumar :-)

Reply

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: http://www.myonlinetraininghub.com/help-desk
Catalin

Reply

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

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: http://www.myonlinetraininghub.com/help-desk to create a sample file and to provide more details on this problem.
Thank you,
Catalin

Reply

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

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

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

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

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

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: http://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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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!

Reply

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.

Reply

RKM November 1, 2012 at 2:29 pm

Thanks! Mynda.

Reply

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 .

Reply

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.

Reply

Mynda Treacy May 22, 2013 at 2:10 pm

You’re welcome, Haitham :)

Reply

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.

Reply

Previous post:

Next post: