I have a sheet that contains rows containing letter codes for example "CU". This is generated by an IF formula i.e. If true = "CU". However I need to be able to calculate the number of instances of "CU" but sumproduct will not work as it sees the cell as containing a formula not the result i.e. "CU". Sumproduct code:
View 3 RepliesI I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
I would like to count the number of instances of different schedules shows in one department. For example for dept. 1, I would like to know how many different schedules are in that dept. I have 48 schedules and 60 departments (numbered 1-60). I would like to know how many "different schedules" dept 1 has and so on. I don't think the frequency function will work. Is there a way I can use sumproduct to get the result I'm looking for? The information is on a separate worksheet in colums (Col. A holds the dept and Col. B holds the relative schedule).
View 9 Replies View RelatedI have a following Data in One of the Column. I want to count the number of instances.
Ex. There are 3 instances for 120412 , 2 instances for 120471 , 1 instance of 120478 and so on. How to write a formulsa for this?
Configuration: Test ID
120412
120412
120412
120471
120471
120478
128715
128722
128723
128724
128725
129291
129329
8984
8984
I'm working on a formula for COUNTIF with 3 criteria.
What I'm looking to do is create a formula that counts the number of instances which meet all of the following three criteria:
the value of Column A is >=0 and the value of Column I is >=0 and
I need a formula to count the number of times the same thing occurs in a particular column. i.e. tree 1, tree 2, tree 3. I just tried the countif function and it is returning "0". Not sure if that's because the data I am looking for is part number/part text. Does this make a difference? There are also spaces between the number and the text. Also, the range I am asking excel to look in is based on a concatenate formula.
View 4 Replies View RelatedI found the following formula online while searching for how to count unique instances of a number in a list. The list may have blank cells or repeated numbers but this formula works. =SUMPRODUCT((A1:100<>"")/COUNTIF(A1:A100,A1:A100&""))
So my question is...can someone verbally explain just exactly what is going on? i.e. I don't understand the <>"" in the sumproduct argument or the &"" in the second argument of the countif.
If you can see the example spreadsheet that has been uploaded, I need VBA to try and count how many times the number 1, 2 or 3 occurs in the column A and give that figure in b15, 16 and 17 respectively. I also need it count the number of times a particular product appears in column B and give those figures in b19, 20 and 21.
View 7 Replies View RelatedI'm working on a spreadsheet that contains a list of various instances of zip codes between a couple of months. I'd like to count the number of times each particular zip code occurs within each month. And, if possible, if I could get that count to populate into another cell on another sheet within the spreadsheet,
View 7 Replies View RelatedI have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies View RelatedI like to think that I am pretty good on Excel 2003, can't stand 2007 but hey thats for another thread.
I can do most forumulas and write some pretty elaborate macros, but one thing I am trying to do has completely stumped me.
Each month I compile a Pivit table that shows by day if a product was in stock or out of stock, this is represented by a blank cell (in stock), "1" Out of stock.
All this is fine, but I want to be able to show the number of instances that a product was out of stock in any given month...
This might be really simple but i don't get it. I have a column with country names (strings). There would be 5 instances of "USA", 10 of "UK", etc, etc.
I made a column next to it, where i want to count the number occurances ....
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv()
lastRow = Range("A1").End(xlDown).Row
pmpt = InputBox(Prompt:="What text are you looking for?", _
Title:="Text", Default:="i.e.: Finished Goods Inventory")
numrows = InputBox(Prompt:="How many rows to delete (counting original):", _
Title:="Number of Rows", Default:="i.e.: 1")
Set cell = Range("A1:A" & lastRow). Find(pmpt)
If Not cell Is Nothing Then
For l = cell.Row To cell.Row + numrows
Range("A" & l).EntireRow.delete
Next l
End If
Do
I want to say thanks for any help you might be able to offer. Attached are 2 sheets out of a workbook I am using which has many sumproduct formulas. On the the Input sheet, is where vacation and training forecast dates are listed. On the Calcs sheet, there are 2 monthly calendars for the year, 1 for vacation, the other for training. Would like to know if there is a way to have both records show up on only one calendar set. I currently have the vacation shown as a '1' and the training shown as a '9'. The workbook is used in Excel 2003 & 2007.
View 5 Replies View RelatedI am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.
I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.
I am attaching a sample file for your reference.
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............
I have had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.
Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.
The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.
I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business). I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump. What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
View 3 Replies View RelatedI was kindly provided with a formula from this forum a few months ago (in the attached workbook) to calculate the area under the curve of strings of data of varying length (i.e. different number of rows) these strings of data are aligned with a timeline (providing the Y and X axis for the curve respectively). The formula is able to calculate the area under these curves without the need for adjustment to the number of rows of the dataset.
However I have now tried to copy this formula to a new datasheet with a different total number of rows and a timeline fixed (of different intervals) in column A (rather than moving for each string of data) and I am receiving a #N/A error. I am sure that I have copied the formula correctly. Can anyone help fix my formula so that it calculates area under the curve again? I would like it to calculate the area under the curve for the data in the same column but using the fixed timeline in column A.
The working formula is on the sheet ‘original formula layout’ and the formula containing the error is on the sheet ‘new formula layout’.
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.
I have attached a small copy of the worksheet.
I need to count when the following set of 3 criteria criteria are met:
1: The Assessor Initials = DS
2: the month = Mar
3: Check 4 Ranges = columns I, K, M, O
I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.
I need to expand my SUMPRODUCT to calculate a numerical value. I have set the formula to calculate how many medical leave, training and annual leave taken by my staff. Now, I need to expand the formula to calculate how many items that was completed on region1,region2 and region 3 as well as working hours. I tried adjusting the formula but it did not work. I have attached the simplified version of the file. The actual is as long as more than 10000 entries.
View 5 Replies View RelatedAll I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 Replies View RelatedI'm using WinXP with Excel 2003 - I have a column of highway sign description data (16k+ rows).
Example:
Curve Arrow Right
Curve Arrow Left
Turn Arrow
Reversing Curve Arrow Right
Winding Road Arrow(plus many more unique entries)
I'm using SUM and COUNTIF to total the number of times "Curve", "Reversing", "Turn" and "Winding" appear in the column.
My formula is:
Code: =SUM(COUNTIF($F11:$F16196,{"*CURVE*","*REVERSING*","*TURN*","*WINDING*"}))
Which works great EXCEPT what I really want is the number of cells with any of those key words, not the total count of those words. The example above should be 5, but since row four contains more that 1 of the key words I'm getting 6.
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.
At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.
EXAMPLE:
TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00
Employees - 4
SUM - £4,237.86
Median - £1,033.93............................
To start, have 1 sheet with approx 25 columns of data and i need one column to check 3 others + another sheet to complete its fields.
In column M i need to enter some prices from another worksheet (tag name 'prices') but these prices depend on whats completed in the fields in columns L, T & V.
Column L has fields completed with eg. Houses, Offices, Warehouses
Column T has some fields completed with eg. B1, D, E (these are just zonal refs)
Column V has some fields completed with numbers 1,2,3
Prices worksheet has 4 columns
A = Property (same heading and fields completed as Column L on first worksheet)
B = Standard Price (this has prices in fields that correspond to the properties in A above)
C = Cresta Price (same description as above)
D = Wind Price (same description as above)
I need column M (blank fields) to look at L (heading 'property') then add in Standard Price from B (worksheet prices) then check to see if any fields are completed in Columns T & V and if they are then it needs to addon the prices from either columns C or D from Prices worksheet.
I am a little lost on this, and have tried to remedy my issue by searches to no avail. I have a cell formula that references another cell:
Cell A1 (=A3) where Cell A3 = 1
Cell A1 displays the text "=A3" and not the value of 1. Autocalc is on, and the worsheet and workbook is unprotected.
I am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.
please follow this link [URL] .....
Part A
2/12
3/12
4/12
5/12
6/12
plan
0
0
50
50
0
[Code] .....
I am thinking that I must have something set up wrong some place, but have not done anything that should have changed my formulas
Its not that they are gone but just are not auto calculating.
I tried just a basic sample invoice and the figures do not calculate without manual calclation