Countif Only If Another Column Is Not Blank
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.
View Complete Thread with Replies
Related Forum Messages:
COUNTIF Function Does Not Work With Blank Or Null Cell
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.
Countif With Multiple Column
looking to use =countif for an address file and want to filter out duplicates of last then first name.
col F has last name
col d with first name
I've tried using the Conditional Formatting funtion, but am not understanding how to incorporate multiple columns,
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
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
Find The Column Name Purple & Delete The Whole Column If There Are No Blank Cells
I'd like a macro that does 3 things..
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.
CountIf: Frequency Distribution Of The Yields Column
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.
COUNTIF In A Column Where Data Is Added Regularly
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?
Copy Non Blank Cells In Column Up 1 Row To Another Column
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish.
Dim ii As Long
For ii = lastrow To 6 Step -1
If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy*****
Defining The Next Non Blank Column
I have a form in excel that people are going to populate, then a button which runs a macro which copies all the cells out into a new line in a different worksheet inside the spreadsheet. im going to use that sheet to formulate graphs etc, the macro works however it overwrites previous data, im assuming i need some function to calculate which is the next empty cell then use this to to copy the data to
Find First Non-blank Column
I have an Excel worksheet with two rows. Row one has months, row two has unit counts. I simply want a formula that will find the first (leftmost) date where a unit count occurs, returning the month of the first unit data point. I've seen several forum questions on how to search/ lookup the right-most non-blank/non-zero data point, however I would like the first, leftmost point. I have been unable to modify the forumula.
Filling Blank Cells In A Column
I have 6,000 rows of check detail. The check number is listed in both columns B & C.
If the check paid multiple invoices the check number is not repeated in columns B & C.
I would like to fill the cells in those columns with the appropriate check number. Data looks something like this-
item ck_01 ck_02 vendor ck_date
1 10100 10100 ABC 09-01-09
2 10101 10101 ALLIED 09-01-09
3 ALLIED 09-01-09
4 ALLIED 09-01-09
5 10102 10102 BEACH 09-01-09
6 BEACH 09-01-09
7 10103 10103 CAP 09-01-09
8 10104 10104 DART 09-01-09
How can I fill the balnks with the check number (above)?
Select First Cell In First Blank Column
I want to select the first blank cell in the first blank column. I would just look at the column and instruct it to select a given range, but each month the blank column will change (by moving one to the right).
So for example
Columns A-Q all have data in it. So, I would need to select cell "R1"
Columns A-R will all have data in it. So, I would then need to select cell "S1" Need code to auto-detect the blank column and then select that column's first cell?
Fill Blank Cells Within Column
If A1 has a value of ABC,
A2-A5 are blank
A6 has a value of DEF
A7-A10 are blank.
Is there a formula that will fill A2-A5 with ABC,
look at the value of A6 and see that it is different from A1-A5,
or not blank,
change the fill value to the new value, A6,
then fill down the value of A6 into A7-A10
instead of manually using the fill handle?
Populate Blank Cells In Column
let's say you have array like this:
and you want to populate (copy, move values in) the empty cells
this code will do as long the last cell in the array is not ""
For LoopCount = 1 To ActiveSheet.Range("a65536").End(xlUp).Row
If Mid(Range("a" & LoopCount), 1, 1) <> "" Then _
TheQuestion = Range("a" & LoopCount)
Range("a" & LoopCount) = TheQuestion
If Cell Is Blank, Stop Going Down The Column
the following macro works great if there is data below cell O4. but is there isn't any (e.g., at the beginning), then the cursor runs down to 65,536. ?if no data exists, how can i stop at cell O5...
Macro To Find Next Blank Cell In A Particular Column
I am trying to write a macro that will do a bunch of stuff then go to the next blank cell in a particular column.
The rest of the code for the macro is irrelevant I just don't know how to code it to find the next blank cell in the column. It could be anywhere from cell A2 to A1000000. Basically I want the macro to select the cell that is next on the list to enter data into.
Delete Row If Blank Cell In Column
way to find blank cells in a column(s) and delete the rows corresponding to the cell number. I found this code in one of the previous threads on ozgrid but it just doesnt work for me because it is slowing me down. Please help (I am working on around 60000 rows in Excel 2007)
.Calculation = xlCalculationManual
. ScreenUpdating = False
Dim r As Long
For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1
If Cells(r, 11) = "" Then Rows(r).Delete
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
a way to do the reverse viz. for a particular column, I'd like to delete the row corresponding to the non-blank cell in the column.
If Column B Is Blank Delete Entire Row
I am using the following macro to delete "completely empty" rows. I also need to delete some rows if a cell in column B has no value. How would I change this macro?
Dim r As Long
Dim C As Range
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Set Rng = ActiveSheet.UsedRange.Rows
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
Delete Rows :: Where The Corresponding Entry In Column Is Blank
I have data input in a particular column, say Column C.
I would like to have a macro that deletes the rows where the corresponding entry in Column C are blank.
In addition, there are multiple worksheets with the same data format in the same spreadsheet, but the number of row varies. It would be great if the macro can delete the row with blank cell in that column across all worksheet.
Insert A Blank Column Next To Heading Columns
I am doing a lookup, populating a column (which works although slowly) then (in this part) doing a search on the header row.. If the heading contains the word "Category", I wish to insert a blank column next to (the column containing the heading), colour it yellow then look for the next heading and repeat if found.
It appears to clear my first row so I must have my columns referred incorrectly as rows
Dim FWord As String
Dim i As Integer
Dim lCol As Long
Dim MyString As String
FWord = "Category"
lCol = Range("A1").End(xlToRight).Column
For i = 1 To lCol
Cells(1, i).Value = MyString '
VBA: Paste Into Last Blank Cell Of Column
I want to cut a range from one worksheet, and then paste it at the end of a column in another worksheet.
I have tried this