In the attached spreadsheet I would like to calculate the totals for pipe and valves by service & size (Columns A,B D & D) from the groups of paired columns Pipes & Valves under the headings of SD0-1, SD0-2, SD0-3 etc etc to LP-SD8 multiplied by the number of Service droppers (Highlighted in red) with the totals in columns G & H.
I can individually write a formula such as G7 = (I7*I4)+( K7*K4) etc etc, but is there a way to lookup up the data cells?
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]....
I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical.
Currently I have this thing solved with following formula:
N48=sumproduct(sumif(indirect("'"&$B$378:$B$385&"'!A:A");A48;indirect("'"&$B$378:$B$385&"'!L:L")))
Where B378:B385 includes sheet names.
But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it.
So, in addition of getting values for specific product number I need to get values from specific column based on column header.
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
View 9 Replies View RelatedI've got a spreadsheet with bird species, dates (just day-month) and numbers (how many individuals). It looks a bit like this (well....the real sheet is a lot bigger). Note that some dates appear multiple times, because I counted for many years.
1
A
B
C
D
E
[Code]....
What I want is an overview, to be able to check which birds I've seen on which days of which months (the year doesn't matter) and in which numbers. It's supposed to look like this.
1
A
B
C
D
E
F
[Code]....
It should look for duplicates in row 2, and sum up all the values within these duplicated columns into the first one... It should also remove the columns from which it has summed up the values, except for the first one.
I have columns that contain text (Populated from drop down lists).
I need to be able to sum the totals of each type from the drop down list. These totals can either be displayed on a second sheet.
I have the following problem -I want the cell with "SUM OF SPE HOURS (C1)" to contain the numerical value equal to the SUM of numbers in column C ONLY IF column A of the corresponding row contains "SPE". Basically a SUM function which only picks up the rows corresponding to SPE.
A
B
C
D
E
SUM OF SPE HOURS i.e. 6
SPE
4
LDE
8
SD
10
SPE
2
I'm making a spreadsheet that tracks instances when something goes above or below a threshold. Anytime it goes below, a column populates with the number 1, and if it happens to go below a threshold on a Friday, a separate column populates with the number 2. If its doesnt go below, the column with 1 does not populate, however, every Friday populates with a two.
I need a column that adds the 1 and the 2 when the threshold is broken on a Friday, but only shows the 1 when the threshold is broken on a non friday and shows 0 when the threshold isn't broken, but happens to be a Friday.
I am having an excel problem such that i have several months in a given year and I want to set up a formula where I can enter a specific year and it will return the sum of all the cells that belong to that year (see attached sheet). So basically if I have 2 months in 2006, 2 in 2007 and 2 in 2008 with corresponding values for each of those months, I want to enter a year in another part of the sheet and a value is returned to me to show me the sum of the revenue for all the months that are in that year. So if I enter 2007, it will sum both those values that belong to 2007.
The years and months are dynamic so I can't just sum the columns because in column D it could be 2006 (depending on the other inputs of my spreadsheet) or it could be 2007, so I essentially need some formula that checks the array to see if that cell belongs in a year that matches the criteria and if it does sum it to all the values that match that criteria. I have tried using vlookup, match, index but can't get it to work.
If I have the following: =SUM(E2:F2) - G2). So lets say that E2 has 50, F2 has 60 which = 110. Now I want to take that 110 and subtract it from what is in G2 and put the result in H2.
View 2 Replies View RelatedI need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
I'm looking to do the following below: column A would have numbers, column B would have words and a few columns over i would have the total of each same word
column A column B
60house
120car
30misc
52.25food
83.33gashouse 180.25
120.25housecar 120
25miscmisc 55
105foodfood 157.25
50.5gasgas 133.83
I have a worksheet (downloaded from Paypal) and the columns I need to sum are of varying length (sometimes 12 rows and maybe 200). I am having trouble finding a way to find the end plus one of the column to write the formula in.
My data in excel looks like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5
What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them.
for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
I am working on a sum formula for a column and I only want to sum if the value is a multiple of 15 or likewise if the value divided by 15 is an integer. I know that I could use several sumif statements to accomplish this, but there isn't really a maximum value. Is there a simpler way to accomplish this? I am comfortable enough with VBA so that is definitely an option as well.
View 4 Replies View RelatedI need to sum the items in column G12:G39 with the 1st name in Column B12:B39 which could be 1 of 9 different names, that, and the 2nd name in column D12:D39 cannot not say either #4 or TE. If it says #4 or TE, then it needs to sum the info in colmn G12:G39, based on the 1st name in column B12:B39.
View 9 Replies View RelatedUsing Excel2007 / Vista
Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client
Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious.
But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence.
One week a client may not appear at all, or they may appear from 1 to 6 times.
I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.
1. I created a macro to put in multiple sheets when clicking in pivot table fields
2. I didn't want to include 2 main sheets in the process ("PROV_AGENTEN_SEE_EXTERN_2013073") and ("PIVOT 20130731")
3. In each newly created list I want to put in the sum of the last column. This will be calculated in the last cell below the table.
The last point is troubling because it looks at right values but the sums are not right. They are right only in the last sheet in others it isn't calculated correctly.
VB:
Sub Sheetsfrompivot()
Application.DisplayAlerts = False
For Each Sheet In Application.Worksheets [code].....
I have Mac:2011 version of Excel. Here's what I'm trying to do... I have 5 sheets, 1 for each week of a typical month, and then a 5th sheet in which I want to add cumulative values.
In the first four sheets, column A is the name of a person. Columns B through H are daily performance values. Column I is the weekly sum of columns B-H. I am sorting each of these first 4 sheets by column I, from highest-lowest; so a person's row position changes from sheet 1 through sheet 4, based on how well they did in comparison to other people.
I should also mention that a person may or may not appear in each sheet for weeks 1-4. They might be there in week 1, miss week 2, and be back for week 3 and 4, etc.
Now, in sheet 5, the 'cumulative values sheet', is where I'm having difficulty. Column A is again, where the names of people should go. Columns B, C, D & E are what I want to be the sum values from Weeks 1-4. And finally, Column F is a sum of these columns B-E, yielding a monthly total. Here's the logic of what I want to do...
Part 1:
Look at the name of the person in [Sheet 1:Column A through Sheet 4:Column A]... If a person's name from [Sheet 1:Column A through Sheet 4:Column A] doesn't appear in sheet 5, column A, copy that name to sheet 5, column A. Otherwise, move on to part 2...
Part 2: Look-up the week 1 value of the Sheet 5: Column A name, from sheet 1, and copy the associated sum value of that name (sheet1:column I) to Sheet 5:Column B. Repeat for Sheet/Week 2 value to Sheet 5:Column C... Week 3 to Column D, and Week 4 to Column E...
And finally, sum the values of Sheet 5, Columns B-E to column F. Sort highest-lowest.
No issues with the summing function, but I'm not sure the best way to achieve 'Part 2'. I've been fooling around with VLOOKUP, and have had some success with =VLOOKUP(A2,Week1!A2:I26,9) for column B, =VLOOKUP(A2,Week2!A2:I26,9) for column C, etc... but it's only performing properly for my first row, and then I mostly get #N/A. So what am I missing? Or is there an alternate function that would achieve this in an easier fashion?
We have a table that records payment dates and amounts ... and it categorizes how the payment was made. See attached file to understand the process.
Column A records the payment date. Two or more payments can be recorded for the same date!!!
Column B records the amount of payment.
Column C records the method of payment (OL or LB or CC or CK).
Here's what we need:
On a given date (cell J4), we need to match any dates in Col A ... and we need to SUM all payments that match that date .... and the SUM needs to be based upon the method of payment (OL or LB ...) and values stored respectively in E4,F4,G4,H4 (or left blank if no date match).
I'm going nuts trying to figure out what formula I could use to give me totals in columns Q and R of the attached file. Basically, in the Weekly Totals column, I want to populate how many Customers and Bags were handled according to each ship name (the data is in the table to the left). Any suggestions would be GREATLY appreciated, I'm taking stabs in the dark but am not coming up with anything that works.
View 6 Replies View RelatedColumn A: Start Date
Column B: End Date
Column C: Ethnicity Code (letter and number such as B1)
Column D: Hours
What I need to do is work out how many hours each ethnic group did in a specific month.
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:
1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6
I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
I have 20 sheets set up exactly the same with employee payroll and another sheet that lists all of the unique names and id's. I need to get a total sum of all the sheets per employee. Please note that some employees can appear multiple times on a sheet.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFG1last namefirst namePositionIDGenderDeptamount2JonesJohnX89891MF410$1,830.003JohnsonMikeX89892MD167$2,220.004AllenRayY89893MF410$675.005ThompsonDavidY89894MG43A$300.006JonesAlZ89895MHYJ$195.007PhillipsTomC89896MT567$150.008SmithBenV89897M67L$240.00Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I'm trying to sum a criteria of all M's in one column that are x's in a different column, throughout multiple worksheets.
I'm able to get the summary number for 1 worksheet using the below formula (*W1 is the worksheet name); however, how do i encapsulate all the worksheets (lets say W1 through W10), please note that some of the worksheets have different ranges (meaning, not all are from Row 2 to 6)
=SUMPRODUCT(--(INDIRECT("'W1'!C2:C6")="M"),--(INDIRECT("'W2'!D2:D6")="x"))
I tried to replace W1 with W1:W10.
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies View RelatedI could probably fix by making a loop, but I am trying to avoid doing any more looping. The program that I am working on has way too much looping in it already, and is getting kind of bogged down.
Here is what I need. On my worksheet, column D lists the names of farms, column E lists a specific chicken house on that farm (by number), and column F lists how many chicks are being placed into that house. What I need to do is find a way, within VBA, to add up all of the chicks in column F that correspond to a certain farm name (column D) and chicken house (column E).
I can make something like this work in excel, but not in VBA. For example, if I wanted to find out how many chicks were being delivered to Johnson Farms, house 4, I would use this formula:
= SUM(IF(($D$5:$D$25="JOHNSON FARMS") * ($E$5:$E$25=3),$F$5:$F$25 ))