Counting Items If They Match A Condition In Another Column

Apr 2, 2012

I need to be able to count items if they match a condition in another column, as below

1 Apple Red
2 Apple Green
3 Apple Red
4 Pear Green
5 Apple Red
6 Pear Green
7 Pear Red
8 Apple Red

How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.

I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.

View 3 Replies


Count Items In Column That Match Multiple Data Items?

Mar 27, 2014

I need to count the total number of times 4 different values appear in a column. This formula works for one value:


Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.

View 6 Replies View Related

Counting Number Of Unique Items In Column

Oct 10, 2008

I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data

a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears

in this case number of uniques items is 3

Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.

View 6 Replies View Related

Counting Unique Values Based On Condition In Different Column?

Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 Replies View Related

Match Column And Colour Based On Condition

Feb 22, 2007

i am trying to do is match 2 columns data based on a condition ie i have data in column "b" of sheet called "balance" data is variable this column has unique indentifiers i want to look at another column and match the uniques identifiers in another sheet if the cell value in column "D" = "please investigate" otherwise do nothing, the (column f) it will be looking up too is in sheet called "hi- port" so if cell value in column "d" of sheet"balace"= "please investigate" match values that are same from colum "b" of sheet "balance" to that of column (f) sheet called "hi-port"

i would like to colour only entire row of sheet called "hi-port" when the data matches to other sheet ie from columns "f " too " b". colour can be any colour
igonre my ranges they are wrong just used as example

Sub start()
Dim oWs As Worksheet
Dim oRngCheck As Range
Dim oCl As Range
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Set rngIRd = Range("a1", Range("a65536").End(xlUp)) 'define the rang
Set rngACs = Range("b1", Range("a65536").End(xlUp)) 'define the rang
Set oWs = Worksheets("balance") 'change to suit
'starts at b2 to llow for header row.......................

View 4 Replies View Related

Copy Rows Where Cells In Column Match Condition/Criteria

May 5, 2008

I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet

This is exactly the same concept I am trying to accomplish, but don't know how all. I couldn't follow the thread either.

I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".

Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate

View 9 Replies View Related

Lookup Column Of Data Match Criteria From Another Sheet Display Items Vertically

Feb 5, 2014

(File is attached here)

I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.

View 3 Replies View Related

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

View 9 Replies View Related

Counting Number Of Times INDEX / MATCH Finds More Than 1 Match?

May 23, 2014

We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.


The columns that need to be filled are shaded in dark pink.

View 5 Replies View Related

Looking Up Multiple Items On More Than One Condition

Jul 18, 2014

I have added this example:

Test lookup main supplier.xlsb

I have a list of 5000+ items that I need to check (only 6 of them in this example in the red table).

I have a list of 25000+ items in a lookup table (only 10 of them in this example in the blue table).

Each item can have more than one supplier, but has only ONE Main supplier.

I need VBA code that determines if the supplier in B1 already is a main supplier for that specific item in the lookup list (yes or no).

I tried filtering it but that appears to be too slow for 5000 x 25000 items.

I have tried with VLOOKUP and Match, but I do not know how to combine the two conditions.

View 7 Replies View Related

Counting/Summing Items

Jul 20, 2009

I have a separate sheet with the followingvery simplified as I probably have over 300 lines of dates and dollars)

Date Dollars

2/3/2009 $25
5/3/2009 $30
5/4/2009 $50
7/4/2009 $100
8/7/2009 $25

On another sheet, I want to count the number of items by month submitted and sum the $ amount.

January $0
Febuary $25
March $0
April $0
May $80
June $0
July $100
August $25
Through December

Pivot table would be best if possible....but any function that would work would be fine.

View 9 Replies View Related

Count Condition Unique Items

Jan 22, 2009

Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).

Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)

View 14 Replies View Related

Count Unique Items With A Condition?

Oct 30, 2012

I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.

I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.

Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.

I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..

I just need to count the unique item with another condition.

Item details
Order week
Delivered Week

Item 1

Item 2


View 3 Replies View Related

Returns Items Matching Condition

Jul 24, 2007

I am trying to have an order form auto populate information, however I have run into a small problem when doing so. I am unable to use VBA because it is locked by whoever originally created the spreadsheet so I need a formula solution if possible. I would like for the worksheet "Order Form" to auto populate the type of printer cartridge when there is a 0 in the column "On Hand" on the "List" worksheet. I know a loop could do the trick but I was wondering if maybe a vlookup or if statements could get the job done. Here is a completed sample of what I would like to happen.

View 4 Replies View Related

Counting Items In A Date Format

Aug 14, 2014

I have a spreadsheet that I use to log incidents. In column "A" I have the date in month/day/year format (8/14/2014). I am attempting to automate the graphing process so I have a graph that will tell me how many incidents I had in the month of August. I have attempted the following formulas (and other similar formates with minor changes)...

=COUNTIF('CY14'!A2:A500,"8,2014") and changed around the date format

All of these come up with "0" while I have 17 listed incidents for August. Am I doing something wrong, or is this not possible.

View 4 Replies View Related

Counting Unique Number Of Items

Sep 15, 2008

I want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.

View 9 Replies View Related

Counting Number Of Items In Between Dates

Nov 5, 2008

I need a formula which can count the number of items which have been applied to a suspense account in between a set of dates so for example i may need all items placed into a suspense account in the month of october. i record all items in a list so i do have a list of dates i just need to know how to count them and keep a running total as i need to feed it into another spreadsheet. I will also need to sum these the format of the spreadsheet is below -

******** ******************** src="<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book4___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2K2B3K3B4K4B5K5B6K6B7K7B8K8=ABCDEFGHIJKL1MOPO/S Date AppliedValue DateRemovedValue AgeCompany Name2Cheque  01-Nov-08(15.55) 01-Nov-08 15.55 0abc3Direct Receipt  01-Nov-08(109.00) 01-Nov-08 109.00 0def4Cheque  02-Nov-08(50.30) 02-Nov-08 50.30 0ghi5Cheque  03-Nov-08(23.48) 03-Nov-08 23.48 0jkl6Direct Receipt  03-Nov-08(2,236.46) 03-Nov-08 2236.46 0mno7Cheque  03-Nov-08(212.11) 03-Nov-08 212.11 0pqr8BACS/CHAPS  04-Nov-08(58.75) 04-Nov-08 58.75 0stuSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Counting Items In Filtered List

May 8, 2009


I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?

View 9 Replies View Related

Counting Items With Multiple Criteria

Sep 8, 2006

In column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:

1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C

So any formulars can do that in instead of using the sorting method?

View 3 Replies View Related

Hide Pivot Table Items By Condition

Nov 24, 2009

I copied and modified the pivottable code from I am getting a "Run time 13" error on the line I colored purple. I tried removing different "Dim" statements to make it work and I'm having no luck.

Sub PickUpPivotTable()
Dim pt As PivotTable, pi As PivotItem
Dim lType As Long, lHarn As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
Set pt = Worksheets("Monthly Pivot Summary").PivotTables("MonthlyPivotSummary")
strCri = "P/U"
For Each pi In pt.PivotFields("Type").PivotItems
lType = pi..........................

View 6 Replies View Related

Formula For Counting A Number Of Items But Excluding Some Of Them?

Jan 19, 2014

1. I use this formula to count a number of items in a column per month: =SUMPRODUCT(--(MONTH(B6:B500)=8))

I also have another column C where I put some descriptors against the values in column B. I need a formula to calculate a number of items in Column B, except for those items, which are equal to XYZ in column C.

2. I also calculate a number of items, which were completed in a given month. Completion is marked with "Y" in a separate column (D). I use this formula: =SUMPRODUCT((YEAR(B6:B999)=2013)*(MONTH(B6:B999)=12)*(K6:K999<>"Y")). I can't make it count a number of completed items in January. C

View 8 Replies View Related

Counting Unique Items(values Or Text)

Aug 3, 2006

I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.

column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1

now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter

now what should i suffix or prefix to this formula to count unique values in column A.

View 11 Replies View Related

Counting Items Unique To A Day/customer Combination

Feb 3, 2010

I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.

To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.

View 3 Replies View Related

Counting Every Nth Occurrence (with & Without A Condition)

Sep 24, 2009

I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I’d want to count the negative occurences in B2, E2, H2, K2 etc. Also, I’d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.

View 4 Replies View Related

Counting Observations Under Given Condition

Oct 20, 2009

I'm trying to get 3 formulae for calculating days in drawdown…
Column A has daily dates and Column B has drawdowns which are either a negative number or 0(which means there is no drawdown)…

Every drawdown period starts with a negative number, and as long as the numbers continue negative the streak continues until it is stopped with a zero.

1. Need - Longest period in drawdown….this should basically be the most number of consecutive negative values in Column B (before the streak being interupted with a 0)

2. Days in Current drawdown - this is the most recent streak of negative numbers, in our case the answer is 10 as shown in column F. (hopefully formula detects the date and figures this out)

3, Days in Maximum drawdown - this is the negative streak which had the lowest value…….in our case I believe its about 292.

View 6 Replies View Related

Counting Dates On A Condition

Jun 1, 2009

I have a range of ten cells (B4:K4), some of these cells contains dates, and other cells contains text, what I want is counting number of cells that contain dates earlier than today's date. I actually tried the following code, but it returns zero value.

View 2 Replies View Related

Multi Condition Counting

Sep 24, 2009

I have three columns of data


I am trying to calculate two things:
first, a simple count of entries (non-blank) in Col-B corresponding to 'a' in col-C. Ex. for Col-C='c', the no of entries in Col-B is 2. Here I was trying SUMPRODUCT(--(Col-B range""),--(Col-C range="a")) with CTRL+SHIFT+ENTER. It doesn't work.

second, a more complex count without repetitions. Ex. for Col-C='b' and Col-B='A', the no of entries in Col-A is 1, because there are two 1's. If there would have been three 2's and four 3's the count would be just 2.

View 9 Replies View Related

How To Use Form Control Dropdowns To List Items Based On Condition

Jun 16, 2014

I have 2 dropdown lists.

The 1st shows the portfolio list and the 2nd one should display project names based on the portfolio selection in Dropdown1.

My data resides in another sheet where Column B is the Portfolio list,Column C project list and Column D to X some data related to the project.

I have to give cell link reference to the project selected in dropdown 2 so that the other values in the dashboard changes based on a vlookup formula.

I have attached the sample sheet for reference with some dump values.

Dashboard sample.xlsx

View 3 Replies View Related

Counting Unique Text Entries In A Sheet With A Condition

Sep 6, 2005

Counting Unique text entries in a sheet with a condition ..

View 14 Replies View Related

Formula To Match 2 Items

Jan 29, 2007

I want to match let's say "Elbow" with "Yes" so that when I select the word "Elbow" from a list the word "No" appears on another cell. There are 54 words to match. This is what I'm currently using, however, I could only get to 29 items and I need to match 54.

=CHOOSE(MATCH($J$2:$J$100,{"Abdomen","Acetabulum","Acromioclavicular Joint","Ankle","Arm","Back","Carpus","Cervical","Chest","Clavicle","Coccyx","Elbow Joint","Femur Bone","Fibula","Fingers","Foot","Forearm","Glenohumeral Joints","Hand","Head","Hip","Humerus","Ilium","Ischium"," Joint","Leg","Lower Leg","Lumbar","Metacarpus","Metatarsus","Neck","Patella","Pelvis","Pubis","Radius","Ribs","Sacrum"," Scapula","Shoulder","Spine","Spine - Lumbosacral","Spine - Thoracic","Spine - Thoracolumbar","Sternoclavicular Joints","Sternum","Tarsus","Thigh","Thoracic/Dorsal","Tibia","Toes","Ulna","Upper Leg","Upper Arm","Wrist Joint"},0),"No","No","Yes","Yes","Yes","No","Yes","No","No","Yes","No","Yes","Yes","Yes","Yes","Yes" ,"Yes","Yes","Yes","No","Yes","Yes","No","No","Yes","Yes","Yes","No","Yes")

View 10 Replies View Related

Copyrights 2005-15, All rights reserved