Sum Cells In A Column Based On Multiple Criteria For Neighboring Columns
Jul 1, 2013
Example data:
01/05/2013
150
Yes
01/06/2013
1
Yes
[code].....
Basically I'm trying to write a formula that will sum the total of the numbers from column B but only where the month equals June AND column 3 says "Yes".
So the answer would be 101, because rows 2 and 5 are the only rows of that table that meet all the criteria, and the sum of their column B cells would be 101.
I thought I was getting there with Sumproduct and Sumif but I'm now drawing a blank. There is a little extra complexity in that I'm actually pulling the month value from a set of cells elsewhere, but if someone can solve the puzzle above I hope I can do the rest on my own as it's just locations of data to pull from other worksheets really.
View 9 Replies
ADVERTISEMENT
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
Jan 2, 2013
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
View 11 Replies
View Related
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Dec 30, 2008
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
View 9 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
Nov 7, 2012
I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)
A
B
C
D
E
F
G
H
I
1
Descriptor
Group
Identifier
Inventory
Total Vaue
Invetory Type
Group
Total Incomplete Inventory Value
[Code] .......
Here is what I would like in:
The sum of the total value of half finished and unfinished inventory for each group.
So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)
I think an array formula would do this, but I'm not really sure how this would work.
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
Mar 8, 2014
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
View 1 Replies
View Related
Sep 27, 2011
Rep Name is Column J Row 5. I need to find multiple columns based by the header which is row 5, if column range (j5:az5) is "Video - Actual" then copy column to sheet "Data", this needs to be repeated for about 8 more times for different criteria
Rep NameBundle - TargetBundle - ActualVideo - TargetVideo - ActualJoe, Jane
2.06.06.04.0Smith, John
7.010.02.06.0Tucker, Chris
19.039.05.017.0Sandler, Adam
15.011.06.010.0Iglesias, Gabriel
8.03.06.010.5Smith, Will
8.03.06.06.5Powers, Austin
10.025.07.09.0
View 1 Replies
View Related
Sep 27, 2012
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
View 4 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
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
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 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Feb 14, 2013
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
View 1 Replies
View Related
Aug 10, 2009
This is driving me nuts. What I have is:
Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text
Sheet 2
ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values
What I want is in Column F (Sheet1) a formula to:
if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True
View 9 Replies
View Related
Feb 28, 2014
I have a spreadsheet of several thousand named items (in column B) with values associated with them in column A). The "name" field is a string of several alternative names for the item.
I have a list of ~50 items that I am trying to find the values for. Each uses one of the alternate names.
What I want is a function that will return the associated value in column A when one of my shortlist names is found.
For example, my big spreadsheet looks like:
AB
1dallas, DFW. 12345
2Atlanta, ATL, 23456
3Boston, BOS, 34567
4Chicago, CHI, 45678
5Los Angles, LA, LAX, 56789
And my list looks like
Atlanta
CHI
I want the output to look something like:
Atlanta2
CHI 4
Is the feasible? IT seems like there should be a way to do it....
View 2 Replies
View Related
Sep 25, 2013
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
View 1 Replies
View Related
Dec 2, 2006
Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
View 5 Replies
View Related
Mar 10, 2014
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies
View Related
Aug 17, 2006
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
View 8 Replies
View Related
May 15, 2014
I am trying to create a list of all instances where contents in A3 is found in C5:C12 and return the values in D5:D12 without any spaces. Right now I can do it in two steps but I'd like to clean it up and do it with only one formula.
View 7 Replies
View Related
Nov 29, 2013
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
Sample Workbook.xlsx
View 12 Replies
View Related
Jul 21, 2009
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.
Is there a nested COUNTIFS or similar function that would work?
View 9 Replies
View Related
Apr 30, 2014
I am really struggling to summarise weekly budget data into period budget data by subcategory.
I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.
View 4 Replies
View Related
Mar 11, 2014
I have a block of numbers and I need any number which is 6 units greater or less than its neighbours in the same column to show up red or highlighted. I have attempted some conditional formatting using formulas, but I think the basics of how to use this function have escaped me.
View 9 Replies
View Related
Nov 7, 2013
What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?
Also i need this to work on all atbs as the commandbutton will on a master tab
VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False
[Code]....
View 4 Replies
View Related
Mar 10, 2014
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies
View Related
Dec 6, 2013
I have two columns, column Q and R. Column Q has Data Validation set, where you can only choose one of four options in any given cell between rows 2-250,000. When "Complete" is chosen, the cell turns green. Now when "Complete" is chosen in column Q, I need the cell next to it in Column R to auto-fill today's date. However, the date should not update to the next day. So if the cell autofills to today's date, which is 12/6/13, tomorrow, when the date changes to 12/7/13, the cell still reads 12/6/13.
View 4 Replies
View Related
