Count Data In Column X Based On Conditions In Column Y

May 6, 2009

I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".

In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.

Sum Cells In Column Based On Conditions In Other Column

Dec 6, 2013

I am trying to sum up cells from a column based on conditions in another column (see attached file). I know this could be done using a formula but for various reasons I want to use code. I am not sure if what I am doing is the right approach.

Count Unique Entries In One Column That Meet Conditions

Jan 17, 2010

I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]

The situation:

I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.

namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm

The challenge:

In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!

The correct result: ...

Count Of Cells In Column Based On Criteria On That Line In Different Column?

Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

Count Filled Cells In One Column Based On Date In Another Column

Apr 24, 2009

I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:

AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May

If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.

Perform Count On Column B Based On Values In Column A

May 15, 2014

I have a column that repeats it self and I want to perform a count on Column B, based on the values in Column A.

Example attached : Book2.xlsx‎

Count Column Based On Certain Text In One Column And Blank In Another

Feb 4, 2014

I need to count column E when it does not say "MTO" or if column G is blank. E.g. below would return me a value of 2. I would count row 1 and 3 only.

Column E Column G
10-Jan-14
MTO
06-Dec-13
18-Oct-1321-Oct-13

Count Unique Values Based On Duplicate Status In Another Column On Filtered Data

Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:
Column C Column D
111111 M
111111 M
111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

Count The Names In A Column Based On Another Column

Nov 30, 2009

I'm using this formula to count the number of times this statement is in Column "D"

How To Count A Column If It Matches Data From Another Column In Seperate Rows

Feb 6, 2009

I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.

Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.

Count How Many Cells Have Data In One Column If Within Date Range In Another Column?

Jan 22, 2014

I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.

So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).

A
B
C
D
E
01/01/2014

[Code].....

Trying To Use SUMIFS To Sum A Column Based On Two Conditions

Jan 11, 2010

I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.

Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)

Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM

I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.

=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")

Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.

Vba To Delete Rows Based On Certain Conditions In Column A

Mar 2, 2006

I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).

Calculating The Average Of A Column Based On Multiple Conditions

Feb 21, 2008

How do I calculate the average based on multiple conditions? Is my syntax incorrect?

Here's what i'm looking for:

=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

Creating Validation List From Table Column Based On Data In Another Column

Dec 11, 2013

If I have a table as noted below with the following assumptions:

- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.

A
B
C
1
Item
Calories
Include

[Code]...

How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?

I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx

Identifying Data In One Column Based On Highest Figure Data In Another Column?

Jun 19, 2014

I am trying to write a formula that shows the best grade for each each student and the subject in which

In a previous post i was shown how to identify the highest grade

MAX(IF(A\$3:A\$34=A7,C\$3:C\$34))

I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E

However, I also want to identify the subject in which they scored their highest grade in column F

I have two problems

1. I don't know how to write a formula that brings in the subject based on the grade for each student

2. I don't know how to write a formula in case there is a tie (see DAVID L)

Transfer & Update Data From Column With Conditions

Jul 25, 2007

I have a lot of data broken up into numeric decimal form in one column. I need all this data to be transferred to different worksheets based on the amount of decimals. Attached is some of the data I need sorted. The first sheet is my main sheet, then the next sheets need to be ascending in numeric order by sorting the data with two decimals under the column Tear number system.

For example I need the next tab to be the data with 0.21 with 2 decimal places, then the next tab would be the data under 0.28 with 2 decimal places, then the data under 0.29 with 2 decimal places. So the 0.21 worksheet would be 0.21 and have all the data for 0.21.1, 0.21.2 all the way through 0.21.6.

I also need this to be compatible with new inserts to the main table. Meaning if a new number is entered in, the number and data would automatically be entered and sorted to the tab that the number pertains to. I have tried a vlookup function but have not had much success. I have also tried recording a macro but am having problems with that as well.

Move Data From One Column To Another Based On Column Headers And Row Text

Feb 7, 2014

I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)

I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.

The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.

It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".

If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".

Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.

If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.

The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.

Populate Column Based On Change In Data Of Another Column

May 30, 2014

I am trying to find a way to populate a column based on the changes in the data of another column...

For example, I want to fill in the code column....so when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to \$37.51, want the code to change to 22.

CodeCustomer's Cost
\$34.10
\$34.10
\$34.10
\$34.10
\$34.10
\$37.51
\$37.51
\$32.50
\$32.50
\$33.48
\$33.48
\$34.10
\$37.51
\$37.51

Clear Contents Of Column Based On Data In Another Column?

Apr 10, 2014

I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.

Pull The Data From The First Column Based On The Ranking Of The Second Column

Sep 15, 2005

Item Quantity
A 2
S 7
D 3
F 6

I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc

The list I will pull this from is variable in length but in the
hundreds.

Get Column Alphabet Based On Data It Contains Or Column Heading Using VBA?

Oct 28, 2011

How to get the Column Alphabet based on the Data it contains or the Column Heading using VBA?

Is it possible to get the Column Alphabet using any Macro or any function that within a Module it can always take the New Column Name during Execution..

Example: I have certain Columns where I have Yes and No Tick using the Wingdings P and Y..
Now these Columns are alternately Placed and there are six columns in all and they are spread over 12 Columns as the Alternate COlumns is reserved for Manual Entries.

Now I intend to increase the Manual Entry Columns but the problem is that every time I do that I need to make changes in my VBA Code.

Is it possible that even when the Columns are Inserted or Deleted in between before or after these columns I dont need to change the Explicit references by changing some approach.

I am not fuly conversant in VBA but use it whenever things are not completely feasible with Formulas AFTER GOOGLING.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("X3:X13")) Is Nothing Then ' You can Change the range here
Cancel = True

[Code]....

As you can see the Ranges are Alternately Placed and there are about 6 such Columns with the above TICKS..

Now, how do I keep it FLoating so that even after changing the COlumn Location it does not need to get Updated i.e. X and Z Column Alphabets..

Summing Cells In Column Based On Data In Another Column?

Feb 27, 2012

I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.

A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 \$5
111 widget 2 \$10
111 widget 3 \$8 XXXXX
222 widget 1 \$5
222 widget 5 \$15 XXXXX
333 widget 2 \$10 XXXXX
444 widget 5 \$15 XXXXX

I had thought an IF formula would be the way to go.

Move Info In One Column Based On Data In Another Column

Aug 28, 2012

I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.

In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.

area (P)
phone (Q)
primary phone (R)
col S
col T
col U

[code]....

Count Values Of Column If Column Header Is Equal To Name In Column A?

Apr 25, 2014

For column "B" count all the 1's if column header is equal to name in column A.

For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.

VBA To Count Data In Column And Paste The Count In Different Sheet?

Jul 1, 2014

VBA which would count data in Column F of dump Sheet and paste the count in master sheet B2 Cell.

Sum Data In One Column Based On Same Date Column?

Sep 27, 2012

I am trying to write a formula in a cell that sum the Total/Day and the Total/month and graph it based on Date

example:

-----Date-------- Sale------Total/day-----Total/month

1---10/10/2012 ---\$5
2---10/10/2012 ---\$7 ------\$12
3---10/13/2012 ---\$2 -------\$2
4---10/17/2012 ---\$8 -------\$8 -----------\$22

1---11/3/2012 ---3
2---11/3/2012 ---5 ----------8
3---11/7/2012 ---6 ----------6 ------------14

1---12/15/2012 ---2 ---------2
2---12/25/2012 ---4
3---12/25/2012 ---2 ---------6 ------------ 8

Count Every 4th Column Based On Criteria?

Oct 31, 2013

I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:

untitled.jpg

I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.

Non-VBA preferred

Count From One Column Based Off Of Criteria From Another

Dec 6, 2013

Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.

test book 1.xlsx‎