Sum Multiple Criteria In Multiple Columns And Rows
Jul 10, 2013create a formula,for multiple criteria with multiple results in rows.
[URL]
ADVERTISEMENT
Copy Rows Meeting Multiple Criteria On Multiple Columns
Dec 18, 20061. 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
Averaging Multiple Columns / Rows If Two Criteria Are Met
Apr 30, 2014I 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
Sumproduct For Multiple Criteria Across Columns And Rows
Jan 10, 2014I'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‎
Sum Range: Multiple Criteria In Rows & Columns
Oct 20, 2006I 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.......................
Transpose Multiple Columns To Rows Based On Criteria
Sep 26, 2008I 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.
Delete Rows Where 2 Columns Meet Multiple Criteria
Aug 30, 2007I 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"
Delete Rows Where Multiple Columns Meet Criteria
Apr 4, 2008I 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.
Return Multiple Values Across Columns And Rows Based On Criteria
Jun 20, 2008I 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
Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
Sep 22, 2006I 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))))
Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?
Feb 24, 2014Im 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.
Multiply Multiple Columns Based On Multiple Criteria
Jan 23, 2012So, 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
Multiple Ccolums/rows To Get Data From Multiple Columns/rows (vlookup)
Jan 15, 2010I 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 RelatedCount Rows Meeting Multiple Criteria Of Multiple Values
Aug 10, 2009I 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.
Delete Rows Where Multiple Column Meets Multiple Criteria
Sep 29, 2011Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
INDEX With Multiple Criteria And Returning Multiple Rows But Only The Two Most Recent
Jun 10, 2013I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.
I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.
I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.
[URL]
Multiple Criteria Sumifs In Multiple Columns
Mar 4, 2014I am trying to improve how I write my formula. In this case I have replaced this horribly long formula:
=SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")
+SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")
[Code] ....
with this nice shortened version:
{=SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},
TransactionCode,{3006;3035;3031},ChargeOffCredits,{"OK"},CAAPartner,A10,ChargeOffCredits,"OK"))}
I have pieced this together using information on various forums and it appears to work, but I have a question:
What is the difference between using a comma and semi-colon in this new formula?
VBA Code To Copy Selected Multiple Columns To Multiple Rows In Excel
Mar 13, 2014I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
Deleting Multiple Rows With Multiple Criteria
Feb 6, 2007I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.
Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................
Delete Multiple Rows :: Match In Multiple Columns
Oct 29, 2008I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
Sum Multiple Columns Using Multiple Criteria?
Jan 25, 2012I'm looking to create a formula that I'm not sure is possible. In my spreadsheet template I have company departments in Column C4:C500 and projects in rows F3:AZ3. The actual proj ID in F3:AZ3 will vary by spreadsheet, but I have a separate Master sheet that has all projects and all departments listed in a pivot table-like format. Since there are far more proj IDs than departments, in this master list the depts are all in the same row and proj IDs in the same column. Due to the input, I cannot change the template setup.
I will be pulling many spreadsheets together and want to calculate the total where the company dept and proj matchup.
The SUMIFS statement I am attempting is =SUMIFS(C5:AZ500,"Project",cell w/ project,"Dept",cell with dept) - but the issue is you cannot do a sumif using multiple columns.
Multiple IF Criteria Across Multiple Columns?
Apr 1, 2013I am trying to create a formula that will populate a cell with text depending on an entry in 7 other columns. Only one of the seven columns will have an "Yes" entry. This is what I have so far that is not working:
=IF(C:C="Yes","Injury",IF(D:D="Yes","Illness",IF(E:E="Yes","Property Damage",IF(F:F="Yes","Motor Vehicle",IF(G:G="Yes","First Aid",IF(H:H="Yes","Near Miss",IF(I:I="Yes","Other")))))))
So if there is a "yes" in column C, I want "Injury" to be displayed in my destination cell; if column C is blank but there is a "yes" in column E, then display "Property Damage" and so on. I am completely flummoxed.
Change Multiple Columns Into Multiple Rows
Oct 10, 2008I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.
At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.
It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
Multiple Columns To Multiple Rows
Jan 14, 2010I have a spreadsheet that has ONE row for each day of the year and FOUR columns for each day. Some days use 1, 2, 3 or 4 columns. I need to turn these into rows for each Column.
Here is an example of the detail data: ....
AverageIfs Multiple Criteria In Columns
Feb 5, 2014So I have 3 columns in this example. I want to average column K if certain criteria are met in Columns G & I.
Columns G & I have text values and Column K is a number value.
Does that make sense?
If I'm simply counting how many meet both criteria, I have this: =COUNTIFS(Data!I2:I282,"Waiting",Data!G2:G282,"Sev1")
but I want to now take those and average the values for Column K.
Count Multiple Columns And Criteria
Mar 13, 2014What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.
I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria.
If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5.
When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.
Company
Option 1
Option 2
a
True
True
b
True
False
c
True
False
a
True
False
a
True
False
b
True
True
Return Max Value Considering Criteria In Multiple Columns
Jan 10, 2014I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER
VBA Delete Row If Criteria From Multiple Columns Are Met
Aug 19, 2014I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
Delete Multiple Columns If They Fit Different Criteria
Jun 17, 2009Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)
Then delete these columns.
How To Hide Multiple Columns Via VBA With Criteria
Jul 1, 2013I have a data ranging from J:EW with a heading in row 2 e.g. J2 the heading is "OT1 Hrs"; K2 the heading is "OT1Amount"; L2 the heading is "OT2 Hrs" and so forth. My difficulty is, it is too much multiple columns from J to EW, how can I draft the vba to hide a column that has "Hrs" in row 2 heading instead of doing it one by one.
View 3 Replies View Related