# Counting Amount Of Columns With Different Content Under Number Of Conditions?

May 16, 2014
I've been desperately trying to figure out a formula that allows me to count the number of rows in a column that have different values, but that only counts those rows in which other conditions have been met. Probably sounds a little confusing, which is why I'll illustrate it with a screenshot:

The file contains a listing of major executives of a variety of different firms in different years from 1992 onwards. In the screenshot for example, you see the executives of AAR Corp from 1992 to 1997. Since my file however consists of 240,000 rows, there's a number of c. 2400 firms with their executives over a time period of 20 years each. Since not necessarily all executives remain with their companies for the rest of their lives, some executives show up for a number of different firms in my sample.

I want to determine at how many firms each executive worked within his current industry up to the respective year, based on the information of the 2400 companies and executive data over 20 years. The current industry is classified by 1 digit SIC codes (column U) and each firm has an individual firm identifier which is the CUSIP in column S.

For example, when applying this to the first row in the screenshot (Ira Eichner), I would need a formula that counts the number of different CUSIPs (thus firms), given that the EXECID (executive ID, identifier for each executive; column I) equals 9248, the 1 digit SIC code (column U) equals 5, and the Year (column J) is below 1992.

View 7 Replies
ADVERTISEMENT
May 9, 2012

I need to count all the cells in columns H, I, and J that contain a number either less than -.5 or greater than .5.

Separately, I also need to count all the cells in columns H, I, and J that contain a number either less than -.5 or greater than .5 if the corresponding cell in column G is not empty.

View 1 Replies
View Related
Feb 12, 2013

I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.

Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.

View 5 Replies
View Related
Jan 30, 2014

I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.

Type

Description

Concern

Formal

Informal

[Code] ....

View 6 Replies
View Related
Oct 19, 2012

I want to have the number of used columns in a worksheet. The information will be used to understand what range the data is in. So for the rows I am using nRows = Application.WorksheetFunction.CountA(myRange). I am hoping to have something similar for the columns.

View 6 Replies
View Related
Dec 19, 2012

I have this data lets say id denotes a particular house and the multiple entries of the same house are for different members of a house. Now i want to calculate the total no of people who earn in a particular household i.e if a cell is filled or not.

idB C

00118000040000

001

001

001

002170000

[code].....

View 2 Replies
View Related
Mar 3, 2007

In my Macro , i add a worksheet and depending on certain conditions i decide whether to populate it or not.

At a later point of time i am checking if the newly created worksheet is empty or not using the following check :

If ActiveWorkbook.Worksheets(ISheet).UsedRange.Rows.count <> 0 Then

Now what i have noticed is that even in cases where the "ISheet" is empty the above condition evaluates to true for the first attempt. I cannot understand why ?

let me know if the query is ambiguous and i will post the code snippet

View 3 Replies
View Related
Mar 6, 2003

I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.

View 9 Replies
View Related
Feb 7, 2014

If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?

View 2 Replies
View Related
Aug 21, 2013

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

View 1 Replies
View Related
May 17, 2009

i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.

View 5 Replies
View Related
Jun 8, 2009

I would like some help from someone on this subject if possible. I am running a stats report in a workbook where each member of staff have their own running totals of how much work they do and how much of that work is incorrect (per week). I have worked out that if I use the following:-

=SUMPRODUCT(('Barry Brooks'!$A$1:$A$2000>=$B$95)*('Barry Brooks'!$A$1:$A$2000<=$C$95))

I get the total amount of work. To explain the above, it returns the amount of times a date appears between two dates i.e B95 is week commencing date and C95 is weekending; therefore giving the total amount of work in that period.

What I am struggling with is that I need a formula to look at the above date range and then look at another column that has either a ‘Y’ or ‘N’ (for yes or no) and for the formula to count how many N’s there are (thus how many in that given range there were)

It appears to me quite a small ask for Excel but I can’t seem to get it to work.

View 6 Replies
View Related
May 7, 2008

I have a column (L18 - L32) which consist of 15 cells.6 of these cells are colored(different colors) and the rest are blank.Im looking for a formula which will be able to count the amount of colored cells in my column.

View 9 Replies
View Related
Feb 1, 2013

I have need of VBA code that will check collumn D, starting at cell 7 until cell 206, for when data that is entered starts with 100. When this occurs it will remove all of the data in that same cell except for the last 12 digits.

Here's an example.

When using our barcode scanner to capture a FedEx Tracking number the barcode captures the following:

1001853514360009020200802541997796.

Embedded in that long string of numbers (which are formatted as Text) is the tracking number. Specifically the last 12 numbers are the tracking number. However, FedEx varies this code at times so the last 12 numbers are the tracking number only with the raw data captured by the scanner starts with 100. Until I find additional conditions this VBA code will have to ignore all of data that is captured in collumn D starting at cell 7 until 206.

View 3 Replies
View Related
Dec 2, 2008

I have two sheets of data.

Sheet 1:

...........Column A......Column F

Row 1........"F"............."acpt"

Row 2........"F"............."acpt"

Row 3........"F".............."---"

Row 4........"F"............."acpt"

Row 5........"G"............."acpt"

Row 6........"G"............."acpt"

Row 7........"G".............."---"

Sheet 2:

............Column A......Column B

Row 2........"F"............[count all "acpt" in Sheet 1!F:F if Sheet 2!A2=Sheet1!A:A]

Row 3........"G"............[count all "acpt" in Sheet 1!F:F if Sheet 2!A3=Sheet1!A:A]

In sheet 2, B2 should = 3 and B3 should equal 2. I can do it manually by simply adjusting the range in a countif(Sheet1!F2:F5,"acpt"), but I want it to be automated because the number of F's and G's in Sheet 1 is dynamic.

View 2 Replies
View Related
Nov 10, 2008

My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.

I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.

I have attached the sheet in pk zip format.

View 8 Replies
View Related
Aug 16, 2009

I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.

I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.

View 2 Replies
View Related
Nov 5, 2009

I am looking to get a formula that count the number of rows that match some

criteria. The sample table is listed below. I would to have a formula

calculate the # of rows in which values in column B (>=3 and <=3.9) along with values in

Column C = 0

IDRATINGAMOUNT(formula match)

003044.0 (5.0)

002992.5 0 x

000692.5 2.0

013804.0 0 x

017293.0 0 x

017832.5 0 x

019342.0 (1.0)

019013.8 (1.0)x

014823.0 (1.0)x

020762.5 (1.0)

COUNT346

B>=3, B<=3.9 = 3 rows

C=0 = 4 rows

Overall = 6 total rows

But if you combine both criteria, there are 6 rows that match. I'm having a

difficult time figuring our how to write the formula for this.

View 2 Replies
View Related
May 23, 2014

I need a formula that will look into a range of data and tell me whan the last time a value exceeded 0 (working backwards).

So below the first row would return a value of 6, the next 5, the next 0, the next 1 and so on....

I can do it with an if formula but the amount of days it will be looking at will be too many, plus the range will keep growing as time passes.

FriSatSunMonTueWedThuFriSat

222000000

111100000

111100011

110111110

000111111

000000011

111111111

111111111

5117400000

564000000

8110660000

0000018171318

View 3 Replies
View Related
Nov 7, 2008

I am working on a spreadsheet that contains multiple text conditions that I need to keep count of. I would like to try the countif but I don't know if this will work. Here's a summary of my spreadsheet

Column F in my spreadsheet contains 7 different regions (all text).

Column D contains an open or closed option that is not validated.

In my totals section of the spread sheet I need to count each region that meets the "closed" criteria.

View 3 Replies
View Related
May 5, 2006

in a2:a277 i have numbers 1-10, randomly. in m2:m277 i have numbers 0-4, also randomly. i want to count how many of the 1s in column a have a corresponding 0, how many have a 1, how many have a 2, etc. if you just give me one formula i can manipulate it for all 40 outputs i need,

View 9 Replies
View Related
Aug 14, 2008

I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.

xxx Open

xxx Complete

yyy Complete

yyy Open

xxx Complete

xxx Open

I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.

View 9 Replies
View Related
May 7, 2014

I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.

The formula I have is:

SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))

The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").

View 9 Replies
View Related
May 7, 2014

I need to count unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.

The formula I have is:

SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D

View 9 Replies
View Related
Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2

12342122

45671112

89101718

11122678

13144544

15162324

17189987

19201215

21221928

1976

2576

2345

4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies
View Related
Jun 3, 2009

I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly.

My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.

View 2 Replies
View Related
Jun 16, 2012

I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-

Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)

Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.

Cell B2 onwards, down and to right, all spent amounts by month

What I need is a formula to get year to date number, which will change to Executives requirement.

Cell-ABCDE

1Jan2011Feb2011Mar2011Apr2011

24100100100100100

34101200200200200

44102300300300300

Year to dateMar 2011( Months will be changed)

Account 4101( Accounts will be changed)

Amount should be 600 What Formula ?

My excel version is 2010.

View 7 Replies
View Related
Oct 31, 2013

I have text amount in L55 column. The amount is auto summed amount. Example, $ 5081.16

Now in C59 column (total FOB) I have to write the amount in text manually. Any formula to make the amount converted in text automatically??? I badly need this. Attaching image.

THE Format of text amount has to be like below.

ex: FIVE THOUSAND EIGHTY ONE AND SIXTEEN CENTS ONLY.

It has to be caps locked, there will be and between cents and main amount, there wil be cents after that. Attaching image.

5081.JPGā€ˇ

View 6 Replies
View Related
Jun 17, 2009

The example:

Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.

Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00

Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.

So the sort of result I'm looking for would be like

Assume todays date is 19-04-09

A B C

12-04-09 £5.00 Last Week 4 Payments Value £45.00

12-04-09 £10.00

13-04-09 £10.00

14-04-09 £20.00

View 9 Replies
View Related
Sep 5, 2009

field A1 & B1 are variable user input. The goal is to subtract B1 from A1, take the result and divide it by 176 (a fixed number) and display the result as minutes:sec.

For example:

A1 B1

14,000 (minus) 2,200 (= 11,800 /176 =) 67

I want to result (67) to display in field C1 as 01:07 (min:sec)

I tried (A1-B1)/176 and formatted C1 to be min:sec but the result displays as 5:27 I have no idea what that number represents.

View 4 Replies
View Related