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
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.?
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.
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
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:
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
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"
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.
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).
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.
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.
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.
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 .....
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)
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.)
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
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.
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.
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.
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.
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
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.
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.
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
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.
