# Counting Matching Occurrences From Multiple Columns

Feb 1, 2007
I have a sheet with data which is all sorted by columns.

What I want to be able to count the occurrence of an instance when 2 cells in the same row (but in different columns) match my given criteria.

Ive been using 'countif' to count the instances in a single column and I am now aware that this is about all it can be used for.

An example of my data is:

A B

1 a

1 a

1 c

2 d

2 c

3 b

3 b

3 c

3 d

What I want to be able to count for example is how many times '3' & 'b' occur on the same row (in this case the answer would be 2).

Im not very good with pivot tables and have tried but have not managed to find an answer. I really hope that someone can help as Ive been working on this probelm for days and quite frankly its doing my head in :-)

View 9 Replies
ADVERTISEMENT
Dec 5, 2013

I have data like so

A B

water juice

water water

juice soda

water juice

juice juice

I need to count the number of occurrences when the answers between column A and B are not matching.

View 3 Replies
View Related
Jan 9, 2008

what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.

View 9 Replies
View Related
Jan 29, 2014

I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.

formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?

View 2 Replies
View Related
Nov 13, 2013

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.

View 2 Replies
View Related
Mar 8, 2012

I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"

I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.

The following formula was able to give me the count of cells "N/A"

=COUNTIF('Master Data'!$UA59:$ALZ59, ">0")

and this gave me a count by Month/Year

=SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))

When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.

View 7 Replies
View Related
Nov 19, 2007

I have a fairly large spread sheet where I need to count the number of instances where the number in one column matches the number adjacent to it in a second column.

View 9 Replies
View Related
Jan 1, 2014

I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.

I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.

However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)

B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.

Range

B1 C1 D1 E1 F1 G1

2 7 19 45 22 13

H1 Total of matching numbers in cell range K1:P11

View 3 Replies
View Related
Apr 23, 2006

I have aspreadsheet detailing stores issues.

The columns are: Item, Qty, Date. I need to find out for each item; what is the most common issue qty and how many times that occurred and what was the largets issue qty and how many times that occurs.

I have a highly conveluted way of finding this out but theremust be a quicker way.

View 8 Replies
View Related
Dec 12, 2008

I have a slight problem...I have a range of values..

0113

1240

8430

0903

I need to count the occurance of a a particular no. from 0-9.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero..

View 14 Replies
View Related
Jun 5, 2007

I have 3 columns of data that contain horses names and these are sorted by a race time.

See attached txt file for example....

View 9 Replies
View Related
Mar 27, 2008

We've got a column of names. A single name might appear once or two hundred times. The column is over 25,000 lines long, so we don't want to have to count how many different users there are (besides the likelihood of doing it wrong!). Is there some way to make Excel count the number of unique entries in the list, ignoring multiple repetitions of the same name?

View 9 Replies
View Related
Dec 11, 2006

I have a spreadsheet which consists of Sheet 1 and Sheet 2.

In Sheet 1, Column I there is a list of country names which indicate who visited our website at a certain time. So for example England may be listed multiple times.

In Sheet 2 I have a table showing January through to December in a column and the different country names across the top in one row.

I want the Table in Sheet 2 to count how many times England (for example) occurs in Column I and to indicate that as a numeric value.

View 9 Replies
View Related
Jan 10, 2008

I am running Excel 2007 at work in compatibility mode. My company is in the process of switching to Excel 2007, but not everyone has it, so I save my files in Excel 97-2003 format, so they can be viewed by everyone.

Problem:

I am trying to count the number of occurrences of "dates" between a starting date and an ending date. (e.g., cells B16:B1000 contain dates when a test at work was performed. I want to count all tests that occurred between 12/30/2007 and 1/9/2007.) Please note that the dates cross the new year

More Info:

I have searched this board, found a similar post, and tried the different formulas listed but get a result of "0" (zero). If I try the same formulas at home using Excel 2000, they work. The formula types I've tried include regular and array styles I am led to believe that this is either an Excel 2007 bug (bug??? - Nah, not from Microsoft!), or one of the cute changes between the new and old versions that I keep running across. The last possibility is that I am doing something wrong, which a smart betting man wouldn't rule out either!

View 9 Replies
View Related
Oct 14, 2008

I'm trying to have a cell where I return the minimum value of column A but only when column B equals 380 and when C equals 80. I know how to do it if I select my range manually to be the range including all the 380's and 80's but I'd like to select the entire data set to look through so that I can easily change the criteria to say finding the minimum in Column A when B equals 420 and C equals 40.

View 6 Replies
View Related
Dec 27, 2012

How to match one cell against multiple columns.

I have numbers in columns a through cc and cannot put them in descending order. I am using this formula:

=match(CF1,A10:A1008,0) I have to use a new cell and type the same formula =match(CF1,B10:B1008,0) then again =match(CF1,C10:C1008,0) I AM RUNNING OUT OF ROOM! Is there anyway to search all columns =match(CF1,A10:CC1008,0) ?? This formula doesn't work when I type it in.

I have tried shift control enter for an array and still no go.

I have tried vlookup

I have tried Index

I have tried Iferror

I have tried isna

View 2 Replies
View Related
Mar 11, 2014

I need sorting a contact list of 3000 + so the emails match with the company name, and first and last name of the contacts

Column A is Email and Matches column C & D. Column B matches with Column E. I need A to match with B-E.

I've attached an example : Example Spreadsheet.xls

View 2 Replies
View Related
Aug 5, 2014

I'm working with a set of text values. The sets of text can be either 2 or 3 chars long (in this example 2), and are separated with a pipe. The end char for each set of chars in fixed. In this case a Z.

I'm looking for a formula which counts the unique / distict number of sets of chars. I have been trying to use COUNTIF with a wildcard (?Z), but unfortunately I haven't been able to find the correct formula yet.

For example.

|AZ|AZ|AZ|BZ|AZ|AZ|BZ|AZ|BZ|AZ| -> 2 (AZ, BZ)

|AZ|AZ|AZ|BZ|CZ|AZ|CZ|AZ|BZ|AZ| -> 3 (AZ, BZ, CZ)

|CZ|BZ|AZ|BZ|CZ|AZ|EZ|AZ|FZ|AZ| -> 5 (AZ, BZ, CZ, EZ, FZ)

|BZ|BZ|AZ|BZ|CZ|AZ|EZ|AZ|GZ|AZ| -> 5 (AZ, BZ, CZ, EZ, GZ)

View 9 Replies
View Related
Mar 2, 2006

I have one column with names and another with date ranges (JAN06) etc. I

want to count the number of times a name occurs within a given date range.

View 12 Replies
View Related
Aug 9, 2007

I want to count the number of times a certain value is found in columnB, only for unique values in columnA.

I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.

View 9 Replies
View Related
Apr 13, 2012

I'd like to count the number of occurrences a value appears in a column. Hard to explain what I'm after so I will draw it out:

Column A

2512

2512

2513

2513

2513

2513

2518

2519

2519

2519

I want to add a formula in column b that will add the number of times it appears as it appears:

ColA ColB

2512 1

2512 2

2513 1

2513 2

2513 3

2513 4

2518 1

2519 1

2519 2

2519 3

View 3 Replies
View Related
May 28, 2014

I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.

I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".

For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.

The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.

There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.

View 7 Replies
View Related
Aug 15, 2014

I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.

Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.

View 7 Replies
View Related
Aug 13, 2012

I have an excel sheet which is currently 1,100 lines long and expected to increase and has a number of dates in columns W-AO representing the dates in which someone has had an onsite assessment. I wish to report in column AQ if there has been any visit activity in the month, i.e August for this monthâ€™s report. Is there an Excel function which will allow for this? Im thinking something like having a drop down list to select the month at the top of the column and then the sheet returns a value of 1 for all lines where a there is a date equal to the month selected?

I guess it is also worth mentioning that the sheet is setup as a table.

View 8 Replies
View Related
May 22, 2014

I have a list similar to the one below. I need to compare four columns against each other and align matches alphabetically.

This is the data:

Nov

Dec

Jan

Feb

[Code].....

I tried using VLOOKUP but I can't figure out how to get it to return what I want in the format I want it.

View 2 Replies
View Related
Nov 23, 2011

=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))

The works when referencing two columns. I need a total count of unique across three columns.

Sample Data

A | B |C

Jason | Temp | 1

Jason | Temp | 1

Jason | Final | 1

Kim | Temp | 1

Mary | Temp | 2

My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.

Temp | Final

1 | 2 | 1

2 | 1 | 0

View 5 Replies
View Related
Jan 14, 2014

I'm looking for the easiest way to count the number of occurrences within a cell range.

The formula that I'm currently using is:

=COUNTIF(D$5:D$8,"a*")

This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;

When I want to find multiple values in the range and count them all, I use this formula:

=COUNTIF(D$5:D$8,OR("a*","b*","c*"))

View 9 Replies
View Related
Mar 10, 2009

I'm on Excel 2003 and have a sheet to track people who have read my emails. I want to know the answers to the questions below. I've included a fake sample to show what I'm looking at. The number of cells in each column is much higher and not the same per column. I've tried variations of MATCH, VLOOKUP, SUMPRODUCT, etc. but haven't found a good way to set up a formula.....

View 6 Replies
View Related
Jun 17, 2014

Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'

Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)

View 3 Replies
View Related
Sep 27, 2013

I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.

I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.

View 5 Replies
View Related
Jul 10, 2014

I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.

I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.

------ UPDATE

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

View 10 Replies
View Related
Mar 29, 2007

Look at 2 columns and assess if certain criteria and then count the number of these certain criteria. I give an example below:

Column 1: Has a drop down box of possibilities from: "Red", "Amber", "Green", N/A

Column 2: Has a drop down box of possible choices of: "Significant", "Other".

What I would like to do is have a formula which will count the number of times you have "Red and Significant", "Red and Other", "Amber and Significant", "Amber and Other", "Green and Significant", "Green and Other" and "N/A and Significant" and "N/A and Other".

View 3 Replies
View Related
Sep 20, 2013

So, I have two columns on a sheet that all contain drop down boxes for various information. We'll refer to them as column 1 and column 2.

In column 3, I want to have the highest instances of those words sorted with the highest on top, and downward from there, along with the number of times it appears in either column 1 or 2. (And I want the sorting to be dynamic, i.e. when I change what's in column 1 or 2, it will re-rank them according to highest number)

i.e.

Column1 Column2 Column3 (Column4-#)

Bob Tom Bob 5

Joe Bob Tom 2

Bob Joe 1

Tom Bob

Bob

I have the drop downs and what not for the 1st 2 columns, I just need to know how to make 3 & 4 happen.

View 1 Replies
View Related
Dec 24, 2006

How do I create a code that record on row 1012, starting with column N, will count the number of rows between each "0" record in the range F7:F106. Perform the same function for number "0" in the range G7:G106 and recording in N1013.

Perform the same function for number "0" in the range H7:H106 and recording in N1014. Perform the same function for number "0" in the range F7:H106 and recording in N1015.

View 9 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
Apr 6, 2014

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.

View 7 Replies
View Related
May 9, 2008

I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.

What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.

A simple table example would be:

colA colB colC

Dan Complex Build House

Dan Complex Build Shopping center

Dan Simple Take dog to walk

Jose Simple Clean desk

Maria Medium Paint office roof

Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex"))

But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...

View 3 Replies
View Related
Apr 2, 2014

Essentially I want to show how many loans funded in October 2013, from ABC Lending, and I want that result to show in cell K9 of the Sample Output sheet. However, since I'm dealing with filtered data, I cannot figure out the correct combination of formulas to make this happen (e.g., SUBTOTAL, VLOOKUP).

Specifically, I'm stuck with the combination of a) counting the results that meet the monthly date criteria, combined with b) the filtered results. The single cell that I want to solve is K9, on the attached "Sample Output" sheet. Once I see how this cell works, I can figure out the rest. This cell is looking for the number of loans that funded in October 2013, from ABC Lending.

The filtered results are located on the "SCORE Data" sheet. The filter is already configured to only show results for ABC Lending, and the relevant column is labeled "Funded" (column "I"). Since the "Sample Output" sheet is looking for loans that funded in that month, and not a specific date, I created column "U" on the "SCORE Data" sheet, to convert specific day-dates to month-dates.

View 3 Replies
View Related
Apr 19, 2013

I have two spreadsheets consists of site ID's and scheduled dates. I need to produce a summary of all sites that are on the same day.

Sheet 1 (A2:B1155)

Sample

883408-Jul-13

211309-Jul-13

448110-Jul-13

010009-Jul-13

972524-Jul-13

Sheet 2 (A2:B1155)

Sample

032009-Jul-13

017222-Jul-13

535022-Jul-13

324024-Jul-13

Using the dates in sheet 2 can I return a list from sheet 1 of all sites that occur on that day

032009-Jul-13 2113 0100

017222-Jul-13

535022-Jul-13

324024-Jul-13 9725

View 5 Replies
View Related
Dec 17, 2008

My question is presented, in details, inside the WB.

View 14 Replies
View Related