Summing Multiple Rows Using Logic?
Feb 3, 2009
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
ADVERTISEMENT
May 9, 2008
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.
View 9 Replies
View Related
Mar 27, 2014
I tried to make attached worksheet self-explanatory. find attached.
Basically, I want to number group of rows based on a logic (blank A,B, and C columns)
inincubus.grouprows.xlsx
View 3 Replies
View Related
Jan 15, 2009
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
View 6 Replies
View Related
Aug 1, 2014
Here is my attachment with data in first sheet and expected output in second sheet. Have given comments in second sheet for better understanding.
Data is of a debt collection, in sheet column F "form #" denotes loan form numbers and column L "paid" denotes the amount received or not received.
In this Form # are unique and form number will repeat with different paid amount.
Entire row to be colored based on the sum value of paid column amount of respective form number.
1. non repeated single form number with negative value of paid - Orange color
2. repeated form numbers with sum of values in paid column as positive value - only entire row of form number with negative value in blue color
3. repeated form numbers with sum of values in paid column as negative value - first form number row to be colored with yellow and rest of repeated form numbers with purple color and its sum value (negative value should be made available in yellow line of the Form # group to the right of right most column data)
4. form numbers and paid column of value "0" - pink color
I tried recording and edited macro only for the logic of paid value "0" and confused with other logics and declaring variables...
View 9 Replies
View Related
Feb 6, 2009
I've got a worksheet with five columns of data. The headings run from a1:e1
The number of rows can be anything up to 200,000 rows (I'm using Excel 2007).....
View 6 Replies
View Related
Jan 16, 2009
I'm working with Excel 2003 on a peice for work that requires users to enter their current grade and, if they have one, a temporary grade. Another sheet in the workbook needs to add together all those in a certain grade and who answered a certain way to a drop-down table, so that I can use it to figure out what percentage of staff are of each grade. At the moment it looks like this:
=SUMPRODUCT(--(Work_Data!F2:F9827="Grade 1"),--(RIGHT(Work_Data!K2:K9827,3)="Top 25%"))
Which only draws information from the current grade (column F) and the answer they give (Column K). What I can't come up with is a formula that will only take column F as long as column G hasn't got a temporary grade, but if it does have a temporary grade will take column G instead.
View 4 Replies
View Related
Jan 17, 2014
I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.
My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:
View 8 Replies
View Related
May 1, 2006
how to exclude a blanck cell from logic?
I have tried what you see below but neither works as desired.
If Not vData(n, 9) = 0 Then
If Not vData(n, 9)Is null Then
have corrected code error
View 9 Replies
View Related
Apr 23, 2012
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
View 1 Replies
View Related
May 14, 2014
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.
View 2 Replies
View Related
Apr 22, 2012
ABCDE122334658677482910211126139145154161718
Trying to get the sum in the highlighted rows of the numbers in the column between the highlighted rows.
View 9 Replies
View Related
May 29, 2008
I have a chart with multiple bundles of rows....
Variable $500
Fixed $400
Step $500
Total $1400
Variable $500
Fixed $400
Step $500
Total $1400
Variable $500
Fixed $400
Step $500
Total $1400
Total Variable $________
Total Fixed $________
Total Step $________
Is there a formula that can help me sum up a total value for the Variable amounts? IE. Variable, Fixed, Step in column A; Dollar amounts in column B
Is there a formula that can recognize the word "Variable" in column A and then pull the number from the adjacent cell in column b?
Want to avoid having to Ctrl+click the hundreds of cells that I would have to in order to do a simple calc =sum(b1, b4, b7, b10) etc. if you know what i mean.
View 9 Replies
View Related
Jan 29, 2010
CCTRCCTRCCTRCCTR112#112NB114#114NWSales group112112114114MONTHQTRYTDNielsen ID#NB##JAN109Q10920091/2/20092894.1717.161231.644704.9JAN109Q10920091/5/20093188.9544.22766.313039.35JAN110Q11020101/6/20092867.92945.571890.774520.44JAN110Q11020101/7/20091927.89210.58484.94960.61RESULTSTABLE 1TOTALS11211410961459742110595211857RESULTSTABLE 2TOTALS112#112NB114#114NW109608361199877441104796115623769481
Table 1 and Table 2 are examples of the results that I need - I have a formula that works for table 2, but when I need to consolidate the numbers as in Table 1 - it won't work - it only views the first 112 or 114 and doesn't sum them together.
the formula that I'm using for table 2 is:
=SUMIF(REV!$B$5:$B$574,"="&$A$50,INDEX(REV!$E$5:$DD$574,0,MATCH($C15,REV!$E$4:$DD$4,0)))
how do I adjust this formula to sum all 112's and 114's in row 3? it sums the columns good, but I need that extra component to make this project work.
View 16 Replies
View Related
Jan 20, 2014
I want to write a macro that would delete the rows if the sum equal to zero and the description is identical. For example:
Column a: Column b:
100 Apple
200. Apple
300. Grape
-100. Apple
-200. Apple
400. Cherry
-400. Cherry
The answer should be 300 grape.
View 2 Replies
View Related
Sep 14, 2009
I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.
Tried to do it myself but succeded to do my head in only.
View 9 Replies
View Related
Apr 21, 2006
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
View 9 Replies
View Related
Jan 8, 2014
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?
View 7 Replies
View Related
Dec 17, 2008
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?
View 2 Replies
View Related
Mar 26, 2013
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 Related
May 19, 2008
I 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 Related
Apr 17, 2009
Using 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.
View 9 Replies
View Related
Feb 2, 2009
I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.
Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.
View 6 Replies
View Related
Aug 6, 2012
I have a sheet whereby in column A i have company names and column B how much is invested in this company so:
A B
AAPL 10
GOOG 5
MSFT 15
APPL 5
IBM 20
GOOG 10
What i would like to do via macro is take all the duplicates sum them so that i see AAPL as 15 but only be left with one row. So we would be left with only 4 rows each with their summed weight.
View 9 Replies
View Related
Aug 20, 2013
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].....
View 1 Replies
View Related
Jul 7, 2014
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?
View 5 Replies
View Related
Feb 8, 2014
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).
View 4 Replies
View Related
Aug 27, 2009
Column 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.
View 14 Replies
View Related
Feb 11, 2010
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.
View 14 Replies
View Related
Apr 24, 2008
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.
View 9 Replies
View Related