Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
Sep 22, 2006
I have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
View 9 Replies
ADVERTISEMENT
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
Aug 21, 2013
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name
Start
End
Other data
Verizon
1/10/2010
1/25/2010
AT&T
3/2/2010
4/14/2010
View 1 Replies
View Related
Apr 30, 2014
I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.
I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).
Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.
Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.
The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?
Location
Parameter
2008
2009
2010
2011
2012
2013
LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33
LocA
beta
4
9
9
8
2
9
LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65
LocB
beta
8
7
6
7
2
9
LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92
View 3 Replies
View Related
Jan 10, 2014
I've not used SUMPRODUCT previously and can't understand how to get results for the attached.
I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.
I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.
The second table is actually in a different sheet but is the source of the data I need added.
Sumproduct P&L.xlsx
View 11 Replies
View Related
Oct 20, 2006
I posted this on Mr Excel, but did not get an answer. I hope someone here can help.
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.......................
View 9 Replies
View Related
Jul 10, 2013
create a formula,for multiple criteria with multiple results in rows.
[URL]
View 1 Replies
View Related
Dec 8, 2006
I have a sheet with "Yes", "No', and "N/A' in most of the Columns. (E - Q) and about 100 rows of data.
I only want to count the number of rows that have "No" in column E and Yes in all the others. If "no" occurs in any of the other columns in that row (D-Q) I dont want it to be part of the count.
View 9 Replies
View Related
Sep 26, 2008
I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:
Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00
I want it to look like the following:
Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00
I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.
View 11 Replies
View Related
Aug 30, 2007
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
View 8 Replies
View Related
Apr 4, 2008
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
View 3 Replies
View Related
Jun 20, 2008
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
EG
Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50
View 9 Replies
View Related
Jan 15, 2010
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies
View Related
Jan 23, 2009
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies
View Related
Aug 13, 2012
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
View 3 Replies
View Related
Mar 6, 2013
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]....
View 4 Replies
View Related
Feb 18, 2014
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
View 5 Replies
View Related
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
Apr 9, 2014
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
View 2 Replies
View Related
Jul 27, 2013
I am working on a project to automate the import of data and fixing the formatting.
The data contains the date in column A and time in column B. The time is in increments of 15 min for each day. The rest of the data is in columns C:F. Some of the dates don't have all of the times within the day (so instead of 96 rows of data per day, there maybe 80 rows). I have a second sheet of just dates and times that has the correct amount of rows for each day. What I need to do is have the rows data that matches in both the date and time column from sheet 1 copied over to the corresponding date and time on sheet 2. Blank data rows on sheet 2 are fine. I have found some stuff on .Find, but the stuff I found was for specific searches.
Example
Sheet 1
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 01:00 AM 9 10 11 12
Sheet 2
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 12:30 AM
7/27/13 12:45 AM
7/27/13 01:00 AM 9 10 11 12
View 1 Replies
View Related
Mar 10, 2009
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 Related
Feb 11, 2014
Original
01012014 DAVE JOHN
02012014 MAT
03012014 CHRIS MIKE PHIL
What I want
01012014 DAVE
01012014 JOHN
02012014 MAT
03012014 CHRIS
03012014 MIKE
03012014 PHIL
View 4 Replies
View Related
Jul 21, 2013
How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
View 14 Replies
View Related
Apr 8, 2013
I have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)
For example: "1100789/3200899/6xlm-sgt-455-0987"
The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)
I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.
for example:
1 100789
3 200899
6 xlm-sgt-455-0987
Is this possible with the data tools in a spreadsheet, or will I need to write a macro?
View 9 Replies
View Related
Jun 19, 2014
I need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.
View 4 Replies
View Related
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Mar 1, 2014
I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.
Sub City2()
'
' City2 Macro
'
'
Selection.Copy
Cells.Replace What:="POMPANO", Replacement:="Pompano Beach",
[Code].......
View 12 Replies
View Related
Jan 23, 2012
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
View 3 Replies
View Related
Aug 10, 2009
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.
View 2 Replies
View Related
