# Counting Rows Based On Different Criteria

Oct 28, 2008

I've got two worksheets. One contains data from a query, the other worksheet should count rows in the queryresults based on two criteria. See example.

I tried doing it with sumproduct in the normal variant and the array-variant...

## Counting Number Of Rows With Multiple Criteria

Dec 8, 2006

I have a sheet with "Yes", "No', and "N/A' in most of the Columns. (E - Q) and about 100 rows of data.

I only want to count the number of rows that have "No" in column E and Yes in all the others. If "no" occurs in any of the other columns in that row (D-Q) I dont want it to be part of the count.

## Counting Data Based On Two Criteria?

Aug 29, 2013

I have data in my worksheet as follows

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;

[Code]....

I want to create a sum of all values in column B (Data Size) that correspond to the same Dept-div code in column A

Ex: 0667 the total should equal 43268.8 for the two cells.

I need to count all cells in these columns versus a specific range?

## Formula For Counting Based On 3 Criteria

Aug 15, 2007

Need to writing a formula that will calculate the number of times a contract is used each month. Problem is that RMA numbers sometimes repeat. So I need to count the contract number for every unique RMA in a month......

## Counting Data Based On Criteria

Aug 11, 2008

I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored

## Counting A Column Based On Two Sets Of Criteria

Jul 23, 2009

I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.

## Counting Unique Values Based On 2 Criteria

Aug 11, 2007

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

## Counting Instances Of Text Based On 2 Criteria

Aug 17, 2006

Need a way to count the number of times a certain bit of text occurs in a range based on a given criteria? Say I've got a column of names and another column with true or false in it (next to each name), how could a do a total of how many times 'TRUE' occurs next to each name?

## Counting Number Of Rows Where Datediff Of Two Columns Meet Criteria

Aug 21, 2013

I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?

Name
Start
End
Other data
Verizon
1/10/2010
1/25/2010
AT&T
3/2/2010
4/14/2010

## Counting Unique / Different Values Based On Multiple Criteria?

Jan 21, 2014

I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).

Program
Clinic Title
Start Date

[Code].....

formula that will automatically give me that answer?

## Counting Text Fields Based On Multi Criteria

May 12, 2006

Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.

Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).

## Counting Macro Based On 2 Criteria (Horse-racing Spreadsheet)

Jun 21, 2006

Horses have 5 running styles; early speed to late closers; denoted as 1 to 5 in column Z. I'd like to count those noted as 1 thru 3 for each race.

In column FC (the last column with data) I have a race ID # for each race on the spreadsheet, somewhere between 2500 to 5000 races in each one. They're a monthly record. The race ID # is a concatenation of columns B (Track ID), C ( Date) & D (race #). Each is unique to its race. E.G. Aqueduct, jan 1st, 2004, 2nd race is id'd as AQU379872.

Would it be possible to create a macro that would (A) count the number of horse 1s, horse 2s & horse 3s from a specific race ID & then post those numbers in columns FD thru FF & (B) then continue to the next race ID in column FC & count those horses from that race & so on thru the entire spreadsheet?

Would it be simpler if each race ID were changed to a number--race 1 down to race 2500?

## Excel 2010 :: Counting Bank Cells Based On Changing Criteria?

Oct 30, 2013

I am using Excel 2010.

BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500

MainList - F2= (current month)

Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.

This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))

## Counting Rows Based On 2 Columns (Distinct)

Mar 19, 2007

From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:

Base Table:

ITEM-----Loc-----QTY
Pen------School----5
Pencil----Office-----9
Binder---Office-----9
Pen------Office-----6
Pen------School---15
Binder---School---12
Pencil----Office----10
Binder---School----6
Pen------Office----11

I need to have a table resulting to the following:

Item-----Loc------Qty
Pen------School----20
Pencil----Office-----19
Binder---Office------9
Pen------Office-----17
Binder---School----18

## Counting Rows Based On Date And Status Column

Apr 2, 2007

I wish to count the number of entries in a workbook based on the entries in two columns. The columns are for example H1:H100 ( date) and L1:L100 (status). I want to the number of times the date in H1:H100 is over 30 days old, as well as L1:L100 = 'Started' for example.

## Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub

## Cut Rows Based On Criteria?

Jan 19, 2014

There are two workseets involved. Inventory is the main and Pending is the second. There are 28 columns of data in each that match based on row 1 headers. I need to figure out how to cut all the rows with "Pend" listed in Inventory column Z to one row below the data in column A of the Pending worksheet. The data can be dates and also notes but no matter what is in column Z it must be cut and moved.

## Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

## Delete Rows Based On Two Criteria Within Each Row

Sep 24, 2009

I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool.

The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error).

The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...).

What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as:
"T" "["
"T" "#"
"T" "@"
"T" "]"

The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ).

I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).

## Hide Rows Based On A Criteria

Oct 6, 2009

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

## Delete Rows Based On Criteria?

Jan 23, 2010

I had a raw data sheet in which i need to prepare a statement just like the attached worksheet.

My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN".

Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN)

I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular.

The department column might be in any column.

## Hiding Rows Based On Criteria

Dec 22, 2011

I am stuck with a task.

I need to hide all rows in a worksheet except the rows which contain the word which the user inputs through find (CTR+F).

The input word should be captured in a variable and this should be searched in all rows & hide all other rows in the sheet which does not contain this word.

## Deleting Rows Based On Criteria

May 29, 2012

I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".

The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.

So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.

I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.

This is the code I wrote:

Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String

Application.ScreenUpdating = False

[Code] ........

So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.

## Deleting Rows Based On Two Criteria?

Apr 20, 2013

I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows

I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.

Code:

Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long

[Code].....

## Delete Rows Based On 2 Criteria

Nov 30, 2006

Is there a vba code that can delete the entire row based on two criterias? If a row has a location of Canada and is a female, then delete the entire row.

Name Location Gender
Alan Mexico Male
Dick USA Male
Sharon Canada Female <-- This will be deleted if based on criteria

## Delete Rows Based On Criteria ..

Jul 19, 2007

I am a pharmacist that runs reports and I need to sort and exclude data

It is in Excel 2003 format

I run a report that includes a patients name in one column, the medications name in another column and if the medication was withdrawn, wasted, returned or restocked in another column. Withdrawn, wasted, returned or restocked are treated as different functions and each have their own row entry. Patient and medication name are the same in all situations

I need a macro that will look at the patients name and medication name, see if it is the same and delete all rows that have a withdrawal with an associated wasted, returned or restocked.

## Move Rows Based On A Criteria

Jul 21, 2007

Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"

I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B

## Extract Rows From One Sheet Into Another Based On Certain Criteria?

Dec 21, 2013

How can I extract rows from one sheet into another, based on certain criteria?

## Copy Rows From One Workbook To Another Based On Criteria

Aug 4, 2009

I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.

This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.

## Extracting Certain Rows Based On Multiple Criteria?

Oct 29, 2013

There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.

Example.png

Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).