Totaling Up The Values From Specified Range Of Dates?

Apr 11, 2014

see the attached workbook. The "data" sheet is the raw data for individual sales orders. Column A has the SKU for the item, and there are also columns for quantity sold and the date sold.

Column A of Sheet 1 is a list of each individual SKU (no duplicates). What I'd like to do is search through the data sheet and total up the quantity of each SKU sold for several individual months. There is also a column that would display totals for the past 30 days (with "today" as day 30).

View 3 Replies


ADVERTISEMENT

Excel 2010 :: Using VBA For Sub-Totaling Returning Unexpected Values

May 8, 2014

This relates to this thread - [URL] .....

This is the only macro in this file

[Code] .....

It will perform the sub-totalling for the column that has the current active cell

When I select Column I

It does.....
-for each blue cell it finds it provides a total of all the white cells bellow it
-for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell

It works backwards, so not exactly as I've just described, but that isn't the problem

Problem is -
UK Excel 2010 - results are as expected
US Excel 2010 - returns zero values for totals

We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then

How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.

Attached File : Example-1p.xlsm

View 6 Replies View Related

Adding Values Between Two Dates And Dates Are Also Derived By Formula?

Dec 9, 2013

I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.

The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column

These dates also have formula by which there are derived

-------------------------Current Ageing-------------------------
Date Range

Bucket
Amount
Percent
Start Date
End Date[code].....

I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.

View 9 Replies View Related

Formula: Select A Range Of Dates Within A Range Of Dates?

Jul 22, 2009

In column B is a list of dates, which are broken out by fiscal year (FY=7/1/ to 6/30). Column C is a list of dollar amounts. Cell F3 is the query ‘start date’
Cell F4 is today's date, which will be the formula: =today()

Using a date range of F3:F4, I'm trying to get each cell bordered in red to query the dates in column B for each FY; then display the matching sum from column C.

For illustrative purposes I went and manually determined what the values should be displaying as of 7/22. Here are a couple of examples of how I am trying to get the formula to work.

Example 1
FY’08:
Start date- 7/1
End date- 7/22
Searching cells B1:B94, cells B1:B13 fall within the start and end date parameters listed above. Sum of cells C1:C13 is $45,112.00, which should display in cell F6.

Example 2
FY’09:
Start date- 7/1
End date- 12/13
Searching cells B95:B222, cells B95:B149 fall within the start and end date parameters listed above. Sum of cells C95:C149 is $150,873.03, which F7 should display in cell F7. How to I type up this formula; its way more challenging that I first thought!

View 5 Replies View Related

Pick Range Of Dates And Find Number Of Days Without Sales Between Those Dates

Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

View 3 Replies View Related

Counting Dates In A Range :: Count Weekend Dates

Feb 4, 2010

In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)

I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100

Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?

View 9 Replies View Related

Huge Table Of Dates, Find Range Of Dates

Aug 16, 2006

I have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.

Dates To Test 14/08/2026 19/08/2026

Results

14/08/2026

15/08/2026

16/08/2026

17/08/2026

18/08/2026

19/08/2026

Table..............

i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D

View 3 Replies View Related

Auto Totaling

Mar 4, 2009

I have a column data on a worksheet, the PO number for specific purchases where a PO number may be listed multiple times. One a separate worksheet within the same workbook, I have a PO summary section where PO numbers are manually entered and the next column sums the the total value of each PO.

Is there a way I could have the Summary section identify POs and automatically total them without having them manually entered?

View 7 Replies View Related

Totaling Various Cells

Apr 15, 2009

Picture a bank statement (several in fact) in Excel format. In addition to all the typical bank statement fields, like date, description, amount, etc., there is also a type field, e.g., food, gas, etc. I am trying to total all amounts of each type, e.g., gas total = $200.00, in one cell automatically. So far, I can't figure out a formula to do this nor a macro.

View 7 Replies View Related

Totaling Sub Totals

Apr 20, 2009

Let me first explain what my worksheet looks like.

View 2 Replies View Related

Using Lookup, Then Totaling The Row

May 27, 2009

I'm trying to use Lookup to find a particular name, then when finding that name I need to total what's in that row (numbers). The Lookup is successful, but I can't get any totals out of it. Tried using Offset to begin summing once I got to the row I was Looking for but that wasn't doing it (or doing it wrong)

View 4 Replies View Related

Totaling Columns

May 29, 2007

I am tracking sales commissions on a daily basis for each month. In column B are the daily dollar totals. In column C is the % to the quota that has thus far been reached. Column D has the average daily dollar amount needed to reach your quota based on cumulative daily revenue. It is this column that has me stumped.

Once the % to Quota (column C) reaches 100%, I want column D to stop calculating the average daily dollar amount needed.

The quota is $322,786 (C2)

Column B (Daily Totals) = $100,000
Column C (% to Quota) = 31%
Column D (Daily Avg. Needed for quota) = C$2/work days left in month)

View 8 Replies View Related

Totaling Textboxes

Jan 23, 2009

I have about 21 textboxes that are going to be watched by 1 textbox called totaltime on a userform.
I created the code below to add up those 21 textboxes, but it's not showing anything even though they are populated, some with zeros and some with one through eight.....

View 6 Replies View Related

Macros For Totaling The Rows?

Dec 4, 2012

I have few columns, which I need to sum them up at the bottom.

Now the problem is the number of rows will not be same.

the macros code for it?

View 9 Replies View Related

Totaling Numbers By Names

Sep 8, 2013

[URL]

The output of my magic spreadsheet, developed to run the local flower show, is a column of names - each of which have won a prize valued in points. I need to determine which person in the list of names has won the most points in total, in order to award a trophy.

Actually, I also need to know how many points each individual has got because - as we all know - points make prizes. And I need to write out one prize voucher for each individual, to the total value of what they won.

Now I have been sorting each column alphabetically, then manually totting up the points, and adding the three totals together. But surely, there must be a way to do this automatically? I don't really want to have to sort the columns at all... just have the answers appear like magic once the data is in the data entry part of the sheet.

Here's an example:

1ST PRIZE
NAME
VALUE
2ND PRIZE

[Code].....

View 9 Replies View Related

Totaling With Drop-downs

Dec 28, 2006

I have a sheet that has drop-downs with totals affiliated with each option. This is a time recording sheet and I would like for each drop-down type to be totaled accordingly. Any help with an equation would be greatly appreciated.

If you have any suggestions, I can send you the document for review.

View 10 Replies View Related

Totaling 2 Textboxes In A Form

Jan 7, 2009

I have a userform with 3 textboxes. The user will put numbers into 2 of them and the 3rd will add the other 2 textboxes together. My problem is that the result is just stringing the values together and not adding them together. So if textbox1 is 2 and textbox2 is 4 the result is 24 and not 6. Can someone tell me where im going wrong?

View 5 Replies View Related

Totaling Columns To The Right Until Certain Points

Mar 14, 2013

I have 3 Columns... I would like the column2 row1 total all the values of column3 using stopping when there is a another value in column1...

Column1
Column2
Column3
MasterItem1
SumOfMasterItemMinions(1,2,3,4)
Minion1
Minion2
Minion3
Minion4
MasterItem2
SumOfMasterItemMinions(5,6,7)
Minion5
Minion6
Minion7

But i don't know how many Minions there could be as they're selectable...

View 3 Replies View Related

Totaling 2 Columns In Spreadsheet

Aug 21, 2013

Any way to total 2 columns in a spreadsheet.

I need a formula that will sum the cells in the the first column below but if there's a value in the 2nd column I'll need to use that number instead. Is there a formula/combination of formulas that will accomplish this?

$91,170

$68,372

$89,015

$61,954

$767,379

$773,712

$247,266

$0

$94,370

$0

Total
$1,245,674

View 2 Replies View Related

Totaling Column Information

May 19, 2009

What I want to see is, is it possible to total up the number of times a specific phrase appears in a column using a formula?

I have a column of information generated from an outside source. The information in each row of the column is either "TRUE" or "FASLE".

Say I have 200 rows, I want to count the total number of times "TRUE" appears in that column out of the 200 rows.

View 9 Replies View Related

Totaling Years In Cells

May 20, 2009

In Column B I have various dates i.e [01/02/2008]. I Need a formula that will count the number of times the year 2008 appears in column B.

View 6 Replies View Related

Sum Totaling Of A Single Cell?

Dec 15, 2005

Is there any way that one would be able to sum total a single cell?

I have my numbers listed in column A & would like the return in column B .....

View 9 Replies View Related

Totaling, Summing & Formating

Oct 27, 2006

I have a spread sheet that I am trying to format the data from. It contains five columns, 2 of which are important for this formating.

-Column A is Account name, and there can be a varying number of accounts with the same name, as well as a varying number of different account names (they are sorted so all similar accounts are together)

-Column E is an indicator/used for a calculation, either 0 or 1.

I need to do two things ....

View 8 Replies View Related

Totaling Subsets Of Data Within One Workbook

Jan 20, 2014

I have a spreadsheet containing our company's current clients and their monthly spend per unit. It is all on one spreadsheet and I want to calculate the total unit cost per month per company.

I have the columns: Company, Unit Description, Unit $ Amount.

There is a row for every individual unit each company owns. I wish to extract the total unit $ amount per customer.

View 1 Replies View Related

Self Totaling Columns With Names In Each Cell

Mar 4, 2009

I have Excel 2007 and have a spreadsheet with 26 columns that I continue to add names to each of the columns. Is there a way that the columns can maintain an ongoing total for each column and update these totals as names are added to each column? Small sample is attached.

View 6 Replies View Related

Totaling Quantities With Dropdown List

Dec 27, 2012

Below I have attached a sheet for keeping track of the number of cables a person produces per day using Drop-Down list. I would like to have a totals area automatically calculate the corresponding cells. I started the Totals area in column AF.

View 13 Replies View Related

Totaling A Column On Each Printed Page

Jul 18, 2007

Is there a way I can display the total of a column on each printed page?

I have a table which is about 6 columns long but is hundreds of rows down. I would like the total of one of these colums to be displayed on every printed page. Is this possible?

View 6 Replies View Related

Bank Transactions - Grouping / Totaling Like Titles

Jun 1, 2014

I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount

1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set.
Total each set.

2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"

3) formula to find all postage totals and combine for a grand total per category.

usps15.23postage
usps14.32postage
usps5.23postage
fedex5.25postage
fedex10.22postage
shell45.28fuel
shell99.38fuel
qt27.38fuel
qt44.88fuel

View 7 Replies View Related

Totaling Numeric Amounts Based On Text In Another Column

Jul 26, 2007

I would like to total numeric amounts based text in another column.

For example, I have a bunch of rows in "column F" with the same text (company name) and in "column I" i have numerics. Is there a way to total "column I" based on the same text of "column F"?

View 7 Replies View Related

Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range

Oct 10, 2009

I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.

View 9 Replies View Related







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