# Add Separate Columns Together To Get A Total Count Of A Specific Criteria

Jul 20, 2009I am trying to add separate columns together to get a total count of a specific criteria. The formula I am using is:

View 3 RepliesI am trying to add separate columns together to get a total count of a specific criteria. The formula I am using is:

View 3 RepliesADVERTISEMENT

In a worksheet of marks of students, i have entered grades A,B,C,D,AND E.Grades are entered in cells o3,AB3,AO3,BB3 AND BO3.

In BQ3,I want to get -in the range of O3:BO3

a)how many "A" are there?

It should display for example A=2,

b) how many "B" are there?

It should display for example B=2,

c)how many "C" are there?

It should display for example C=2,

d)how many "D" are there?

It should display for example D=2,

e)how many "E" are there?

It should display for example E=2.

In BR3, I want to get >

If A=10, B=8, C=6, D=4, E=2 then

display the total value for the grade letters.

Pls see the attached file for more clarity.

I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8

View 2 Replies View RelatedI have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified

Location

Location

Location

[Code].....

I have been able to analyse the average results for the whole dataset by using the following formulas:

=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed

=ROUND(AVERAGE(D2:D4),2) Average response for medical centres

=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph

But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.

=IF(A2:C4,"camden"),COUNTIF(D2:D4),1

=COUNTIF(D2:D4),1,IF(A2:C4,"camden")

I need to take the data out of one column from sheet A and compare it to the data on another column sheet B.

Real life example:

I have a very large list of contacts with multiple columns of data. On a separate spreadsheet, I have a list email addresses that I need to remove from the larger list of contacts. How can I compare them against one another to detect any matched email addresses for deletion?

List of contacts is appx 130mb

List of email addresses is appx 4mb

I have repetitive task of copying and pasting. Macro to sort some data.

I have time values (sorted in order) in Column A and a value in Column B.

Sheet1 AB1TimeValue

200:00:002300:00:002400:00:001500:00:002601:00:004701:00:002802:00:001902:00:0021002:00:0031102:00:0041202:00:0011302:00:002

I need separate the time each hour of data into columns. So the above would become

Sheet1 ABCDEF1TimeValueTimeValueTimeValue

200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001 02:00:003500:00:002 02:00:0046 02:00:0017 02:00:002

There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.

I've got two columns of numbers, such as:

(Col A) (Col B)

Cycle Bin

1 - - 3

1 - - 7

1 - - 7

1 - - 2

1 - - 5

2 - - 7

2 - - 9

2 - - 6

3 - - 7

3 - - 2

4 - - 2

4 - - 8

4 - - 2

4 - - 8

4 - - 5

I'm trying to get the total number of columns that have a specific match-up, for instance, how many cells are in Cycle 1 with Bin #7? ....

I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

NAMES-------THIS-------------THAT

Mary-------some text--------some more text

John-------<blanks>---------just text here

Sue--------just some here-----<blanks>

Dave-------something--------something else

Adam------<blanks>-----------<blanks>

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

I need to be able to count all the blanks in columns other than A but only until the last used cell in column A. I am using a formula right now that counts the blanks in column A until the last used cell but I don't know how to apply the range of column A to other columns like B and C. Here is an example of what I hope to accomplish:

Formula used in A1 that I need applied to other columns but with the range of column A

="Total Blanks: "&COUNTIF(INDEX(A2:A8,MATCH(TRUE,A2:A8<>"",0)):INDEX(A2:A8,MATCH(2,1/(A2:A8<>""))),"")

Here is an example of what B1 and C1, with the formula, would look like if it counted blanks but with the range of column A

Total Blanks: 3

Total Blanks: 6

Total Blanks: 2

[Code].....

I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.

I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.

how can I sum up all the "Type of Defect"[CR, LOF, DR] for a specific Welder. They were tag as Welder 1-7. In the attached example (Sheet1- Data), Welder 1 has 2 "CR" and 1 "LOF", this figures to be reflected in Sheet 2 (TOD).

View 2 Replies View RelatedI have data in the following type

A Criteria B

A Criteria C Criteria A

C Criteria C

D Criteria 1

2 Criteria 3

Criteria 2

3 Criteria

I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.

I'm trying to create a formula that tells me the following:

If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.

I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...

View 3 Replies View RelatedI want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?

View 5 Replies View RelatedAttached excel sheet below. Suggest a formula to get the count of items that fall in a specific data range ?

Count of items that specify a range criteria.xls

How can I count the number of used columns in a specific row? The below code doesn't work.

View 14 Replies View RelatedI have two columns with numbers in ranging as follows:

2

2.5

3

3.5

4

4.5

5

On the spreadsheet it looks like this:

4.5

5

4

4.5

3.5

3.5

3.5

4

3.5

3.5

3

3

3.5

3.5

What I need to show in the next column is a True where these two figures add up to 8 or more. However, the minimum in each column needs to be 4 and 4 i.e. if one column has 3.5 and the next 5 then return a value of False.

Countif must meet criteria in 2 different columns. I can get one criteria in, but not both.

View 3 Replies View RelatedI have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.

Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).

Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).

VB:

Sub cond_copy()

'assuming the data is in sheet1

Sheets("Sheet1").Select

Sheet2.Range("A2:G5000").ClearContents

[Code] .....

I have a workbook in Excel 2003 with two spreadsheet, a data sheet and a summary sheet. The data sheet contains account numbers in column A and monthly values from column B to M (April to March) for each account. Account numbers occur more than once in column A.

The summary sheet contains each account number as in the data sheet, but only once with a month column and a YTD (year to date) column. Part of my function is to report on a given month number as well as a year to date number as at that given month. For example if it is May, I need to report the results for May as well as April and May totals. Cell C 1 contains the value of the particular month (say month 1 equals April and month 12 equals March).

I need a formula or function to return the year to date results given a certain month (cell C1) for each account number. The sumif function does not help since it only returns values for 1 column. I need the formula to perform a sumif function but if I put in 2 in cell c1 I need the formula to add the values in columns B and C, if I put a 3 in cell C1 I need the formula to add the values in columns B, C and D.

What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.

I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria.

If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5.

When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.

Company

Option 1

Option 2

a

True

True

b

True

False

c

True

False

a

True

False

a

True

False

b

True

True

Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.

View 1 Replies View RelatedI am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.

I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.

1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.

2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.

Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.

I am trying to use a "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.

View 3 Replies View RelatedI want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.

A B

1 1.00

2 Yes 4.78

3

4 5.00

5 Yes 4.89

6 11.99

So this example would count 3

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:

DCM1005-1

DCM1005-10

DCM1005A

MTR1005-1

MTR1005-10

MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)

A1, B1, C1, D1

DCM1005-1, DCM, 1005, 1

DCM1005-10, DCM, 1005, 10

DCM1005A, DCM, 1005, A

MTR1005-1, MTR, 1005, 1

MTR1005-10, MTR, 1005, 10

MTR1005A, MTR, 1005, A

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO

406AMZN

451AMRC

404AMAD

605ANCC

405ADRC

The result would be:

302 ALTO

406 AMZN

451 AMRC

404 AMAD

605 ANCC

405 ADRC

I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.

Situation:

I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this

jp RET

jp RTO

jb RET

ma RTO

Rn CI

ma Fur

tc CI

ma RET

jp RET

and I want to have a count of how many RET's there are for Initials JP ect

I have month to month data for how many people have left an organization. I'm interested in tracking three things, how many of them left voluntarily, how many of them left involuntarily, and how many left within 90 days. The way to do it was to create a bar graph with one bar that would indicate total people who left and one bar that would indicate how many left within 90 days. However, I want the total bar to be made up of two separate bars (a composite, see this link for an example of what I mean Graphing- Bar Graphs ) one for involuntary, and one for voluntary.

View 3 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved