# Counting Even If Doesn't Meet Criteria

Jun 23, 2009
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.

Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.

Here is the formula/function I'm using:

=(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")

View 6 Replies
ADVERTISEMENT
Feb 12, 2014

STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:

When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.

UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.

View 2 Replies
View Related
Apr 6, 2013

I need to count the number of occurances that meet two criteria i.e.,

Status needs to be UNSOLD

Size needs to be =>10.5 and also <12.5

Dress

Status

Size

1

UNSOLD

9

2

10.5

3

12.5

4

UNSOLD

12

5

15

View 4 Replies
View Related
May 17, 2014

BP Formula.xlsxI'm not sure what formula to use or if Excel can do this. I want to use it to determine number of blood pressures in a certain range. So if I use column A for SBP (systolic BP) and column B for DBP (diastolic BP). I want a formula to tell me if column A is between 140-159 or column B is between 90-99. I can get that part, but what I'm having trouble with is it counting each row twice if both column A and B meet the criteria, whereas I want every row (person) to only be counted once if either column A OR column B meet the criteria. (See attachment)

View 5 Replies
View Related
Oct 7, 2009

Every other line is the planned production and the others are filled in after as the actual production

I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.

Here is my problem- if a station is running two different products it gets counted twice.

I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines.

Attached is an example of what I have with the formula and below that is what I am looking for

View 2 Replies
View Related
Mar 8, 2012

I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"

I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.

The following formula was able to give me the count of cells "N/A"

=COUNTIF('Master Data'!$UA59:$ALZ59, ">0")

and this gave me a count by Month/Year

=SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))

When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.

View 7 Replies
View Related
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

View 1 Replies
View Related
Aug 16, 2008

I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB-"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB-12345, BP-12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB-" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand.

So far I am using the below code to get a TOTAL count of products that start with "AB-", but i cant figure out how to write it so that it checks additional criteria in another column.

=SUMPRODUCT(--(ISNUMBER(SEARCH(A3,(OFFSET('photo list'!G2:G5001,,,,))))))

A3 contains the text "AB-" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB-". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.

View 9 Replies
View Related
Dec 6, 2013

I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.

I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.

I then want to count the number of rows (looking at column 1-3) with the following criteria:

Countif column2<= "date in colum4" AND column3< "date in column4"

View 6 Replies
View Related
Jul 31, 2013

I cannot figure out why the Count function counts blank cells.. Data adjacent to the blank cells were pasted from Access datasheet.

View 8 Replies
View Related
Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _

"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

View 1 Replies
View Related
Mar 25, 2009

I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.

I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.

View 9 Replies
View Related
Oct 6, 2006

I have built the following code which should look through a data sheet and then work out the instances where it meets the following criterias:

= "Client A"

= "First letter of surname is "a"

The code is as follows:

Public rowcn

Sub compare()

Dim rngTemp As Range

Dim intCounter As Integer

loop_col = "Client A"

data_sheet = "Data"

target_sheet = "Summary"

rowcn = 2

Do.............................

View 2 Replies
View Related
Mar 13, 2014

How can u use sumifs to meet 2 critera and then sum the one column..

=SUMIFS('[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$M$8:$M$100000,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C$8:$C$100000,B5,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$B8:$B100000,$A$2)

That is the formula i used and it worked, yet when i try to use it for other names (where b5 would be) the numbers skew and do whatever they want..

I just want to sum all of the values in M:M which have B5 or "fiona" in columns C:C and also match the date from B:B with A2.

I believe ive done it right and as i say it does work with the first name in b5 but when i pull the formula down it doesnt work anymore...

View 5 Replies
View Related
Apr 7, 2014

Aim - Extract every word after: and before , and put them in the appropriate headings

I have attached my desired results in column B - E. To calculate the answer, the heading of the tile is normally just before the : Meaning the Answer is after the : and before the ,

View 2 Replies
View Related
Nov 22, 2009

On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?

View 3 Replies
View Related
Jul 2, 2014

I have a spreadsheet that has employee name and average hours worked.. The employee is listed multiple times because each row represents a pay period with number of hours worked. the last column I used to average the hours worked. So what I need is whenever there is a value greater than 30 in column g, I need to copy the row where column G has a value. It can be copied on the same sheet or on another.

View 9 Replies
View Related
Apr 11, 2008

I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?

View 13 Replies
View Related
May 28, 2009

I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.

View 4 Replies
View Related
Apr 5, 2012

I want to make a function that gets the range like. "A1:B9" And this function automatically gets the range when given criteria meet.

For example: I have a cell A1 with value 0 and next 0 value in cell A10. I want to put that function in C1. So this function returns me the range like " A1:B9"

View 3 Replies
View Related
Jun 18, 2013

I need a formula that will tell me if EITHER two cells = a text word. I've tried a few things and can't seem to get it to work!!!!

See in the example of my spreadsheet below: If A2 = FALSE or B2 = FALSE then D2 should display "Allowed" if either are TRUE D2 should display "Not Allowed"

Is this possible!?!?! I've tried way to many different formulas and am close to giving up..

A

B

C

D

1

Not Slow

50%

FY

Change

[Code] ........

View 6 Replies
View Related
May 30, 2014

Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.

View 1 Replies
View Related
Apr 6, 2007

I want this macro todo is to start at the top of columns A to J

and work down the column and colour any cells that meet the criteria.

Range(“A1”).Select

Do Until ActiveCell = ""

If ActiveCell > 0 Then

Selection.ActiveCell.Interior.ColorIndex = 5

Else

ActiveCell.Offset(1, 0).Select

End If

Loop

End Sub

View 9 Replies
View Related
Oct 27, 2009

I need to extract key cell info from a master list when one of the cells meets a certain criteria.

Example:

Each row of the main sheet represents a Purchase Order with columns A-M each containing text, numeric, or currency values regarding that PO. Column F represents a 4-digit activity code that defines exactly what was purchased. If the activity code for a row is in the 1000 series (1000 -1099) I need to assign info from 3 other columns (say B, E, &G) to a subsequent sheet for tracking.

My limited Excel-o-vision has built this: (sheet 1 represented as FY10)

=IF(AND('FY10!F4>=1000,'FY10'!F4

View 9 Replies
View Related
Jul 3, 2009

I want to sum all cells in column A that have one of several tags in an adjacent cell in column B.

- Some of the rows in column A are not tagged

- There are currently 3 tags that are valid in column B (I, S, R), but this could increase (or reduce) in the future, say to I, S, R, E

- The possible tags are contained in a named range, but don't have to be

- It's not possible to restructure the data because the columns are in a scratch sheet, where a variety of calculations are created on the fly in the column. I've attached an example, which might make it clear

- There are several (up to 10) sheets like this in the workbook with lots of different calculations that are then summarised in to some analysis.

- The overall objective of the workbook is to be able to;

a) include/ exclude individual calcs for any one column

b) include/exclude a country - solved

View 3 Replies
View Related
Jan 4, 2008

In my spreadsheet I have several columns and I have written a formula that has two conditions. If these are met the result is counted. However I want to add another condition. I need to write a formula that displays information relating to the conditions in a cell if the other two conditions are met. For Example, if the formula picks out that a row has the word 'John' and has the a number between 1 and 14 it will copy the inputted data at the beginning of the row.

View 6 Replies
View Related
May 14, 2008

I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc).

Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.

View 4 Replies
View Related
Jun 13, 2013

I attached a spread. On the data tab, I have 2 criteria that I would like to use to move rows to another sheet. Columns U and W. An example would be that all rows that have a coil in Bay "B" and are allocated would go on the "B Allocated" sheet. I would also like this to automatically update when I delete the data in the data sheet and put new data in.

View 4 Replies
View Related
Dec 22, 2013

I would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.

Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.

Capture.PNG

View 2 Replies
View Related
Feb 14, 2014

I have a table that I would like to search to return all the values that meet 2 criteria entered by the user.

I have 3 columns - Role Name, Skill, and Skill Level - I'd like to be able to enter the skill and skill level and return all the roles that meet the two entered criteria.

At the moment I have an array, but it only returns the first value from the Role Name column that it finds.

=INDEX(A2:A100,MATCH(1,IF(C2:C100=G4,IF(E2:E100=H4,1))),0)

Where the A column is the role name, C is the Skill, and E is the Skill level. In cell G4 the user enters the skill to be search, and in H4 the level required for the skill (a scale of 1-4)

Is it possible for the formula to return all the values in column A that meet the criteria entered in G4 and H4?

View 7 Replies
View Related