I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
View 5 Replies
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.
A B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
I have data in 2 columns.
COLUMN A COLUMN B
2008-01-01 00:00:00 CRIMINAL EVENT
2008-01-04 00:34:48 OTHER
2008-02-04 00:23:59 SUSPICIOUS INCIDENT
2008-01-31 23:59:59 ENEMY ACTION
2008-01-08 00:45:43 FRIENDLY ACTION
2008-01-09-00:45:33 RAID
I need a formula or macro that will count all the times "Criminal Event", "Other", "Suspicious Incident", and "Enemy Action" occured in January. Also if possible I need to not specify column ranges, such as A2:A7, but instead it needs to find the last cell containing data in Columns A and B, and use that as the end point. For example it would search A2:Last Cell Containing Data
I have two columns of data each with repeats. I want to be able to look up the third column of data and then list the multiple results attaching to the first two columns.
I have attached a dummy spreadsheet of what I am trying to achieve.
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies View RelatedUREC Bar Event Sheet.xlsx
I am trying to correspond prices with products from the validate function in multiple columns. I want to select the product and have the price for the product populate in the 'cost' column. I have the list of products with corresponding prices on sheet 2 named 'products'. I have attempted to write a formula in cell G11 on the Event Sheet, but was unsuccessful. Please direct my path.
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
Sample data:
Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication
[Code] .....
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
I need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-
1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc
a b c a a b a a b b
If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.
I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.
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
What I'm trying to do is input a formula in col G which will look for instances of the city named in col F in both cols A and C. This should then return the total of these, from cols B and D that have the letter "F", into col H. Therefore, in the attached example, cell G2 would return "1", G3 would be "0" etc.
Should I be using VLOOKUP or COUNTIF, or maybe a combination of these or something totally different?CityCodeCount.xls
I 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 have multiple columns in excel that contains values like this
A B C D E F G H
TrxVolTrxVolTrxVol Trx Count Vol Count
122001400013500
125031290012499
130001300012700
Now at the last columns G & H i want to get the result that how many column of title "Trx" are having values greater than or equal to 1 and how many columns of title "Vol" are having values greater than 2500 respectively.....
I have a column (A) in sheet1 with these values:
Code
a1 04800128
a2 04800178
a3 04800128
a4 04805555
a5 04800128
And in Sheet2 - Column A and B has these values
Code
a1 04800128
a2 04800128
a3 04805555
a4 04800128
a5 04800128
Status
b1 Y
b2 Y
b3 Y
b4 Y
b5 N
I need to count in sheet1, where the code of sheet1 will be matched with sheet2 code and its status should be equal to "Y" .. I do not want to hard code these values as I have a huge data.
I 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 need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very frustrating.
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
If I have 2 excel files. One with data about electronic equipment and one with more general counting data, i will explain..
For example the electronic equipment excel file contains data about notebooks, desktops, epads etcetera and also prices and how old they are etcetera. The excel file also has a column for serial number, so for example a notebook serial number looks like this: 23N34ERT3 and an epad SN looks like 25OKE445EE. IF i filter the SN on text and begins with: ??N then it will show me only notebook data(because the N stands for notebook). Same if I only want the Epad I just filter on ??OK, so i get all the epad data.
now for the general counting data file, in this file i actually want to put data which i get from the other electronic equipment file. For example i want this data to be retrieved:
a. total number of rows of notebooks from the electronic equipment file
b. total number of rows of epads from the electronic equipment file
c. how many rows there are for notebook that are 0 - 1 years old(in electronic equipment file there will be a column called product_Years so in this column you have data like: 1,4,12,3) + that are from model: A from the electronic equipment file
d.how many rows there are for notebook that are 2-3 years old + are from model:A from the electronic equipment file and then going on for 3-4 years model:SD etc......
e. in the electronic equipment file there are prices for each model, i also want to calculate the prices for each rows which i get here in the list above.. These rows must be calculated with prices from electronic equipment file
Is there any easier way than constant filtering and copy pasting the data?
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
View 14 Replies View RelatedI have a workbook (attached).
I want to count the number of times North AND Win, North AND Lose etc.
Can anyone provide me a way of doing this. I dont want to have to use B2&C2 for example. I know I could do this and then use an IF statement (I think).
I have 2 column: item and quantity. I need to count number of items. In column "a" some item are more than one time. I need something like SUMIF function but by using vba. open attached file
View 3 Replies View Related1. I need to count how many different items within one column, these are formatted as Text. cells G63:G1600
Sample text:
F41D6
F44DD
FABHD
FAXHD
FDTGD
FFDGD
FFTED
FGDFD...................
2. I need to count how many dates fall during the week using the ending dates. Weeks ending 1/06/07, 01/13/07,etc L2:L53, the dates to count are L63:1600.
Sample Text:
09/05/07
09/21/07
09/07/07
09/17/07
09/21/07
09/05/07
10/03/07
09/13/07
09/12/07
09/18/07
09/18/07.............
I need a quick way to count the number of items in a column
For example
COLUMN A COLUMN B
apples 3
oranges
pears
bicycles 4
cars
trains
buses
There are over over 17.000 rows
Counting unique items in column (numbers)
View 9 Replies View RelatedI have a two massive spread sheets in which I am comparing items from one with the other and changing in one if the other doesn't match the first. I already have reduced the processing time from 6 hours to about 2 minutes by rewriting the macro to use autofilter to make the changes. The problem I have now is that I need to keep some metrics on the changes i.e. when I apply an autofilter, I need to be able to assign a variable to the number of items actually shown after autofiltering so I can add it to a variable that is keeping track of total number of changes. I know that outside of VBA, in regular excel use, the status bar displays # of total# found during autofilter, so is there a way to tap into the program and pull that number during execution of a macro?
View 9 Replies View RelatedI'm new to the forum - I've done a search but cannot find the answer to my problem.
I work in a hospital and have a list of consultants who refer for a particular type of scan over a period of time.
Each time the consultant refers their name is added to a list so I get a list like this:
Dr Smith
Dr Smith
Dr Jones
Dr Smith
Dr Paeker
Dr Paeker
Dr Jones
etc...
I am working with VB 6.0. So far I have been able to create my recordset, opened excel spreadsheet and imported data to the spreadsheet using the recordset. The spreadsheet has five fields and each field has multiple items as in the example below.
Name:Pass#:Amount:
John:22222:500.00
John:22222:500.00
John:22222:500.00
John:22222:500.00
------------------
------------------
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
Jane:52522:300.00
------------------
------------------
What code can I use to count all the occurences of "John", "Jane" in the spreadsheet?
