Count Cells That Satisfy A Condition
Apr 30, 2007
I have cells in a column, which contains text and numbers. I want to count the number of cells that have value greater than zero.
I tried using DCOUNT, but I got no idea why is its not working
=DCOUNT(I15:I114,1,J5)
where the values are form I15 to I114.. and J5 contains the condition >0
Also, while I am at it.. Is there a straightforward way in excel to get the count of the max run of cells with values greater than zero.
for e.g if cells from 18 to 30 have values greater than zero , and cells 44 to 50 have values greater than zero. then I want it to return 13. i.e (30-18)
I am hoping that there is a simpler way than looping through the column in VBA
View 6 Replies
ADVERTISEMENT
Jan 26, 2007
I have a few (eg file1, file2, file3, file4, file5) files in a directory and i want to open them as individual excel file and I need to do a check on all the opened files first. If Cells(11, 4) = "important", then Count will start from 0 and workbook will be saved as wb_0 , wb_1 and so on. Else the workbook will be discarded and close. So if file2 and file4 are the only 2 files that satisfy the condition, then they will be saved as wb_0.xls and wb_1.xls.?
ChDir "C:Documents and SettingsDesktop"
For x = 1 To 5
Workbooks.OpenText Filename:= _
MyArray(x) _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:= _
False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array( _
1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, _
1), Array(9, 1)) .....................
View 5 Replies
View Related
Jul 27, 2012
I have a table with several criterias (Name of supplier, date, amount paid to this supplier). This table is my inputs. Each time I have an expense, I put down the name of the supplier and the amount of the expense and the date when it occurs.
I have another table with name of each supplier in rows and the date in column. Sometimes I have 2 amount for a same supplier for the same month. I would like this amounts sum in my table. I cant find a good formula to do it efficiently.
Obviously I can do a sum
(if(A2=B2, if(c6=c8);b10)+sum(if(A2=B2, if(c6=c8);b10)+ etc...
But I can 150 expenses per month...
View 2 Replies
View Related
Aug 21, 2008
i've got a list of values that need summing, but only if i certain value is in another cell:
a b
1 paid
1 paid
1 not
1 paid
1 not
i need a formula that will count all the values in column a only if column b is set to "paid"
View 4 Replies
View Related
Dec 15, 2006
the report I'm creating has rows of refreshable data with a header and I need to find a way to count number the rows where a "yes" value appears in column J, then paste that total number into another new sheet in cell E9. I saw it on here a few days ago, but didn't mark it
View 4 Replies
View Related
Jun 22, 2007
1.I have a map which fill with value in the cell like shown below.
2.I need to count the value in the cell using VBA.
3.There are many maps with different value that I have to count manually. So it takes time to count the value on by one.
4.By referring to the map, I want the VB to count how many value in each cell within the range of the map only, which mean I just want to count how many 1, 14, 19, 2, 99 and others.
5.The answer will be displayed on the bottom of the map like this:
For example:
bin1=?
bin14=?
bin19=?
bin2=?
bin99=?
bin8=?
View 3 Replies
View Related
Sep 8, 2006
I have a data sheet which has a number of columns
I am only interested in one of the column which is 'Type'. Within this column it specifies the type of order it is. I would like to count through the sheet and get a final count of the different order types on the other sheet, so if there are 56 instances of 'trace' orders then I would like this displaying on the other sheet as Trace = 56, and so on
View 9 Replies
View Related
May 8, 2008
I am attaching a sheet in which Data can be entered in cells C5 & D5 in "Calculation " sheet, depending upon the value of C5 it will count all the Occurrence of data of D5 in adjacent cell i.e. (one up & and one below of data of C5) . data will search in "PCM_PLAN" sheet in column " I ".
count will be given in E5 in "calculation sheet"
View 9 Replies
View Related
Apr 12, 2014
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
View 9 Replies
View Related
Jan 30, 2014
I have a worksheet that contains Country Abbreviations and related Codes. Some countries have multiple codes. How would I search for two countries and have it return me only the codes that are related to BOTH countries and not just one country.
Example:
Code Country
1 USA
2 GBR
3 HON
4 GBR
4 USA
5 HON
6 USA
6 HON
6 GBR
If I typed in or selected USA and GBR it would only return 4 and 6, not 1 and 2. If I use filters it returns 1, 2, 4, and 6.
type in the three-letter country abbreviations or be able to select them from a list and it return the correct code(s).
View 1 Replies
View Related
Jul 31, 2006
I am having an excel problem such that i have several months in a given year and I want to set up a formula where I can enter a specific year and it will return the sum of all the cells that belong to that year (see attached sheet). So basically if I have 2 months in 2006, 2 in 2007 and 2 in 2008 with corresponding values for each of those months, I want to enter a year in another part of the sheet and a value is returned to me to show me the sum of the revenue for all the months that are in that year. So if I enter 2007, it will sum both those values that belong to 2007.
The years and months are dynamic so I can't just sum the columns because in column D it could be 2006 (depending on the other inputs of my spreadsheet) or it could be 2007, so I essentially need some formula that checks the array to see if that cell belongs in a year that matches the criteria and if it does sum it to all the values that match that criteria. I have tried using vlookup, match, index but can't get it to work.
View 2 Replies
View Related
Apr 30, 2014
I'm trying to evaluate if 2 of the 3 criteria are met using an IF function in Excel 2010.
I have 3 columns of data. Each column has a number ranging from 0-15.
Example:
B2 = 0
C2 = 3
D2 = 4
Since B2 = 0, I'd like to have the equation return a 'Yes' because there are values greater than 0 for 2 of the three cells.
The hangup is that any of the three cells could potentially equal 0, so the equation must account for C2 or D2 being zero.
View 4 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Jul 2, 2009
I have a column with different cities that customers visit (Barcelona, Lisbon etc) and another column with their booking requirements (All inclusive, breakfast only etc)
I'd like to set up a count on the total number of customers who are going to Barcelona and having all inclusive.
I've tried using nested if statements with vlookups and a countif but can't seem to get that working.
View 7 Replies
View Related
Jun 26, 2008
I need a unique count of the ID number. What I need is to check and see if the row has a number in it and if it is greater than 0 and is not blank. If the row has 0 in it, then that is not counted in the count. For in stance .....
View 9 Replies
View Related
Jan 10, 2008
I need to calculate the lookup value that have more than one...
I want to hav a simple analysis on likert scale
I hav students from 3 (or more) institute (A,B,C). 3 survey questions...
How to calculate how many students form each institute that score 1/2/3/4/5 (base on the likert scale, maybe up to 10) for every single question (in my sample worksheet, i named it as Item1, Item2, Item3 as a sample questionaire)
View 5 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
Jan 18, 2010
I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.
I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003
View 4 Replies
View Related
Mar 15, 2009
I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.
View 14 Replies
View Related
Mar 16, 2009
problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB.
Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.
View 7 Replies
View Related
May 8, 2012
I have data like
col A col B col C col D
DLM2.2 kWDLM75 kWDLM55 kWDLM160 kWDLM11 kWDLM1.5 kWRDLM110 kWRDLM2.2 kWRDLM11 kWRDLM55 kWDLM11 kWDLM30 kWDLM45 kWDLM1.5 kWUS40 kVAUS10 kVAFD45 kVAFD45 kVAFD6.4 kVAFD25 kVAFD45 kVAFD11 kVAFD11 kVAFD150 kVAFD10 kVAFD18.5 kVAFD25 kVAFD18.5 kVA
The problem is col B having M type and col C contain their respective ratings. I want summary report.
Rating 2.2 should give me total count, but if you see there are types.
In col A -
DL
RDL
blank cells
so, 2.2 DL = the perticular count
2.2 RDL = the perticular count
Only M type having subtype DL and RDL.
Im col B, there another subtype also like US or FD. For that also particular rating show their exact count.
Is that possible? I want excel formula not vba code.
View 7 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
Dec 25, 2012
Build table with 2 matching condition.
View 6 Replies
View Related
Mar 19, 2009
1. Count of Unique Print Customers
2.Total of Print Revenue
3.Count of Unique Online Customers
4.Total of Online Revenue
5.Count of Customers advertising in both Print and Online
6.Total Online and Print Revenue
View 9 Replies
View Related
Jan 26, 2010
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.
So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.
Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.
View 6 Replies
View Related
Nov 15, 2006
I have a spreadsheet which looks like this: http://img46.imagevenue.com/img.php?..._122_521lo.JPG
I'm trying to figure out how to set up a macro which can generate a correlation table for each row. For instance, I would like for the macro to cycle through the row labeled 'List1' and record each cell value (B1, C1, D1, etc). Then, I would like to compare those values to the row labeled 'List2' to determine how closely they correlate. If List1's columns were identical to List2's columns, (order does not matter), I would want those two to have a correlation of 1 or 100%. If none of those two lists' columns match, I would want a correlation of 0 or 0%. In the end, I would like to only keep the List rows which are most uncorrelated with each other.
View 4 Replies
View Related
Jan 22, 2008
Here in the code wbResults is a Workbook present in my present folder. In the Sheet1 and Column "I", the result of test cases is entered. The Result of the TEST CASES can be PASS or FAIL. So i am trying to get the total number of PASS test cases and Total FAILED test cases. BUT with the code i have written, i am not able to fetch the right data. Is the method of comparing wrong? or what else? i have tried to debug but nothing comes out.
Do While Not i = 1008
If (wbResults.Worksheets("Sheet1"). Cells(i, 8) = "PASS") Then
PASS = PASS + 1
End If
i = i +1
Loop
View 4 Replies
View Related
Feb 20, 2008
I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.
The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.
I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.
I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.
View 5 Replies
View Related
May 28, 2014
I would like to check if there's a way to count the number of unique value with a given condition
Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector
Attached is the files, and my desired result is highlighted in RED.
View 3 Replies
View Related
Feb 5, 2009
Is there a way I can get Excel to sum/count in an IF equation if the condition falls between 2 points?
For instance:
I want to count the amount of days between two dates.. I am currently looking at something like:
=If(cell>[date1]&cell<[date2],sum(cell yada yada....)
Using <> wont work in this instance.
View 5 Replies
View Related
