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.
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 RelatedI 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".
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]....
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies View RelatedI 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.
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.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies View RelatedI have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.
I've looked at COUNTIF/S, SUMIF but just cant get there.
The below sample is an accurate example of a much larger set of data. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.
A
B
C
D
E
F
G
H
1
Item#
Description
Quantity
Month
Year
2
1001
[Code] ........
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
VBA code to summarise a column based on 2 other columns.
All flagged codes must be summed and all non flagged codes summed.
code.....amount.....flag.......summerised code..sum amount...flag
aaa100...yaaa200...ybbb100...ybbb200...yaaa100...yccc100...ybbb100...yaaa300ccc100...ybbb300
aaa100ccc200aaa100bbb100bbb100ccc100ccc100aaa100bbb100
I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:
untitled.jpg
I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.
Non-VBA preferred
Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.
test book 1.xlsx
I would like to title columns based on values from other cells...
View 9 Replies View RelatedI am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.
First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.
In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.
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.
I have a sheet with 2 columns. First Column lists sizes of Devices. Second colum lists a servername. Example:
A B
30 server_a
30 server_a
65 server_a
65 server_a
45 server_b
45 server_b
15 server_b
What I want to do is a device count, not device sum on a criteria based on Column B.
For instance whenever column B equals servername A, then count how many devices it has.
This would equal 4 instead of 190 which is the sum of the devices. I can do a sumif statement, very easily, but I don't want a sum, I just want a count.
There is probably a simple way of doing this in excel but the only way that i can think of doing it is through VBA.
There are about 25 columns and 4000 rows in my spreadsheet. I want my macro to look up 5 criteria in Sheet 1 and the same 5 criteria in the previous years spreadsheet. If all 5 values in Sheet 1 match all 5 values in Sheet 2, i want it to import a cell's value from that row in Sheet 2 to the active row in Sheet 1.
Here is the code I have so far:
Code:
Sub Import2012Numbers()
Dim BlockNbr As String
Dim Position As String
Dim Scenario As String
Dim LOA As String
Dim ScenVal
Dim x As Integer
[Code] ...
I think i've got everything down in terms of the variable definitions etc, but i think the problem is coming from the order or method of the for each and looping process.
I need the macro to take A2, S2, U2, W2, and V2 in Sheet 1 and look through all the rows in Sheet 2 to see if any of the rows match all 5 criteria. Only then do i want it to move on to A3, S3, U3, W3 AND V3 in Sheet 1
When i step through the code i keep getting the error message "Next without for" for i, or "For command already in use".
I've worked on this one way to long and thought I'd ask here now.
Column A Column B
A 1
A 0
A 0
B Blank Cell
B 0
B Blank Cell
B 0
B 0
I have a third cell that I would like to count how many zeros in Column B there are that correspond to the letter B in Column A. Blank cells cannot be counted. This answer should be 3.
A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria are met?
I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.
I am looking for a formula as per the thread title. I have attached an example workbook.
I have been playing around with SUMIFS and SUMPRODUCT but can't crack it.
sum based on criteria transpose columns to rows.xlsx
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies View RelatedI need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.
View 1 Replies View RelatedSimple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.