Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

Sponsored Links:

Related Forum Messages:
CountIf Blank With Two Columns
counting cells if blank. I have two columns E and J . For example if E5 and J5 are both blank I want that to count as 1.

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

View Replies!   View Related
Highlight Blank Cells Red In Column G If Not Blank In Column C (same Row)
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.

I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.

Note: Any row headers will always be in row 1 only


View Replies!   View Related
COUNTIF: 2 Criteria: Date Range Column & Text Column
Col C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date
range in Col F.

View Replies!   View Related
First Blank Cell In Column & Offset To 1st Blank In Row
I have checked archives, some similiar but not quite what I want.

In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.

View Replies!   View Related
Count Blank & Zero Cells Where Corresponding Column Not Blank
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.

View Replies!   View Related
If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View Replies!   View Related
If Column A Is Blank Then Column C To G Must Be Blank Even With Formula
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

View Replies!   View Related
Countif Column Has Value A
I want to use the countif function on the below. I want to countif col A has ‘A’ and col b has ‘liv’


View Replies!   View Related
Countif Into Column
I have a column (o) that contains the letters ar and i need to count them into column (o 28 ) i am aware that i can use the count if formula.


View Replies!   View Related
Countif Formula: Count The Number Of Incidents In Column BB That Are >0 But Only IF The Value In Column E Is "Abbeywood"
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!

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

View Replies!   View Related
Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?
I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.

If there is no value in column B, then I want that row in Column C to just stay blank.

View Replies!   View Related
Sort Column To Match Pair In Other Column, Skip Blank Spaces
Here's what I have.. (on a much smaller scale)

(notice how the lines that match are now even.)

[Edited by admin~ *Link* to large images, don't display them]

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

=COUNTIF($I$6:$I$35, "<=L6")

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.

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

View Replies!   View Related
Using Countif To Calculate The Number Of Days In A Column
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.

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

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

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

View Replies!   View Related
CountIf: Return Column C In The INDI LEG VALUES Table
I know would like to return column C in the INDI LEG VALUES table if the following occurs

View Replies!   View Related
COUNTIF Formula (to Count How Many Item In A Column That Meet Certain Criteria)
I am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...


View Replies!   View Related
Lookup (display The Value In Column B If Column C Is Not Blank)
What i am looking to do is have column D display the value in column B...if column C is not blank. Here is an example.

View Replies!   View Related
Count The Number Of 'YES' In Column B ONLY When Column A Is Blank
I have 2 columns - Column A and Column B. I want to count the number of
'YES' in column B ONLY when Column A is blank.............

View Replies!   View Related
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*****
Next ii

View Replies!   View Related
If Column B Is Blank Copy From Column A
I need part of my macro to copy from the coresponding row in column A if the cell in column B is blank.

View Replies!   View Related
Countif: Value Of One Returned To A Cell In A Row If One Cell In That Row Contains A Value Between 10 And 5 (less Than 11, More Than 4) And Another Cell Is Blank
I have a spreadsheet with details of letters that should be responded to within 20 days, and i want to have a cell showing the amount that that need a response within 10 - 5 days. Basically I want to have a value of one returned to a cell in a row if one cell in that row contains a value between 10 and 5 (less than 11, more than 4) and another cell is blank. For example, AD27 contains 6, k27 is blank. I want AE27 to show 1. I also need the same if the value is less than 5, but i'm sure i could figure out how to alter it.

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

View Replies!   View Related
Find Next Blank Column
I am trying to create a macro that will find the next blank column in a specific worksheet, not just the active worksheet.

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

View Replies!   View Related
Add Blank Cell Automatically In A Column
For example, In excel in cells A2 to A56 I've got words in them, I wanted to copy all the cells in column A to B in same order,but when the word "check" is encountered, a blank cell will be added below the cell with the word "check". How can I do this?

View Replies!   View Related
Located The 1st Blank Cell In A Column?
I have a macro that copies data from 1 sheet to another one.
However, in the destination sheet I have no way of know which is the 1st empty row that my data can be paste into.

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

Sub DeleteBlankARows()
With Application
.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
Next r
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

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.

View Replies!   View Related
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?
' DeleteBlankRows

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
End If
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
End If
Next r


View Replies!   View Related
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)?

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

Sheets("Latest Results").Select
Sheets("Archived Results").Select
Range("A500").End(x1up).Offset(2, 0).Select

View Replies!   View Related
Find The First Blank Cell In A Column
Is there a way to find the first blank cell in a column using a formula?

View Replies!   View Related
Shortcut Key For Sum When Column Contains Blank Cells
I know that "Alt + =" (Alt + Equal Sign) is a shortcut key combination that will sum a row or column of numbers. The problem is that it stops when it encounters a blank or empty cell and won't sum the entire row/column. I know I can create a macro to get around this, but is there a standard shortcut key or option setting that will quickly sum the entire row/column even when it contains empty cells?

View Replies!   View Related
VBA Code - To Delete The Blank Column
Example :

I want to Delete the Blank Column.

Click the image to view large

View Replies!   View Related
Unlock Columns If Column Is Not Blank In Any Row
Unlock columns if Column is not blank in any row

Using VBA
On Sheet("Draft Final") , search every row and

If Col A is not blank, then unlock Columns G: M and unlock Columns O:U

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

This Month:
Columns A-Q all have data in it. So, I would need to select cell "R1"

Next Month:
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?

View Replies!   View Related
Code To Go To The First Blank Cell In Column A
Here's what I tried using macro recorder...

View Replies!   View Related
Count Blank Cells In A Column?
Can i get a simple formula to count a number of blank cell in a column ?.

View Replies!   View Related
Count Of The Blank Cells In Column
I am creating a chart and want to get a count of only the blank cells in Column D only if there is information contained in Column A (same row). I am using Excel 2003.

View Replies!   View Related
Select All The Blank Cells In A Column
i need a macro that will select all the blank cells in a column.

View Replies!   View Related
Move To First Non-blank Cell In Column
I will be moving to a specific cell - "N1". I'm looking for code that will then scan down from that cell, making the first blank cell the active one.

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

View Replies!   View Related
Populate Blank Cells In Column
let's say you have array like this:
6|value 3

and you want to populate (copy, move values in) the empty cells
6|value 3

this code will do as long the last cell in the array is not ""

Sub PopB()

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
End Sub

View Replies!   View Related
Delete Row If Non-blank Cell In Column
Yes thats exactly what I am looking for. deleting rows if a cell in a particular column is not empty/has data.

View Replies!   View Related
Locate 1st Blank Or Last Used Cell In Column
Here is the sheet where I entry new data. From left to right: Productnumber/article/supplier/price per item/starting nr of articles/order threshold/ date 1st order/date of delivery. When I press the button "verwerk" the data will be transported to the sheet "Voorraad verloop" shown underneath. As you can see the first problem consists of the placement of the first table. I can't get it to the first column. The second problem is that when I enter a new product like in the first image, it overwrites the current data in the sheet "voorraadverloop". See the image below. I used the following macro:

Private Sub CommandButtonVerwerknieuw_Click()
Dim iLC As Integer
Dim FindString As String
Dim rng As Range
Dim Lr As Long
If Application.WorksheetFunction. CountA(Range("A4:F4")) < 6 Then
MsgBox ("Niet volledig ingevuld"): Exit Sub
End If
FindString = Range("A4")
If Trim(FindString) <> "" Then
With Sheets("Voorraadscherm").Range("A29:A65536")
Set rng = . Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _ .................

View Replies!   View Related
Delete Rows If Corresponding Column Is Blank
I am trying to delete rows that do not have values in column B. This is a quote form that takes up over 1000 rows but not all are needed (ie lines that do not have any value in B "qty"). Is there a way do have excel delete these without doing it manually?

View Replies!   View Related
Copyright © 2005-08, All rights reserved