I have a list of parts in column A that is about 200 rows or so. There are only a dozen parts or so that repeat multiple times. I then have about 20 columns of data for each part. Column K is "Customer".
What I need to do is count the number of each part in column A. That was done with a simple countif formula. Now I need to count each part in column A only if a cumster name is listed column K (this column is left blank until a customer is added). I can't seem to get this to work. Let me know if you need me to be more specific.
how to set up a formula that would count the number of numbers in a column, only if the cells before those cells are blank. In plain English: total the number of occurences in each column, provided the value in cellrow is the first occurence in that row starting at column A. E.G
A B C D John x x Dave x x Kyle x x Bob x x
Count would return under Column A, 1; under Column B, 1; (because row John has a value in AJohn it is ignored), under Column C, 2; under Column D, 0.
I have a formula (below) where I want it to count the cells that contain a date that is less than or equal to today, but only when the adjacent cell is blank. The latter part of the formula works, but I'm struggling to tell it to only do the count when cells in column I are blank;
I tried to answer a problem on here by giving this formula: = COUNTIF(A1:B6,"=""") but it doesn't work on my machine. Nor does =COUNTIF(A1:B6,"<>"""). In either case, the effect is as if all cells in the range are non-blank. But the result of =A1="" is "TRUE". Neither syntax is rejected by Excel; in fact, if you omit one of the quotes, the syntax is corrected to the form shown. So I am curious. Does this work normally, but some setting in my machine is stopping it? Or, if it never works, why is it not giving a syntax error? I am using Excel 2000.
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
I am dealing with date ranges with the data being on a second tab of the Excel workbook. The first tab is to populate a report of the difference in dates. I need to count the number of values are less than 3, after I subtract Column P from Column K.
Raw Data'!P:P is a Particular date and 'Raw Data'!K:K is another date. I am trying to find a count of all of the rows where the difference of the 2 dates is less than 3.
I was curious if it is possible to give a single cell multiple number formats based on what the number is in the cell. So for example if my number is bigger than 1000, I would like to use comas so that it looks like this 1,000. However, if it is less than 1000 I would like it to look more like this 999.00.
I'm trying to count the number of incidents in column BB that are >0 but only IF the value in column E is "Abbeywood". i.e. how many times there's a figure greater than 0 for Abbeywood. I can't seem to get count if to do this!
How do I setup a COUNTIF function so that it 'searches/uses' more than one column for results.
Example: I have a spreadsheet with 20 columns - starting at A1 to T1 - and ending at A100 toT100. I want to use the COUNTIF function to 'search' all of these from A1/T1 to A100/T100 and tell me how many times the number 21 appears across that range of cells.
This COUNTIF function will reference data on a sheet called 50 - so at the moment my current single colum COUNTIF looks like this -=COUNTIF('50'!A1:A100,21) - and it works fine for one colum, but I need to search 20 columns.
I have a value in A1 and I have a constant formula from Column C to G. what I want to do is if column A has no value then Column C to G should not have values. If column A has values then Column C to G should calculate the values. I have a sample excel attached. I can't figure out what to do! I've tried conditional formating but dosn't seem to work
I have 44 cells each in columns D & E, column D being a class of item, and E being the quantity of that class, and I want cell G42 to show the total quantity of the class. I have used =COUNTIF(D5:D49,"A") and am only showing the number of A's (A being the class) in column D. What formula can I nest in this equation to get my desired results?
Ok so I am trying to streamline the Shirt ordering process at my work. I have made a table with Employee names as Column A, then Row 1 is shirt sizes from small to XXXL. The Cell values are going to be the colors people want. I want to total the number of each size/color combination. But someone may want more than one of the same color. For instance you would enter "BK, BK" if you wanted 2 black shirts of that size. This is where the issue with COUNTIF comes in. If a cell has the same value twice, it only counts it as one because counts the cells with an instance of a value, not the actual count of instances. Is there a way to make COUNTIF or a better function for this?
I am trying to use the following formula to count the number of times a day appears in a column. It doesn't seem to be working the way I want it to. =COUNTIF($A13:$A200,"Sunday"). It could be the way the days of the week are gathered on the sheet, you can see the attached workbook to see how I am using it.
I'm working on has me making a frequency distribution of the yields column. If you look at M6 on the spreadsheet i'm trying to use the formula:
to represent all yields that are less than or equal to 0, in the next box (M7) I want to represent all yields that are greater than 0% and less than or equal to 1%. etc until I reach 7%, however the original formula isn't working and I don't know whats wrong with it.
I want to perform a COUNTIF in a column where data is added regularly. The COUNTIF range is to be the last 100 rows of data: so if yesterday that range was a2:a102 then today it should be a3:a103, tomorrow it will be a4:a104 and so on.
How can I get COUNTIF to change the range as new data is added? I can generate a string with the changing range ("a2:a102", "a3:a103", and so on) but how do I put that into COUNTIF?
We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.
Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.
In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50 Balloons 12567 Blue Princess balloon .86 Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27 Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.
However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.
Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.
This is for a template for teachers to analyze student testing data. On sheet4, wrong answers in each column are noted by a lack of a + in the corresponding cell. I want to paste the names of the students who missed each question into sheet 5.
I've done it by repeating a filter macro, but I manually copied the following separately for the 75 columns in the template.
Problem #1 - there must be a more efficient code, something that automatically loops to the next column
Problem #2 - the template has 75 columns, but many tests have fewer questions. I'm trying to find a way to stop the loop whenever it hits a blank cell in Row 10 on sheet 4. I've done it with an if/then in the last section on the above code, but where I'm at now, i would have to add that to the code section for each column. Which isn't that big a deal, but I figure there must be a better way.
The relevant portions of the workbook are attached here.
In sheet "diary" I have data in certain rows in column A8:C10000 that contains values if a certain condition is met. I need a vba to copy and past only non blank cells in column D8:F10000. I first wrote a formula with index but it takes too long to caculate.
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1: - column B is called Supervisor Name - Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will: - Take all unique values in Supervisor Name column, and paste these Above the current table. - For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2: Supervisor Name October November December