Counting a variable range. Does anyone know why this is not working...
Dim r
r = ActiveCell. Offset(0, 12).Value = Application.WorksheetFunction. CountIf(Selection, "Standard")
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = r
I'm having trouble getting a formula to sum the last 10 results of data entries, so far I have:
=SUM(OFFSET(INDEX(D2:D300,COUNT(D2:D300)),-9,,10,1))
This works fine on the assumption that all the rows contain a value 0 or 1, this may not be the case as rows may be left blank for a varying degree of time and results may be posted intermittently?
I've been asked to produce a spreadsheet that monitors the time it takes to answer queries from partner companies. Ive attached some of the spread sheet that im struggling to work out. I would like a formula that will total the number of days it takes us to answer the queries per month for each company.
Example - For January
Compnay A 8 Days
Company B 7 Days
Company C 13 Days
how to sum 3 cells when 2 out of 3 cells match.
Here is the data.
Cells A1:A10 = Florida
Cells A11:A20 = Florida State
Cells A21:A30 = ~Florida
Cells b1:b5 = W
Cells b6:b25 = L
Cells b26:b30 = W
Sum all cells that have "Florida", "~Florida" and "W" in common.
I am trying to implement a count on a table that splits up the number of customers associated with a list of managers. Sample data attached.
Count function.xlsx
So, I need some way to display a single instance of each manager name with a count of how many customers are associated with that manager. The number of managers and the number of customers associated with each manager does vary and are displayed in separate columns per the attached.
I'm having difficulty trying to write a formula for the following type of data:
[data] ....
This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...
I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.
I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.
However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.
For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.
You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedI am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.
View 4 Replies View RelatedI am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I currently have a spreadsheet that grabs data from 10 columns using vlookup.
This data is transferred to the new sheet in individual cells. (see attatched)
what i want to do is create a list from them cells, filtering out duplicates & counting them.
Creating a list like this:
720x560 = x5
452x282 = x3
etc.. etc...
I've tried everything i can think of, but i'm imagining that its a vba job to do what i'm asking.
I'd appreciated it of someone could point me in the right direction.
i need to count the number of used entries in a range.
so fex the range is A1:A10 but there are only 2 entries what i search is the number 2
is there a VBA function or so doing that.
ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..
I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.
I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.
I have this formula that I want to simplify:
=COUNTIF(Table1[t-5],"=NF")+COUNTIF(Table1[t-5],"=NO")+COUNTIF(Table1[t-5],"=NA")
I want the formulat to capture all situations in which either NF, NO and NA are in the range Table1[t-5]
I have got a spreadsheet with a database of people and all their details, Surname (E), Name (F), Gender (L), Date of Birth (M), Age (O), etc...
So I have a demographics sheet that I calculate ages, gender etc and would like to do the following 2 tables.
1. Working out ages between 2 specified ages that I input into set cells. E.g. I want all people between the ages of 13 and 18 years old (So i put 13 into cell A13 and 18 into B13. My formula will then sit in C13 and will Also count those ages. I want to do this so to be able to change the ages if needed
2. Working out Surnames that sit between an alphabetic range. So as I did with ages I can have "A" into A51 and "D" in B51 and it will give me all the surnames that start with those letters in a range between A and D (Including the 2 options I have in those cells).
I've got a database with a date header across the columns C1 to W1 [C1 value = '02/04/07 and W1 value = '30/04/07]
Each row represents a different person
For each person there can be one of skills: Maths, English, French, Science, Geography on each day. Most people keep the same skill for the whole month but some change multiple times within the month.
What I'm after is a formula which will return the total number of people who have had each skill for at least one day within a time specificed time period. The startdate criteria is in A1 and enddate criteria is in B1
E.g How many different people had Maths on at least one day for the period 16th April to the 20th April.
At the moment I've been trying to work along the lines:
=sumproduct((C2:W200>=datevalue(A1))*(c2:w200
Is there a way I can count the cells in a range up until a certain value and use this # as a variable?
View 9 Replies View RelatedI have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies View RelatedI have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
In column A I have a list if places that can contain duplicates ie
Manchester
Birmingham
London
Birmingham
London
Manchester
Manchester
London
In column B through to D a list of statements to which there are multiple answers i.e.
Yes / Maybe / No
What I'd like to know is how many 'Yes' answers are in the data range for column B:D in Manchester
I've used a countifs but have to result to multiple countifs adding each column together which is fine for 3 columns but not when there are 50!
I'm looking for a way to count entries of numbers per day of the week within a date range. Example
Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40
I don't know if that possible in excel or its to much
I have a column of start times which are entered as per 24hr clock and what I am trying to do is to is to sort the times out into ranges;
00:01 to 06:59, 07:00 to 19:00 and 19:01 to 00:00
The range is T7:T488 and here is my first formula for 00:01 to 06:59 which works, it correctly finds 35 entries;
I have a column, we'll say E18:E2500. In the cells in that column are four digit numbers. Some of these cells may have multiple four digit numbers separated by a comma and a space. (example: 2020, 2100, 3120) Some other cells in the column may also share the four digit numbers (I mean duplicates).
So I'm trying to write a formula to sum and count all the unique values in the cells and in the range. This is what i've been trying to use but it counts all the values with no regard to duplicate values:
=SUM(IF(LEN(TRIM($E$18:$E$2500))=0,0,LEN(TRIM($E$18:$E$2500))-LEN(SUBSTITUTE($E$18:$E$2500," ",""))+1))
Is possible write a formula in one cell and count how is certain seasons?
I want instead criteria argument in the function =COUNTIF(range,criteria) set formula.
Instead criteria argument inside Countif function I want to use a formula on this link
Attached file : Lookup formula Date to Season.xlsx‎
I have a range of cells (A1:K20 for example) and I'd like to have Excel count the number of times a certain set of initials (PT for example) showed up in any of the cells within the range.
View 7 Replies View Related
