# How To Use Count With Multiple Conditions

Aug 4, 2005

I have a table in Excel:

The first row is time in years.

The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"

I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

how to revise the formula?

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")

## Count Unique Logs With Multiple Conditions Of Multiple Sheets

Oct 8, 2007

I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.

This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.

The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.

Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))

Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.

2. I don't know how to make it work across several sheets.

This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.

SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))

## SUMPRODUCT - Count Multiple Conditions

Feb 16, 2006

Ive started using the sumproduct function to count multiple conditions which is useful

howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?

the only way i can think is like the below

=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))

Rather than having to eliminate red and yellow i would like to say is green or blue.

## Count Cells Using Multiple Conditions

Jul 1, 2008

I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.

I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes

## Count Rows Matching Multiple Conditions

Nov 17, 2009

I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.

When "dates" or J2:J25 is less than or equal to today's date
AND
"HTeam" or W2:W25 is equal to Civil
AND
"Percent" or K2:K25 is equal to 100

## 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

## Count Number Of Unique Values Based On Multiple Conditions

Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

## Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

## Count Unique Values With Multiple Conditions Array Method

Apr 28, 2014

I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.

I've attached the XL file for your review of the project.

=SUM(IF(FREQUENCY(IF(('TX1'!\$B\$2:\$B\$15=B2)*('TX1'!\$C\$2:\$C\$15=C2)*('TX1'!\$D\$2:\$D\$15=D2),MATCH('TX1'!\$A\$2:\$A\$15,'TX1'!\$A\$2:\$A\$15,0)),MATCH('TX1'!\$A\$2:\$A\$15,'TX1'!\$A\$2:\$A\$15,0))>0,1))

## Advanced Filters - Having Multiple Filters And Conditions (Unique Count)

Jun 7, 2006

I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.

## Conditional Sum & Count: Count Request Matching The Crateria Of Date And Other Conditions

Nov 17, 2007

see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.

## Count By 2 Conditions

Nov 1, 2009

i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.

Dim unassigned As Long
unassigned = 0
For rowvar = 18 To 504
If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then
If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then
unassigned = unassigned + 1
End If
End If
Next
Sheet8.Cells("b3").Value = unassigned
End Sub

## Sumproduct To Count From 2 Conditions...

Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).

=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

## Count Cell Value If Conditions Met

Jan 22, 2009

I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.

## Count A Cell Only If It Met One Of Three Conditions

Apr 13, 2007

What formula would I use to count a cell only if it met one of three conditions?

ie only if the cell equalled 1, 2 or 3 but not 4?

## Count For Instances With 2 Conditions

Feb 13, 2009

40,000 rows,

Column A is a Port Code . . . always 4 digits

Column B is a 2 digit code representing a mode of of transportation.

I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .

## Count Conditions On Tabs

Mar 29, 2007

There are a variety of tabs on this database. These tabs track the large customers and specific brands.

In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007.

Using formulas such as: ...

## Count Occurrence Based On Two Conditions?

Sep 24, 2013

I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".

## Count Data In Cells Using Two Different Conditions

Nov 3, 2009

I have a need for a formula that is able to count the data in column that coresponds to data in another column.

Example work sheet attahced for a quikie look.

## Count And Sum Cells Meeting Two Conditions

May 20, 2007

I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.

I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.

Next, I fill in each day with an employee name.

Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:

Jones:
Friday 4 (total number of days jones is in a blue box)
Saturday 5 (...on a green box...and so on...)
Sunday 3
Holiday 2
Monday-Thursday 50

For each employee name. Sounds easy, right? I can't get it to work!

## Evaluate Two Ranges And Count Two Conditions That Met

Feb 27, 2014

I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...

## Count Entries With Two Previous Conditions

Apr 25, 2008

I am trying to count the number of entries in range BH3:BH621 when the cells in range B3:B621 = "Acting" and the range D3:D621 = "Feb"

I can do it with either the B range or the D range, but not both together.

## Count Observations Based On Specific Conditions With VBA

Sep 11, 2012

I would like to write a macro that would count observations from a database extract based on specific conditions and generate a report.

My database extract looks like this :

Client name
Info1
Info2
Info3

Bob
aaa
aaa
aaa

[Code] ....

I would like to have an efficient and simple VBA code to count the observations. Something that would store the result of query like those:

# 'client name' = Bob : 2
# 'client name' = Bob AND info1 = aaa : 1
# Info3 = bbb : 2
# 'client name' = Bob and Info1 = aaa OR Info1 = bbb : 2

I am aware of DCOUNT function, but I am not too sure how to translate it in VBA. I also think that using autofilter and counting the visible rows may be subject to bugs.

## Count Cells Based On Conditions In That And Other Cell(s)?

Jan 28, 2013

I have an issue log containing (separate) columns with dates, numbers and text.

One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)

I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).

After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.

So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".

After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.

## Count Number Of Occurrences If Two Conditions Are True

Apr 12, 2014

I'm trying to count the number of occurrences where two conditions in a table are true.

I have a table that has two columns for ratings; impact and probability. Each can be scored 1-5 This creates a matrix table of possible scores from 1 - 25 (image attached)

I want to COUNT the number of items in each of the boxes (not the total score). For example, how many are Impact 5 and Probability 5 (25 total); how many are Impact 4 and Probability 2 (8 total), and so on. Basically a count of the each of the intersections in the matrix.

Something like "Countif Impact is 5 AND Probability is 5"

Is it possible to count something once, checking for multiple conditions?

## Match And Count Unequal Ranges With Conditions

Oct 13, 2009

I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.

For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).

The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).

For example: .....

## Count Blanks In Range With Another Dynamic Conditions

Mar 15, 2013

Assume this Data in a spreadsheet

I want to count blank "PI" based on "Product name" & "Product date" occurence together

So I created intermediate field "IS Blank", and I dunno what formula can give me the below results

My obective to get this result
CountBlank for PI = 3

--------------------------------------------------------------------------------
Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]
xxx .......................ddd ...............Blank.................. 1
xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)
xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)
yyy ......................ttt............... Blank....................1 (another product,yyy)
yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)

## Count With Conditions & Doesn't Exist In List

Jun 4, 2008

I have a Sumproduct formula to count instances of a particular event (from a list of events) based on multiple criteria.

I am trying to utilize the same method to count instances of all events not defined in the list of events but I would welcome any solution

In the attachment,

Defined list of events A4;A5 (this is just an example, the actual list is approx 100 events)

Data being counted F2:N10 (actual data approx 1000 rows)

My working formula is in cells B4 through D5

My not working attempt to adapt the formula B6

## Count Unique Entries In One Column That Meet Conditions

Jan 17, 2010

I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]

The situation:

I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.

namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm

The challenge:

In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!

The correct result: ...

## Count Number Of Times Conditional Format Conditions Met?

Apr 22, 2014

I have used Formula to ID cells containing a formula to flag cells red with conditional formatting in a list that do not have formula.

I need a single cell to change colour if any of the cells in the list do not have a formula. e.g. the subtotal in the top row could go red so the user can scroll down to find individual red cells.

Is there a way to count the red cells without formula in the list - Conditionally formatted cells are hard to count. I don't want to use a vba script to do it as the user probably won't run it and auto run slow calculations down.

Is there an array formula that can return true or false if not(CellHasFormula) is true anywhere in the list?

countif(AH1:AH2976,CellHasFormula) doesn't work.