Formula For Counting Occurrences That Meet Multiple Criteria?
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.
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'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 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 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.
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I have a sheet with data which is all sorted by columns.
What I want to be able to count the occurrence of an instance when 2 cells in the same row (but in different columns) match my given criteria.
Ive been using 'countif' to count the instances in a single column and I am now aware that this is about all it can be used for.
An example of my data is:
A B 1 a 1 a 1 c 2 d 2 c 3 b 3 b 3 c 3 d
What I want to be able to count for example is how many times '3' & 'b' occur on the same row (in this case the answer would be 2).
Im not very good with pivot tables and have tried but have not managed to find an answer. I really hope that someone can help as Ive been working on this probelm for days and quite frankly its doing my head in :-)
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'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted. The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
Iam doing a market report for real estate. I have attached a file for reference. I am trying to track the Active Listings within a given time period and within a specific zip code. Unfortunately my local MLS does not track historical data on Active Listings, and therefore I have to use the Listing Date and the date it no longer went active to determine which listings were active in a given time period. Some of the listings are still active and therefore do not have a "No Longer Active" date. These listings will need to be counted as well.
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 would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
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 am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background: -- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook -- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing. In the entire workbook, I want to add 1 (counting function) only when: R1=2 AND E1>F1 or S1=2 AND G1>H1 or T1=2 AND I1>J1 U1=2 and K1>L1 on each appropriate sheet in the workbook.
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
I have common tasks where I need to sort through large amounts of data to receive a total number of line entries matching multiple criteria in different columns. I've attached a Workbook with a simplified example. On STATS!B1 I would like to total, from DATA!, all lines which have "1A" in A:A and any of the following in E:E (RP, SAO, AE, RSNR).
