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)
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.
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.
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
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
I have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).
In this case the result I'm looking for is on row 2:
Is this possible to calculate in 1 cell only (h1)?
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
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.
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.
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......
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
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.
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.............................
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 ,
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?
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.
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?
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.
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"
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..
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.
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