# COUNTIF Function With Multiple Criteria

Jun 7, 2006

I need to count rows that meet 2 criteria.
I have seen this help page
http://www.ozgrid.com/Excel/count-if.htm
but that counts rows with "criteria 1" OR "criteria 2"...
I need to count rows that fulfill "criteria 1" AND "criteria 2"
ie - count the rows that have todays date AND a cell that says "COMPLETE"

ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???

## Countif Function Work On Multiple Criteria

Apr 15, 2009

Can countif function work on multiple criteria to look on?

this are the criteria.

SA SL 1.0SA SL 0.5SA VL 1.0SA VL 0.5SA SLWOP 1.0SA SLWOP 0.5SA VLWOP 1.0SA VLWOP 0.5SUSPRD

## CountIF() On Multiple Criteria ..

Jan 9, 2010

i want to use Countif() when 3 ceritera are true

ceritera
--------
1) Branch Name
2) Status

can this be done by any mean

## Using Countif With Multiple Corresponding Criteria

Feb 12, 2010

what I'm trying to do is to make a logbook for a machining center. Each part has an op10 and an op20, essentially front and back. And each part number falls into the category of OS or FS. I've used AND logic to make tables in hidden columns to be used by a countif statement to determine my totals. I.e. to determine if a scroll is completed, op20 has a a value of 1 AND column C is "OS".

I use
=IF(AND(A9=1;C9="OS");1;" ")
Then I countif criteria is 1 in the column i created with that statement.

That works just fine. Now what I want to do is to be able to create daily totals of OS and FS by simply modifying a variable date in a formula. So I'd like to essentially say: Countif Column C =OS and Corresponding column D = 1, and corresponding Shift date = 10.02.12(date to be variable). I'm at a wall here. Is there any way to do this somewhat simply?

## Countif Multiple Criteria.

Oct 9, 2008

I'm trying to count multiple criteria from a second page in a work book, all the formulas i've looked up and tried do not seem to work... here's the formulas i've tried. DKOBULAR is the name of the 2nd page. D is the column used for the different resolves.

=COUNTIF(DKOBULAR!D:D="resolveA")+COUNTIF(DKOBULAR!D:D="resolveB")+COUNTIF(DKOBULAR!D:D="resolveC")+ COUNTIF(DKOBULAR!D:D="resolveD")

=COUNT(IF(DKOBULAR!D:D="resolveA",IF(DKOBULAR!D:D="resolveB",IF(DKOBULAR!D:D="resolveC",IF(DKOBULAR! D:D="resolveD")))))

## Multiple CountIf Criteria

May 19, 2009

In Excel 2003, I need a countif to check for 2 criteria: (1) the left function looking for the value "Territory" in column A and (2) value > 0 in column G. I only want to count the rows where both the criteria are met. I have tried different combinations of countif including "and" in the formula, but I cannot get it to work. What is the proper syntax?

## Countif Multiple Criteria

Aug 16, 2007

I need to create a formula which counts the number of times a username appears in column X based on a given value in Column Y. This data will not be static - will need to be refreshed regularly. Countif does not support multiple criteria - what is the best way to create this formula?

## Countif Formulas With Multiple Criteria?

May 26, 2014

I need a formula to count cells E7:E500 that say "Submitted" but only if cells R7:R500 say "PPB".

My original formula below is counting all cells except for the ones that say "PPB"

=COUNT(IF(E7:E500="Submitted",IF(R7:R50="PPB",'E7:E500)))

## Multiple Criteria Countif Or Sumproduct

Sep 16, 2009

I haven't been this deep into excel before. The deeper I look, the more potential I recognize, the more amazed I get. That being said, I have come to a tough count issue. Let me attempt to explain as precisely as possible.

My current worksheet is large but I am only particularly concerned with two columns of information (Regions) and (Days). The logic I am attempting is something along the lines of Count If Region = East, or West, and Days is greater than 0, less than 60.

I am open to any and all suggestions on how to tackle this situation. I have been able to achieve similar counts by using pivot tables but the dynamic nature of these two columns presents some difficulties that my “new user” mind has been unable to work through.

## Using Countif(s) To Count Multiple Criteria

Nov 25, 2013

I have a bunch of cells that i want to count in a table based on 2 criteria. The first would be checking whether or not it matches a certain text which i can do. Now i have a column for "Completed Date" and "Deadline Date". Each deadline date is different. I know i need some sort of array function in there to compare all the completed dates vs deadline dates but i just can't think of it. This will be the second criteria.

## Countif Array With Multiple Criteria?

Sep 13, 2012

=COUNTIF(A\$2:T\$1001,V2 & W2 & X2 & Y2)

I want to copy and paste this formula down column Z.

I want to count or add 1, only when a row of 20 cells (A to T) contains all 4 criteria in V W X Y.

## Multiple COUNTIF Statements With Different Criteria

Dec 21, 2007

I'm a marketer trying to find all calls within a week that are longer than 300 seconds. I'd prefer not to use a pivot table because of client issues.

=COUNTIF(A:A,1)-COUNTIF(F:F,"

## COUNTIF With Multiple Range And Criteria

Mar 6, 2008

I need to add all cells within multiple ranges where the characters "A", "S" and "D" occur.

=SUMPRODUCT(COUNTIF(INDIRECT({"C22:C25","C28:C32"}),{"A","S","D"}))

## Multiple Criteria For Countif Functions

Nov 11, 2006

I need to create a formula that counts the number of times that an age range appears within a column. In column G, there is a list of ages based on a demographic collection. The ages range from 13 to 50+. I want to designate another cell to count the number of times the characters between 13 and 18 occur within that column. I have =COUNTIF(G8:G20,"13") How do I add "14", "15", "16", "17", and "18"?

## Using Countif Function For Multiple Worksheets

Jan 28, 2010

I am trying to use countif to count the number of times a unique items occurs in multiple worksheets.

For example, I want to count number of times "ITEM1" occurs in row 1 of sheets1, sheets2, sheets3, sheet4, etc. It may look like this:

Sheet1 = 4 entries
Sheet2 = 22 entries
Sheet3 = 5 entries
Sheet4 = 10 entries

So the entire count would be 41 total.

## Countif Function With Multiple Sheets

Feb 27, 2009

Can I use the "countif" function using a group of sheets as the range and the criteria on a separate sheet or manually typed in?

I have tried and continue to get the #VALUE error.

OR

Can I do the same thing and perform the "countif" function by using a specific value as the way to count?

## Excel 2003 :: COUNTIF With Multiple Criteria

Jan 26, 2013

How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.

Start Date
11/1/2012

End Date
11/31/2012

Assigned To
Created
Fixed

[Code] .....

## Excel 2003 :: How To Use Countif For Multiple Range And Criteria

Jan 1, 2014

How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

## Countif Multiple Conditions: Look At Two Columns And Set A Criteria To Count

Jan 15, 2010

I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.

A B
1 1.00
2 Yes 4.78
3
4 5.00
5 Yes 4.89
6 11.99

So this example would count 3

## 2003: COUNTIF/SUMPRODUCT, Multiple Criteria W/Wildcard

Nov 24, 2008

I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?

## Ifs Function Using Multiple Criteria

Dec 15, 2013

I am stuck trying to add another criteria to an existing formula.

On my sheet "Data" i have the following formula in column AF2

=IF(Z2="","A",Z2-AE2)

If Z2 (Payment Date) is Blank then return "A" in AF2, however if Payment Date has an actual date in the cell then minus AE2 (Received Date) from the Payment date.

This gives me the "Receipt to Pay" number E.g. 6 days from receiving to paying an invoice.

I now need to add another criteria to this and i cant fathom it.

I need to do the following:

If Payment Date (Z1) is Blank and Invoice Status (T2) is "Loaded" then Minus Received Date (AE2) from Todays Date, while also retaining the last part of original Formula Z2-AE2 if the first criteria is not met.

(I have put =TODAY() in Cell AG1 for ease).

What do i need to do to add this into the formula. Do i need to use IFS function?

## IF Function With Multiple Criteria

Feb 4, 2010

MathScienceLanguageOverall
PassPassPass----
PassFailPass----
PassFailFail----
FailFailFail----

What formula should I use to show "Pass" if a student passed all three subjects, "FAIL" If student did not pass in any subject and "TRY AGAIN" if he passed at least one subject? I can't seem to accurately plot "IF" formula. Should add in "AND" / "OR" as well?

## Function Using Multiple Criteria

May 5, 2009

Bid Manager Probability of Success Total Fee

cp 25% 675,000
rz 40% 18,383,541
nw 50% 8,700,000
cp 10% 4,500,000
ap 50% 1,200,000
kd 50% 49,500
sl 25% 3,200,000
bc 20% 310,000
sl 20% 3,460,000
nm 30% 6,750,000
at 50% 23,000,000
ap 30% 367,500
rn 40% 13,750,000

I have a list of commodity and percentages of success with fees in the 3rd column
I need a function to count the records with multiple criteria e.g greater than 20% but less than 30%.

Second i need a function when it complies to previous formula it should give fee total pertaining to that count.

## Use IF/Lookup Function In Multiple Criteria

Apr 30, 2009

I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)

I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )

excelformula.gif

pricing.xls

## Lookup Function On Multiple Criteria

Mar 21, 2012

I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.

Please see tables below for the reference.

Document 1
Document 2 ID Start date End Date Data
ID Date Data 11 01/01/2009 31/12/2010 Data 1
11 05/05/2011 22 01/01/2010 31/12/2010 Data 2
22 08/08/2010 22 01/01/2011 31/12/2011 Data 3
33 01/01/2012 33 01/01/2012 31/12/2012 Data 4
11 02/02/2009 33 01/01/2010 31/12/2011 Data 3

11 01/02/2011 31/12/2011 Data 2

## Find Function With Multiple Criteria?

Jul 20, 2012

Find Function with Multiple criterias. Ex-

KNE XE KN ACDA

If this is my data in Column A, I need to find the names which contains " R " and " X " in 16th digit. I can do it using find formula twice but is there a way to do it in on shot..?

The formula I'm using is =FIND(" R ",A1,16) it gives me numbers like 16, 17, 19, 20 etc but I'm interested only in 16 digit so I just filter for 16 and copy the data to seperate tab.

## Using Multiple Criteria In Second Argument Of CountIfs Function

Feb 26, 2014

Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.

=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")

Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.

=COUNTIFS('P12 Source'!\$H:\$H,A19,'P12 Source'!\$F:\$F,'P12 Source'!\$F\$75)

## Using MATCH And INDEX Function With Multiple Criteria?

Feb 9, 2010

We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.

How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?

I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.

## Worksheet Function Max With Multiple Criteria In Excel VBA

Apr 30, 2013

I would like to use worksheetfunction max with multiple criteria in excel VBA. I have the following data:

Col A
Col B

a
1

a
2

a
3

[code]....

In excel i was able to use the formula =MAX((\$A\$2:\$A\$6=\$A2)*(\$B\$2:\$B\$6)).

I would like to be able to do the same in VBA using the worksheetfunction.max. I was trying something like

worksheetfunction.MAX((\$A\$2:\$A\$6=\$A2)*(\$B\$2:\$B\$6)) and it didnt work. How to proceed?

## VBA Match Function With Multiple Criteria To Return Row Number

Jul 16, 2012

I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.

I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.

I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.