Counting Multiple Options
Dec 6, 2009
Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;
I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.
View 6 Replies
ADVERTISEMENT
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Apr 23, 2014
I am looking to design a Userform with "OptionButtons" in which the user selects 1 of 4 print formats. I was going to have one single button that stimulates the userform mentioned which allows the user to pick from 4 options. Then there will be a commandbutton1 on userform which allows them to "Print Preview" the selected print style and a commandbutton2 on the userform which allows them to "Print" with the chosen style. The following are the 4 Print Formats:
Print1:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Rows 1-17 are Normal
From row18 on every row with text in columnA becomes a print break with row16 as a header before it. (the point of this is that row16 is a header but since it will always on the first page and row17 is a text row that would kick it on the next page)
Print2:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break
Print3:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide ColumnE and ColumnF after row15.
Print4:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide Column A, B, E, F after row15.
View 1 Replies
View Related
Apr 5, 2007
I have a Case Select statement I just learned how to use yesterday (kind of) What I am wondering is can I have multiple arguements or conditions to each case? For example: my sheet is a "Job Cost Estimate" for construction sales people. The section I am working on is "Fasteners"
I have 2 drop down boxes per line. The first box selects the fastener type, (Screw, Nail, Auger) the next box selects the size in inches and half inches (16 different sizes). Currently the select statement is set up for screws and says if this size then this price. I want to say if this type of fastener and this size then this price.
Additionally, when I select a size in my drop down box, I have to click somewhere else to get it to calculate. Is there a way to select the option and it auto calculates?
Here is my statement in my Worksheet selection change section:
Select Case Range("AZ21").Value ....
View 9 Replies
View Related
Aug 1, 2013
I am a bit of a novice with excel. I have created my own sales tracker where I get two forms of bonus.
Sheet 1 I have with all my sales. Based on the amount of sales I do I get a set bonus for each amount.
Sheet 2 I have for all the sales that progress.
They also are on a value basis- for every sale I get a certain amount of bonus. I have 2 cells calculating the amount of points. I was wondering if there was a way to have the cells calculate from the bonus table what i would get without me adding it up manually.
Sheet1 is booked leads.H3 calculates the total amount of points.
Sheet 2 is the paid occurences. F2 of that sheet is total points.
Sheet 3 is the bonus structure.
I am looking to put all the information in sheet 1:
Booked Bonus
Occurred Bonus
Total Bonus
Bonus structure is as follows:
Booked Payout Table
Occurred Payout Table
Net Points
Total Bonus
Net Points
Total Bonus
16
GBP 250
[Code] .....
View 4 Replies
View Related
May 14, 2012
correct below code?
Code:
Private Sub WorkSheet_Change(ByVal target As Range)
Dim rngdv As Range
Dim oldval As String
Dim newval As String
If target.Count > 1 Then GoTo exithandler
On Error Resume Next
Set rngdv = Cells.spcialcells(xlCellTypeAllValidation)
[code].....
i would like to select multiple options from dropdown... but it is not working...
View 4 Replies
View Related
Feb 13, 2013
I have a macro to find me a document from a certain details, but in some circumstances their may be multiple applicable documents, I know nothing of user forms, but how to I find all the documents, show their "modified date", "Name","File type" and select one or which several to open.
My current code:
Code:
Code:
Private Sub OpenPDF()
Dim Ans
Dim TheFile As String
[Code]....
To have something more like a form popup showing for a search within a folder for files containing 'Brians Store':
Open? Name Date
O "Quote Brians Store - ProductsA.xlsm" 1/2/12
O "Quote Brians Store - ProductsB.xlsm" 1/1/12
O "Quote Brians Store - ProductsA.xlsm" 1/5/11
O "Quote Brians Store - ProductsC.xlsm" 1/4/11
And I may want to open the 1st, 2nd and last files.
View 4 Replies
View Related
Apr 2, 2007
Is it possible to update Chart Titles in Batches. For the following Titles:
'Client 1, Jan 07'
'Client 2, Jan 07'
'Client 3, Jan 07'
'Client 4, Jan 07',
Would you be able to change 'Jan 07' to Jan 08' on all of the above whilst keeping the variable client name, in a single batch process?
View 3 Replies
View Related
Aug 3, 2012
I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.
View 9 Replies
View Related
Oct 1, 2013
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
View 2 Replies
View Related
Apr 23, 2013
i am attaching a sheet here.
i have put data validation list in three columns
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
Sr No.
Unit Type
Unit Type
Code
H
W
D
Qty
1
Base Unit
Double Hinged Door
B60
720
600
580
2
2
Base Unit
Single Hinged Door
B30R
720
300
580
4
View 2 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Jan 5, 2009
i am using excel 2007. can you use multiple options within a conditional format without having multiple conditional formatting. i know that you can use "or" within a function string, is it possible within the conditional formatting
View 6 Replies
View Related
Oct 7, 2009
I have common tasks where I need to sort through large amounts of data to receive a total number of line entries matching multiple criteria in different columns. I've attached a Workbook with a simplified example. On STATS!B1 I would like to total, from DATA!, all lines which have "1A" in A:A and any of the following in E:E (RP, SAO, AE, RSNR).
View 4 Replies
View Related
Nov 10, 2008
My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.
I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.
I have attached the sheet in pk zip format.
View 8 Replies
View Related
Jan 23, 2013
I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.
View 2 Replies
View Related
Jan 12, 2010
I am trying to count the total number of oil rigs which have revenue in column H (postive or negative revenue, just not zero), revenue in column L, and have the owner name "Transocean" in column B. I'm trying to use this SUMPRODUCT formula but it's not working.
=SUMPRODUCT(--('Golden Source'!H2:H6280),--('Golden Source'!L2:L6280),--('Golden Source'!B2:B628="Transocean"))
View 9 Replies
View Related
Nov 5, 2009
I am looking to get a formula that count the number of rows that match some
criteria. The sample table is listed below. I would to have a formula
calculate the # of rows in which values in column B (>=3 and <=3.9) along with values in
Column C = 0
IDRATINGAMOUNT(formula match)
003044.0 (5.0)
002992.5 0 x
000692.5 2.0
013804.0 0 x
017293.0 0 x
017832.5 0 x
019342.0 (1.0)
019013.8 (1.0)x
014823.0 (1.0)x
020762.5 (1.0)
COUNT346
B>=3, B<=3.9 = 3 rows
C=0 = 4 rows
Overall = 6 total rows
But if you combine both criteria, there are 6 rows that match. I'm having a
difficult time figuring our how to write the formula for this.
View 2 Replies
View Related
Jan 22, 2008
i have 4 worksheets with various names. i have an 5th worksheet with a named list (TabNames) of the 4 worksheet names
each of the 4 worksheets have cells with percentages in them.
i would like to check a cell from each sheet and see if the percentage is equal to or above a certain %, for example anything equal to or above 90%. then return the number of cells that meets or exceeds that 90% criteria.
so for example
sheet1 sheet2 sheet3 sheet4
A
1 80% 91% 76% 82%
2 80% 95% 92% 43%
so checking cell A1 in list TabNames would return 1, and checking cellA2 would return 2.
View 14 Replies
View Related
Jan 23, 2010
Column A contains "Power","Instr","EHT", Column AJ contains "25M32", "25M35","25M39"and the a date is in column AE. I need to count how many time the date appears in AE if column the cell in A is "Power" and the cell in AJ is "25M32".
View 2 Replies
View Related
Nov 7, 2008
I am working on a spreadsheet that contains multiple text conditions that I need to keep count of. I would like to try the countif but I don't know if this will work. Here's a summary of my spreadsheet
Column F in my spreadsheet contains 7 different regions (all text).
Column D contains an open or closed option that is not validated.
In my totals section of the spread sheet I need to count each region that meets the "closed" criteria.
View 3 Replies
View Related
Dec 2, 2008
I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.
Right now, I'm using the following:
=SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)
View 3 Replies
View Related
May 2, 2009
I have a spreadsheet with data from a survey. There are 375 respondents (rows) and 26 questions (columns). I use COUNTIF in order to figure out the frequency of responses and SUMPRODUCT to do some finer analysis. Here's my problem, some of the questions have multiple valid responses. In those cases the data was entered into the same cell. So I have a column that looks like this:
1,4
2
1
3,12
1,11
8,
3,14,9
I can't figure out how to count the number of "1"s. It would be much easier if the responses could only be 1-9, but unfortunately they can be 1-20. So far, I've come up with:
View 3 Replies
View Related
Nov 23, 2011
=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))
The works when referencing two columns. I need a total count of unique across three columns.
Sample Data
A | B |C
Jason | Temp | 1
Jason | Temp | 1
Jason | Final | 1
Kim | Temp | 1
Mary | Temp | 2
My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.
Temp | Final
1 | 2 | 1
2 | 1 | 0
View 5 Replies
View Related
Mar 11, 2013
I am trying to calculate nonzeros across multiple sheets, rows, columns, etc.
I understand the countif(range,>0) however it will not calculate my range when I try and separate the cells with commas.
What is the best way to count?
View 4 Replies
View Related
Jan 9, 2008
what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
View 9 Replies
View Related
Sep 8, 2006
In column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:
1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C
So any formulars can do that in instead of using the sorting method?
View 3 Replies
View Related
Jun 6, 2007
I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.
View 9 Replies
View Related
Aug 14, 2008
I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.
xxx Open
xxx Complete
yyy Complete
yyy Open
xxx Complete
xxx Open
I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.
View 9 Replies
View Related
