How I can sum up from raw data under multiple criteria:
Raw Data
Region
Item
Company A
Company B
Company C
North
Orange
1,200
7,894
100
[Code] .....
In the table above I want to sum up as result below, what formula is best for it?
Result
Region
Item
Company A
Company B
Company C
North
Orange
?
?
?
[Code] .........
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx‎
Need in displaying data by user input criteria, mulitple rows of display also need to display to blank out when no criteria is input. In example spreadsheet the criteria cell is B6 in "Results" page and needs to pull in data from the "Database" page.
View 3 Replies View RelatedI am looking to search in a table (say 4 columns) corresponding to multiple criterion (one for every column except fourth) and returning the values which are numerous (from column 4). I have tried the INDEX function but it only gives me one of the many cells. I am working on a table with +20000 cells per column
View 3 Replies View RelatedI would like to put a data validation check on a cell to make sure only numbers are inputted in the cell (As opposed to words). So I was looking to write in 2 data validations: 1 to say =value(A1) and the other to allow a zero to be inputted as well. How can I write these both in?
View 2 Replies View RelatedI have an excel sheet which has 200 fields and 1000 rows, now I want to make summary of them, so I need some fields to be extracted from those 200 fields to another sheet on multiple criteria, Like Category, First Name, Last Name, District, Village, Date and etc.
View 9 Replies View RelatedI have attached a very simple model of a much larger BI report that we use. I have written a DSUM that returns the correct result in all cases other than when one of the criteria columns is blank. When one or more columns is blank, the result returned is 0 whereas I need it return all data (for e.g. if you remove "sains" from cell B2, I need it to still return data for person "b", "c" and "d" (i.e. 51 for Mar14)).
Attached File : DSUM (2).xlsx‎
I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
how to Transfer data between worksheets using multiple criteria?
View 1 Replies View RelatedIn the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):
It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!
create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!
I am trying to sum data based on multiple criteria..
The english version of the formula is Sum all refunds for Store during week
Original Data Format: ....
I am having difficulty trying to consolidate a much larger version of the attached spreadsheet. I need to be able to have excel automatically add up the assets, based upon the firm ID. I have looked around and thought that SUMPRODUCT or SUMIF would work for what I need, but I've been unable to get either to work. I almost need to do a VLOOKUP and have it SUM at the same time.
View 3 Replies View RelatedI am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.
Is there a way to do this? I have pasted below an example of what I need done as reference.
Last Name
First Name
Address
[Code]....
I have two different workbooks. In workbook1 I have a table like below:
A
B
C
[Code]....
What I want to do is to create a drop down menu in workbook2 where I can select a name
and then see below what time that person is working each day of the week.
I have a data table that has the following structure
Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33
I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.
I can use the following index / match formula to extract the salary that matches the job title and location.
(INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1
However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)
I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300
What I would like is a formula that sums up column C if Column B of that row is "Plate" for example
I have multiple worksheets to add employee details using a form and create worksheets (copy templateSheet and paste) for each employee using their uniq user id, which works fine. I have 12 worksheets named by month (Jan, Feb, Mar.... Dec). I have only 2 worksheets Jan and Feb for test.
I want data from employee worksheet's (worksheets created by employee user id) to be in 'Jan, Feb... Dec' worksheets to use a criteria (userid, worksheet name) to get monthly holiday booked data from employee worksheet (userid and worksheets name are same) to month name worksheet. I can do it manually (Example: =rahmanm!C3
) for each row and column, but I will also have a function to delete employee name and details. Is there anyway in VBA I can update worksheets (Jan, Feb, Mar... Dec) from employee worksheets (rahmanm, hallD, aldridh.. ).
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
Attached sample file : example.xls
I have a spreadsheet with 12 tabs (one for each month of the year). What I need is a macro/function that on execution will pull all rows from each sheet that has the word "overdue" in cell E from E9 down. I need the whole row of data being taken into a new sheet.
So for example, in each sheet there could be the word overdue appearing in 30 out of 500 rows I need those complete rows (A to Y) being put into another sheet for ease. At the moment I am filtering each sheet and copying and pasting into a new sheet for each bloody sheet (LOOOONG way).
The worksheets are titled: Jan 14, Feb 14, March 14, April 14, June 14, July 14, Aug 14, Sept 14, Oct 14, Nov 14, Dec 14
I have data on a worksheet (“Interface”) that I need to copy over 7 other tabs for analysis.
("Module_1", "Module_2", "Module_3", "Module_4", "Module_5", "Module_6", "Module_7").
The linking Field on the Interface tab defines what row I need to paste the data to on Module 1-7. The column headers are the same on both the interface and other tabs (“YES, NO, N/A, BLANKS, STANDARDS MET, STANDARDS NOT MET, NOT APPLICABLE”)
The tab is selected based on the row
Module 1Medication Management
Module 2Privacy
Module 3Process
Module 4Patient Safety
Module 5Medical Records
Module 6Infection Control
Module 7Environment of Care
I’m trying to modify code written for a similar purpose with no luck. This is the code I'm trying to modify. I know I'm not on the right track yet.
I have a few people all entering in data into excel, since they cannot be all in one sheet they each have their own copy. I would like to be able to extract all data where column P reads "Red" from each users spreadsheet and then delete the corresponding lines from their spreadsheet (end of day activity)
The Sheets will be named
FDEntryU1AM.xlsm
FDEntryU2AM.xlsm
FDEntryU3AM.xlsm
FDEntryU4AM.xlsm
[Code] .......
I will then need to do the same in another workbook for all items Labeled Blue, but that of course will be same code with just an item replaced.
The idea is after they close up the person overseeing the data can import all at once and leave the users with fresh empty workbooks.
I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
i am looking some formula to make sequence number with criteria;
1) if in cell B2 contains only single/one data ----the result is mark "-"
2) if in cell B2 contains several data (not single)----the result is auto numbering with adding mark "." (dot) and start from 1.,2.,3.,4,etc........
for the detail,
condition 1 (multiple data in col.B)
number
name
1.
john
2.
mike
3.
sisca
etc...
condition 2 (single data in col.B)
number
name
Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.
elevation
type
grade
percent
weight
5000
5000
5000
5020
[Code] ..........
Spreadsheet contains 15 worksheets, all named differently, "apple", "pear" "plum" etc etc. Worksheet has data columns A to E including in column E a "Next Review Date".
On worksheet 16, called "Crumble" .... I want to click a macro that will cut and paste in all the data from the 15 fruit worksheets where the date is
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.
Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?
Sub PopulateReport()
Application.ScreenUpdating = False
Dim MyFilter1 As String
Dim MyFilter2 As String
Dim MyFilter3 As String
MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string
MyFilter2 = CStr(Sheets("Report").Range("C4").Value)
MyFilter3 = CStr(Sheets("Report").Range("C6").Value)
Sheets("Waste").Select
Dim Rw As Long
Dim Rng As Range
Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A1:W" & Rw)
With Rng
.AutoFilter
.AutoFilter Field:=20, Criteria1:=MyFilter1
.AutoFilter Field:=2, Criteria1:=MyFilter2
.AutoFilter Field:=13, Criteria1:=MyFilter3
End With
how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)