is it possible to use the sum product formula to give me the total results for multiple values?
I know I can ask it to look at column a for a value of e.g "44" and column B a value of "gin2334" but can I ask it to ge me total for values from column A that equal "44, 45, 46, 47" ans B "gin2334"?
if not has any got a clue as to how i would get these results?
Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?
=SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P11111"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P22222"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P33333"))
Data below, I would need a formula for the sum total of the volume multipled by the price for each product category.
Category
Product
Price
Vol1
Vol2
HW
Prod 1
12
2
5
[Code] ..........
I have a sumproduct formula that does a count of my table column based on 2 conditions. However, when I filter my data it still present the same results. So ive been told to use a Subtotal formula. I have scoured the net for the right formula and have come up with the following... though it returns data - it returns the wrong results.
Anything wrong with the below subttotal - sumproduct formula? for us in filtered data sets?
Code:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(TasksTable[HIGH LEVEL],ROW(TasksTable[HIGH LEVEL])
-ROW(Tasks!$K$20),0,1)),--(TasksTable[BD]=999),--(TasksTable[HIGH LEVEL]=GR15))
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.
I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).
Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.
Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:
(LEFT($D$2:$D$100,LEN($J9))=$J9)
Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.
Attached file. [URL] ........
On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is:
1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix.
2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet.
3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table.
Currently this will be put into a macro and assigned to a button.
The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it.
I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx‎
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
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].....
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
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.
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
I’ve got a folder on drive C named “Customer Sheets”. In this folder at any time there could be over 500 individual customer sheets. i also have a "Summary Workbook" on drive C
What I need is a macro in my "Summary Workbook" that copies cells (“B3, B6, B12, B13, G5, G6”) form every customer sheet in my “Customers Sheets” folder and paste it into my “Summary Workbook” onto sheet2, every customer in a new row.
The “Summary Workbook” should run this macro every time it is opened or on my command so that the database is continually refreshed when a new customer sheet is made.
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
{=INDEX(Jan!$B:$B,SMALL(IF(Jan!$A:$A=Smith!$A$2,ROW(Jan!$A:$A)),ROW(1:1)))}
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
Index across multiple sheets returning multiple values
I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")
But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?
that data would be returned in order of the date of the pay.
file: Test formula on payroll_v3.xlsx
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.
View 2 Replies View RelatedI am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)
A
B
C
D
E
F
G
H
I
J
K
L
1
name
score
color
[Code] ..........
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.
I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.
When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.
In in Excel/VBA, I have been making OWC charts on a form. My aim is to chart categories (x axis) against values (y axis), showing several values in each category as markers. In addition, I have been calculating the mean for all values in each category, and plotting this as a line across the categories. I first set up the X axis at the charts level:
Set oChart = chtspcXIC.Charts.Add
With oChart
.HasLegend = True
.HasTitle = True
.Title.Caption = "Used fractions per timepoint"
.Type = chChartTypeLineMarkers
.SetData chDimCategories, chDataLiteral, XICtmpts
End With
Next, for the individual points, I made two corresponding arrays, one of categories and the other of values and attempted to send these to a new a seriescollection:
Set oSeries = oChart.SeriesCollection.Add
With oSeries..............
I have cells with multiple values in A1. [Alt-enter]. What's the easiest way to get these values to split to B2, C2, D2, etc?
Number of characters range, number of "Alt-enters" range.
Example Data:
653576051617433
653376051616332
653276051617014
653176051617654
653176051617111
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
