Conditional Summation Of Like Entries In A Table

Feb 15, 2009

I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.

I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.

Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??

View 2 Replies


ADVERTISEMENT

Conditional Format Table Row If Entries Missing

Feb 26, 2008

Please find the attached sheet.

I just want to know whether is it possible to condional format multiple cells based on multiple cell values.

Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).

View 8 Replies View Related

Conditional Summation

Dec 22, 2008

How can I summate cells in a column *except* for those rows which contain a specific value in another column?

=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.

View 3 Replies View Related

Conditional Summation Based On Drop-Down Choice

Dec 2, 2009

I have attached a spreadsheet that I need to automate. Cell C3 is a drop down box of the raw data found on the raw data sheet. I would like B6-8, c6-8 and d6-8 to automatically updated and change depending on what users select from the drop box in C3. (so basically im trying to re create the yellow table with formulae/ vb.)

View 4 Replies View Related

Making A Summation Table

Jan 11, 2009

I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...

What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data

View 2 Replies View Related

Conditional Summation Formula: Set SUM Code To Grab All Numbers Next To Codes

May 6, 2008

I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).

View 2 Replies View Related

Conditional Entries

Jul 18, 2007

I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.

I'd like to evaluate these last 8 entries using a couple of different conditions.

#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...

a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)

#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and 0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)

View 9 Replies View Related

Conditional Date & Time Stamp Entries

Oct 23, 2007

I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).

I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date
Target.offset(0, -1).numberformat = "yyyy-mm-dd"
End If
End Sub

View 6 Replies View Related

Apply Conditional Formats To Cell Based On Entries In Another?

Jan 27, 2012

apply conditional formats to a cell, based on the entries in another, different cell?

View 2 Replies View Related

Sum Selected Entries From A Table

Oct 19, 2009

I have a dividend table of 8 cells (vertical) with alternate Date and Dividend amount. Is there an elegant function to add the dividend up to a given date?

i.e. if 23 Nov 09 is the 2nd dividend and I need dividends to this date,

View 7 Replies View Related

Display Last X Entries In Table

Dec 3, 2006

I need have a table that displays only the last so-many (250) elements of a large data collection (table), updated continuously.

View 6 Replies View Related

How To Convert Table Entries Into 1 Column

Jan 23, 2014

I have a table with Companies in a column and Countries in the heading row. Now in this table I have an entry "1" against certain countries for each company.

I need to get a concatenated (Company&Country) list, but I need this is all in a single column.

Companies list.xlsx

View 2 Replies View Related

Summarizing Selected Entries In A Table?

Apr 26, 2013

I wold like to summarize values of a certain numbers of enties (eg. 5), on a specific criteria, but discarging earlier entries of the same criteria. An example being:

Entry
Value
Entry A
1

[Code]....

If I would like to summarize the values from Entry A, but firstly do so from number three entry buttom-up, and only the prior five values, how could this be done?

View 9 Replies View Related

Automatically Number Table Entries

Oct 13, 2007

How do I automatically generate an Entry Number for each entry in a table?

I have a sheet to build up a database of information and a Userform for the data to be input through. Each time the user selects to create a new entry with the userform I'd like to be able to automatically provide a number (i.e. Entry 1 of 12) in the Entry Number field which will then be transfered back to the sheet.

In addition, if an entry is deleted from the sheet the entry numbers beneath the deleted entry will need to update.

View 3 Replies View Related

Synchronize 2 Table Entries Across 2 Worksheets

Apr 25, 2008

Range B9:B58 and I9:I58 data set 1 (worksheet Deal Selection)

Range J5:J18 and L5:L18 data set 2 (worksheet Tables)

Identical tables. Taking worksheet Deal Selection as our starting point column(s) B and I

If the user adds a new supply customer in column B then I would like this new customer to be

Reflected in worksheet Tables column J5 at the end.

And visa versa for column I add to column L5 at the end same work sheet.

I am guess that this could be done by amending the current macro button

Add Supplier and Add Demand on both side(s). but not removing it current functions

I have also added the question to another forum experts exchange as i am have difficult completing this task.

View 5 Replies View Related

Pivot Table / Chart Not Grouping Same Entries

Feb 4, 2010

I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:

When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?

View 4 Replies View Related

Clearing Pivot Table Drop Down Entries

Mar 18, 2007

I was wondering if there is a simple way of deleting past entries from a drop down list for a pivot table. I have tested my spreadsheet and now want to create a 'blank canvas' for users.

View 3 Replies View Related

Pivot Table - Stop It Summing Date Entries

Dec 5, 2013

How do I stop a pivot table adding data together?

e.g.

1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200

I want this but instead get this:

1st Nov - 200
2nd Nov - 400

I need the pivot to split it out,

View 1 Replies View Related

Excel 2010 :: Total Entries Between Two Dates In Table

Mar 17, 2014

I'm an Excel newbie using 2010, but have been given a rather daunting task. I have a lot of data organised in a table, something such as:

Column A: Name, Column B: City, Column C: Date Entered

I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.

I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.

So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.

View 3 Replies View Related

Count Of Unique Entries As The Subtotal Of A Pivot Table

Jan 28, 2010

I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?

View 2 Replies View Related

Create A Second Table Of Data Counting How Many Entries There Are For Each Month In Each Company

Mar 24, 2009

I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear

View 3 Replies View Related

Excel 2013 :: Count Unique Entries In A Table That Match A Criteria?

Jul 11, 2013

2013

Current Year

Q1-13
Q2-13
Q3-13
Q4-13

Totals

Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00

[code].....

I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.

View 5 Replies View Related

Relative VBA Summation

May 19, 2009

I have written an SQL Stored proceedure which drags this information into excel.

All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.

What I basically have is this ....

View 7 Replies View Related

Searching Summation

Jun 26, 2007

I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....

View 10 Replies View Related

Summation Of Any Numbers Come To ###

Dec 17, 2008

I try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?

View 15 Replies View Related

Summation With Limit

May 25, 2009

how to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.

View 9 Replies View Related

Revenue Summation By Account Name

Aug 15, 2013

Attached is my sample data...Indicative Data_Revenue 2.xlsx

My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.

The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage

Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.

View 9 Replies View Related

Shortening Summation Formula?

Jan 10, 2014

I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.

Untitled.png

The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.

I am currently using:

AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)

As you can see this formula is far too long so I am looking to write it in compressed formula.

View 4 Replies View Related

Cells Summation From More Than One Sheets

Oct 8, 2008

I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.

Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.

View 4 Replies View Related

Random Summation Of Numbers

Jul 13, 2009

I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved