# 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

A B

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
ADVERTISEMENT
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:

=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)

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
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
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
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
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 to...at 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
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
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
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.

sampleexcelhelp.xlsx

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

View 5 Replies
View Related
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
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
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
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

W1

W1

Item 2

W1

W1

[code]....

View 3 Replies
View Related
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
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

=COUNTIF('CY14'!A2:A500,"&DATE(8,2014))

=COUNTIF('CY14'!A2:A500,"August")

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
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
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="http://www.interq.or.jp/sun/puremis/...<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
May 8, 2009

{=SUM((C2:C8="black")*(B2:B8="sneakers"))}

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
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
Nov 24, 2009

I copied and modified the pivottable code from http://www.ozgrid.com/VBA/hide-pivot-fields.htm. 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
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
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.

so,

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
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
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
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
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
Sep 24, 2009

I have three columns of data

Col-A-----Col-B------Col-C

1---------(blank)-----a

3---------A----------b

1---------A----------b

2---------B----------c

2---------(blank)-----c

4---------D----------a

3---------E----------a

1---------A----------b

3---------B----------c

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
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
Sep 6, 2005

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

View 14 Replies
View Related
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