How to countif with multiple criteria in Excel?

In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex, today, I will talk about some items for counting with multiple criteria.

MethodA: Countif with multiple criteria in same column

Count cells with multiple criteria based on text value
Count cells with multiple criteria between two values
Count cells with multiple criteria between two dates

MethodB: Countif with multiple criteria in same column with a useful feature

MethodC: Countif with multiple criteria in multiple columns

More tutorials about countif cells ...

Countif with multiple criteria in same column

Count cells with multiple criteria based on text value

For example, I have the following data which contains some products, and now I need to count the number of KTE and KTO which are populated in the same column, see screenshot:

To get the number of KTE and KTO, please enter this formula:

=COUNTIF($A$2:$A$15,"KTE")+COUNTIF($A$2:$A$15,"KTO")

And then press Enter key to get the number of these two products. See screenshot:

Notes:

1. In the above formula: A2:A15 is the data range that you want to use, KTE and KTO are the criteria that you want to count.

2. If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…

Tips:
Another compact formula also can help you to solve this problem: =SUMPRODUCT(COUNTIF($A$2:$A$15,{"KTE";"KTO"})), and then press Enter key to get the result.
And you can add the criteria just as =SUMPRODUCT(COUNTIF(range,{ "criteria1";"criteria2";"criteria3";"criteria4"…})).

Count cells with multiple criteria between two values

If you need to count the number of cells that the value is between two given numbers, how to solve this job in Excel?

Take the below screenshot as an example, I want to get the result of the number that is between 200 and 500. Please do with these formulas:

Enter this formula into a blank cell where you want to locate the result:

=COUNTIF($B$2:$B$15,">200")-COUNTIF($B$2:$B$15,">500")

And then press Enter key to get the result as you need, see screenshot:

Note: In the above formula:

B2:B15 is the cell range that you want to use, >200 and >500 are the criteria you want to count the cells;
the whole formula means, to find the number of cells that have a value greater than 200 and then subtract the count of cells with a value greater than 500.

Tips:
You can also apply the COUNTIFS function to deal with this task, please type this formula: =COUNTIFS($B$2:$B$15,">200",$B$2:$B$15,"<500"), and then press Enter key to get the result.
And you can add the criteria just as =COUNTIFS(range1,"criteria1",range2,"criteria2",range3,"criteria3",...).

Count cells with multiple criteria between two dates

To count the cells based on date range, the COUNTIF and COUNTIFS functions can do you a favor as well.

For example, I want to count the cell numbers that the date is between 5/1/2019 and 8/1/2019 in a column, please do as this:

Enter the below formula into a blank cell:

=COUNTIFS($B$2:$B$15, ">=5/1/2019", $B$2:$B$15, "<=8/1/2019")

And then press Enter key to get the count number, see screenshot:

Note: In the above formula:

B2:B15 is the cell range that you want to use;
>=5/1/2018 and <=8/1/2019 are the date criteria you want to count the cells;

Click to know more about the COUNTIF function...

Countif with multiple criteria in same column with a useful feature

If you have Kutools for Excel, with its Select Specific Cells feature, you can quickly select the cells with specific text or cells between two numbers or dates, and then get the number you need.

Tips:To apply this Select Specific Cells feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Select the list of cells that you want to count cells based on criteria, and then click Kutools > Select > Select Specific Cells, see screenshot:

2. In the Select Specific Cells dialog box, please set the operations as you need, and then click OK, the specific cells have been selected and the number of the cells are shown in the prompt box as below screenshots shown:

Note: This feature also can help you to select and count the cells between two specific numbers or dates as the following screenshots shown:

Download and free trial Kutools for Excel Now!

Countif with multiple criteria in multiple columns

If there are multiple criteria in multiple columns, such as following screenshot shown, and I want to get the number of KTE whose order is greater than 300 and the name is Ruby.

Please type this formula into the desired cell:

=COUNTIFS($A$2:$A$15,"KTE",$B$2:$B$15,">300",$C$2:$C$15,"Ruby")

and then press Enter key to get the number of KTE which you need.

Notes:

1. A2:A15 and KTE are the first range and criterion you need, B2:B15 and >300 are the second range and criterion you need, and the C2:C15 and Ruby are the third range and criterion you based on.

2. If there are more criteria you need based on, you just need to add the range and criteria within the formula, such as: =COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, range4, criteria4, … )

Tips:
Here is another formula also can help you: =SUMPRODUCT(--($A$2:$A$15="KTE"),--($B$2:$B$15>300),--($C$2:$C$15="Ruby")), and then press Enter key to get the result.

Click to know more about the COUNTIFS function...

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

53 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
alwin.dare@gmail.com · 8 months ago
Hi,

I need to count the different names in a single column which is repeated more than one time..

aaaa
aaaa
aaaa
aaaa
bbbb
bbbb
bbbb
bbbb
bbbb
bbbb
cccc
cccc
cccc
cccc
cccc

i dont want to mention names in formula, because its many names in the column and i dont want pivot table also,
I tried with this formula, but its counting the blank also..
=SUMPRODUCT(1/COUNTIF(C10:C500,C10:C500&""))

Result what i need is -

aaaa - 4
bbbb - 6
cccc - 5
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 8 months ago
  
  Hi, alwin,
  To solve your problem, you should apply the following array formula:
  =SUM(IF($A$1:$A$11=C1,1,0)), after entering this formula, please press Ctrl + Shift + Enter keys together to get the correct result.
  See the below screenshot:
  
  doc-1.png
  
  Reply
To post as a guest, your comment is unpublished.
E. Safi · 9 months ago
Hi, I am wondering if someone can help me figure our how to combine COUNTIFS formulas. For example, I need to present this more efficiently:

=COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,PH,VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,"Both", VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,PH, VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,"Both", VAR5,"Pres")

So I need counts if VAR1 =Yes; VAR2 = 2016; VAR3=Q1; VAR5=Pres; AND VAR4= PH or Both; AND VAR6=PH or Both. I think the way I have it does the job, but I'm sure there is a more concise way to write the formula as it may get very long as I add criteria to it.
- Reply
To post as a guest, your comment is unpublished.
Yodhey · 1 years ago
Great content, very helpful! Thank you so much for sharing this information!
- Reply
To post as a guest, your comment is unpublished.
ct · 1 years ago
hi, need to countif from two file with range and selection
=IF((COUNTIFS([SCCD.xlsx]open!$AV:$AV,">6",$AV:$AV,"<10"),[SCCD.xlsx]open!$T:$T,C2,[SCCD.xlsx]open!$V:$V,"SLAHOLD")+COUNTIFS([KTPH.xlsx]open!$H:$H,"SLAHOLD",[KTPH.xlsx]open!$J:$J,">6",!$J:$J,"<10",[KTPH.xlsx]open!$G:$G,C2))=0,"",COUNTIFS([SCCD.xlsx]open!$AV:$AV,">6",$AV:$AV,"<10"),[SCCD.xlsx]open!$T:$T,C2,[SCCD.xlsx]open!$V:$V,"SLAHOLD")+COUNTIFS([KTPH.xlsx]open!$H:$H,"SLAHOLD",[KTPH.xlsx]open!$J:$J,">6",$J:$J,"<10",[KTPH.xlsx]open!$G:$G,C2))

got error.. can someone advice
- Reply
To post as a guest, your comment is unpublished.
Rajinder · 1 years ago
Hi. I need to select information of cells range h6 to m126. I then need to count how many of these are male (and female) from cells range c6 to c126. I have tried =countifs($h$6:$m$126,”B1”,$c$6:$c6$C126,”M”) but when I enter it is coming up as #value!
Any advice will be gratefully received.

Thanks.
- Reply
To post as a guest, your comment is unpublished.
Marizze · 1 years ago
Hi, I have trouble making a formula to this. kindly help me if there are existing formula for this.. thanks! pls see below.

There are diff. zone in a column.. each row has open or closed remarks. How can I add all the open and closed items for each zone?

Column: ZONE No. Remarks
1 open
2 open
1 open
1 close
This is the sample data.. I wanted to know how many are still open/closed per zone number.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, Marizze,
  Maybe the below formulas can solve your problem:
  All open item with zone number 1: =COUNTIFS(B2:B8, "open",A2:A8,"1");
  All close item with zone number 1: =COUNTIFS(B2:B8, "close",A2:A8,"1")
  
  with the same formulas to get other zone number result as you need.
  
  Please try, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
Excellnotsolent · 1 years ago
Ok So I have a complicated one

I need to pull data to a table to show :

The total number of overdue items based on the date now for a specific centre

So the total number of overdue items in the grace centre where the data table contains multiple centres
the formula I use for the overdue items is =countif(rawdata!I:I,''<''&D12) - where D12 formula contains =NOW()-0

This brings back the overdue items based on date for all centres but I want it specially for those which are only overdue for the grace centre and the centre data is in column E.

I have tried adding ,rawdata!E:E,''Grace''), but it comes back too many arguments

Can I not use multiple formula for the
- Reply
To post as a guest, your comment is unpublished.
info.rajandahal@gmail.com · 1 years ago
I HAVE A TABLE OF STUDENTS WITH GENDER IN A COLUMN AND RACE IN ANOTHER COLUMN. HOW CAN I FIND THE NUMBER OF A SINGLE RACE BY MALE OR FEMALE DIFFERENTLY?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hello, Rajan,
  To solve your problem, you should apply the below formulas:
  Count the number of Male: =COUNTIF($B$2:$B$12,"Male");
  Count the number of Female: =COUNTIF($B$2:$B$12,"Female")
  
  Please try, hope it can help you!
  
  count-1.png
  
  Reply
To post as a guest, your comment is unpublished.
rowed · 1 years ago
I'm trying to count the number of cells in a given row that have the same text and formatting (ie same cell fill color). Can anyone help me with this issue? Thanks in advance
- Reply
To post as a guest, your comment is unpublished.
cris · 2 years ago
hi. hope i can get help with the setting up the correct data table and how to extract specific information from the table. here are the variables:

we have multiple products under several different categories
we have multiple sales rep assigned to specific territories
i need to track their individual sales per product
i also need to break down their sales per month, quarter, and on an annual basis (still per category, product and area)
i need to compare the data of their actual sales versus their targets

what's the correct data set, and the correct formula for it? thanks

with these, i can then make a pivot table out of the data table.
- Reply
To post as a guest, your comment is unpublished.
MS · 2 years ago
Can multiple arrays are possible 8n single countifs?

Countifs(range,{criteria: criteria},range,{criteria: criteria}, range,{criteria: criteria})
- Reply
- To post as a guest, your comment is unpublished.
  Josh · 1 years ago
  
  Yes but you need to ensure that you wrap a SUM() formula around your countif so that it totals the results that are applicable as per the countif.
  
  Reply
To post as a guest, your comment is unpublished.
Theo Bourgery · 2 years ago
My column A contains a set of different categories. My column B contains dates as "1 October 2018", but my filter is by year ("2018").
Both [ =SUMPRODUCT(--(A:A="Category x"),--(B:B="2018") ] and [ =COUNTIFS(A:A,"Category x",B:B,"2018) ] give me a result of zero, which is evidently incorrect. Could there by something wrong with my date filter?

Thanks!
- Reply
To post as a guest, your comment is unpublished.
tafliard@gmail.com · 2 years ago
I am not able to upload the image of my data. neither .png file nor .bmp file upload. Any advice anyone?
thanks
Dave U.
- Reply
To post as a guest, your comment is unpublished.
tafliard@gmail.com · 2 years ago
I'm showing 3 tables. The middle table shows lab data. In my example, I want to count any platelet values (PLAT) that have a supporting event in the left table, with matching dates. My formula in Column M looks like this:

SUM(COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), F:F, ">="&EDATE(L13, 0)) + COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), G:G, "AFTER"))

This formula works; however, I must HARDCODE the values "Thrombocytopenia" and "Platelet count decreased". I would like it to work dynamically where it references Column Q, or perhaps cells Q10 and Q11, where it uses that text based on the matching lab name (e.g., PLAT). In essence, I'm looking for a nested OR statement that behaves dynamically within the middle of a COUNTIFS statement. Tricky..... maybe I need to learn how to use --SUMPRODUCT. Notice the NEUT lab test in the far right table which has 3 "events" that would be acceptable to find in the leftmost table... I would want them to be counted, eventually when I find a good formula.

thanks - Dave U
- Reply
To post as a guest, your comment is unpublished.
Susan · 2 years ago
I have another request if possible, I am looking for a formula that will give me staff holiday cover, there are 5 people and each person has to cover at least one day over Christmas and New Year, each person has to give me what holiday entitlement they have left for the year so that I can calculate the cover.
- Reply
To post as a guest, your comment is unpublished.
Susan · 2 years ago
Just wondering if you can help, I need a formula to decide a Pass or Fail as the result. The following data is in 6 columns with either a yes or no in them, if the results are all “yes” then this is a pass, if any one column has a “no” then this is a fail. I have tried various formulas with “IF” “AND” “OR” but nothing gives me what I am looking for. Thank you in advance.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, Susan,
  To solve your problem, you can apply this formula:
  =IF((COUNTIF(B2:G2,"no")),"Fail","Pass")
  Change the cell references to your own.
  Hope it can help you!
  
  未标题-1.png
  
  Reply
To post as a guest, your comment is unpublished.
ryogi9966@gmail.com · 2 years ago
I have a work Count the students branch wise and course wise i have Ex. A1 course like B.tech or Diploma A2 Have Branch EEE,ECE and soon i want count diploma all banchs and btech all banchs any formula for that
sheet enclosed
- 1.png
- Reply
To post as a guest, your comment is unpublished.
Patricia · 2 years ago
please assist. I want to count the number of blank columns next to a certain name.

I am trying to use "=countifs", but struggling with the blank part...

for example:
column A Column B
Lesley Nico
Lesley Sipho
Lesley
Lesley Floyd
Bronz Sam
Bronz Gift
Bronz
Bronz

Result should be:
Lesley 1
Bronz 2
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hi, Patricia,
  To count all blank cells based on another column data, the below formula may help you, please try it.
  
  =COUNTIFS(A2:A15,"Lesley",B2:B15,"")
  
  Hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
Jason · 2 years ago
Okay I'm soooo stuck with this formula. Here's what I have
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:G77=FALSE))

Now I also have a column H. I need the formula to count if G and H are false but if I do
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:H77=FALSE))

or
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:G77=FALSE)--(H2:H77=FALSE))

it won't allow either. Please help!
Thanks
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hi, Jason,
  The formula in this article you applied is based on the criteria "and", and your problem is to apply "or", so you can use the following formula to count all "FALSE" in the two columns:
  
  =SUM(COUNTIF(F2:G77,{"False"})).
  
  Please try it, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
Joxyz · 2 years ago
Hi,

I have a large document of data in the below format:

Offer Start date End date
Offer 1 12/08/2018 18/08/2018
Offer 2 13/08/2018 26/08/2018
Offer 3 13/08/2018 26/08/2018
Offer 4 14/08/2018 01/09/2018
Offer 5 20/08/2018 26/08/2018
Offer 6 27/08/2018 08/09/2018
Offer 7 09/08/2018 12/08/2018
Offer 8 08/08/2018 18/08/2018

I need to calculate a number of offers avaliable each week. The final document should be in the format below:

WeekNum Start date End date Offer count
31 30/07/2018 05/08/2018
32 06/08/2018 12/08/2018
33 13/08/2018 19/08/2018
34 20/08/2018 26/08/2018
35 27/08/2018 02/09/2018
36 03/09/2018 09/09/2018
37 10/09/2018 16/09/2018

In theory, it's relatively easy. You can use COUNTIFS to calculate cells when the offer end date is between the week start date and week end date. The problem however is when offer lasts for more than 1 week. Eg. Offer 8 lasts until December 31 which means it needs to be counted as one every week from week 32 to week 53. Do you have any ideas how this could be calculated?

Thanks!
- Reply
To post as a guest, your comment is unpublished.
Harry · 2 years ago
Hello,

Good day ...

We have two results from an item number from different location

that will show like
Eg.
C1 C2 C3
item#123 Required Not Required not Required

From this this 2 answers the final answer will be 'required' if required available on column

If 'required' not available then answer will be 'Not Required'

In final cell I would like to get one answer
- Reply
To post as a guest, your comment is unpublished.
Nandu · 2 years ago
I have a column with Multiple names and i wanted to find the count of the names except a perticular name. Can some body help me??
Column Values: a b a b c d e f a b a x y z (Here i want count of a & b & c) without using countif(A:A,"a")+countif(A:A,"b")+countif(A:A,"c").
- Reply
- To post as a guest, your comment is unpublished.
  Hannah · 2 years ago
  
  =COUNTIFS(rng,"<>x",rng,"<>y")
  Where rng is the range e.g. A:A
  X or why is the thing you do not want to count
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  shravan · 2 years ago
  
  If the range is A:H, how can apply the formula..?
  
  Reply
To post as a guest, your comment is unpublished.
Mario · 2 years ago
lets say I have these values, 1 to 1.5 will be a 1, 1.6 to 3 will be a 2, 3.1 to 4.5 will be a 3 and 4.6 to 6 will be 4. How do I put that formula for several values, like lets say I have a list with 100 items and their values vary between 1 and 6. So every time I log in a number it will automatically give me the value. Thank you.
- Reply
To post as a guest, your comment is unpublished.
Alex · 3 years ago
ColA, ColB

Count Range is in ColB, nd Count Criteria is in Col A how can I count , pls give soln
- Reply
To post as a guest, your comment is unpublished.
akramwaseim · 3 years ago
=IF(Working!C3=Working!B7,SUM(COUNTIFS(Gender,"Male",Category,{"Bombay","Pune"},Class,{"1","2","3","4"})))

Am not getting the correct answer for this, getting output for only 1st criteria.

*(Working - Sheet Name)

Kindly help
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, Waseem,
  Can you give an example of your problem?
  You can attach a screenshot here!
  Thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  akramwaseim · 3 years ago
  
  I dono its not uploading image, trying again,,
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  akramwaseim · 3 years ago
  
  Thanks for ua consideration... Below is the formula again..
  
  If B14 matches with A17, then I want the number of counts of 'Male' from Bombay and Pune and they should be in class 1 to class 4.. (For this answer should be 3, but am not getting that)
  
  =IF(B14=A17,SUM(COUNTIFS(Gender,"Male",Category,{"Bombay","Pune"},Class,{"1","2","3","4"})))
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  akramwaseim · 3 years ago
  
  Here is the screenshot..
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  skyyang · 3 years ago
  
  Hello, you can upload the screenshot from here:
  
  未标题-1.png
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  akramwaseim · 3 years ago
  
  I tried yesterday,, N trying again now,, hope it uploads
  
  Capture6.PNG
  
  Reply
To post as a guest, your comment is unpublished.
nk · 3 years ago
I'm trying to find a formula that helps me tally how many unique numbers I have in column A for every row that has numerical value (a date) in column B. Column A has multiple duplicates. Column B also has text cells and blank cells. Is that even possible?
- Reply
To post as a guest, your comment is unpublished.
David · 3 years ago
I have a spreadsheet where I am trying to find a particular value in a column, from those that were found I need to find another value in another column, and a third column. Would this be COUNTIF calculation, how could I do this?
- Reply
To post as a guest, your comment is unpublished.
paulb · 3 years ago
i'm trying to count the number of cells where the date is within a certain range, no problems, i have the formula =SUMPRODUCT((P8:P253>=DATEVALUE("1/7/2017"))*(P8:P253<=DATEVALUE("31/07/2017"))) also trying to count where the product of another cell is another condition, say A1, again no problems, i have =COUNTIF(E8:E253,"A1") but how do i combine the two as conditional where i get the number of cells between a certain date range that contain a specific entry? thanks
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, Paul,
  the following formula may help you:
  =SUMPRODUCT(--($B$2:$B$11>=$E$2), --($B$2:$B$11<=$E$3), --($A$2:$A$11=$E$1))
  please view the screenshot for the details, you should change the cell references to your need.
  
  Hope it can help you!
  Thank you!
  
  doc-1.png
  
  Reply
To post as a guest, your comment is unpublished.
paul · 3 years ago
i'm trying to count the number of cells where the date is within a certain range, no problems, i have the formula =SUMPRODUCT((P8:P253>=DATEVALUE("1/7/2017"))*(P8:P253<=DATEVALUE("31/07/2017"))) also trying to count where the product of another cell is another condition, say A1, again no problems, i have =COUNTIF(E8:E253,"A1") but how do i combine the two as conditional where i get the number of cells between a certain date range that contain a specific entry? thanks
- Reply
To post as a guest, your comment is unpublished.
Sbetarice · 3 years ago
I have a spreadsheet where I need to count column v if it is equal to "EQ" and if columns j thru u are blank.
- Reply
To post as a guest, your comment is unpublished.
prasad · 3 years ago
how to count a , b , c , d in excel . i want to count only a b d in excel not c .

please tell formula
- Reply
- To post as a guest, your comment is unpublished.
  BASHIR AHMED · 3 years ago
  
  A
  B
  C
  D
  
  =COUNTIF(B2:B5,"A")+COUNTIF(B2:B5,"B")+COUNTIF(B2:B5,"D")
  
  Reply
To post as a guest, your comment is unpublished.
Shalini · 3 years ago
SUMPRODUCT(COU NTIF(A:A,{C1;C1 &",*";"*,"&C1," *,"C1&",*"}))
In this, C1 needs to be given in double quotes. Please check and verify
- Reply
To post as a guest, your comment is unpublished.
RANGYF · 3 years ago
On formula =SUMPRODUCT(COUNTIF(range,{ "criteria";"criteria";"criteria";"criteria"…})), what if i want the content from some cell to form the criterias? Like this =SUMPRODUCT(COUNTIF(A:A,{C1;C1&",*";"*,"&C1,"*,"C1&",*"})), here i got syntax error. Seems it's illegal to use cell reference in {} array.
- Reply
To post as a guest, your comment is unpublished.
Jesse · 3 years ago
On formula =COUNTIFS(A2:A11,"KTE",B2:B11,">=200") above. Why can you not note cell "A2" as the criteria instead of spelling out KTE. I know in this example KTE is short but not the case in my sheet.
- Reply
To post as a guest, your comment is unpublished.
Stephen · 4 years ago
In your example above, how to find order>=200 for product KTE and KTO but not using countifs(..."KTE"...) +countifs(..."KTO"...)
- Reply
To post as a guest, your comment is unpublished.
Lori · 4 years ago
=COUNTIFS(B3:B109,"OPEN",I3:I109,"
- Reply

Login to your account

Password Reset