Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Need To Sum Only Certain Numbers With A Date Restriction

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have notes in the attached example file explaining what my problem is. Essentially, I need to have a lookup (I think) that sums up everything in a table from the current day through the end of the week, ignoring the numbers that came before it.


Similar Excel Video Tutorials

Helpful Excel Macros

Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format

Similar Topics







I have attached an example file similar to the file I am working with, just with sensitive data removed. Each week has multiple columns within it. I need to set a formula that automatically looks at each week before the current week (based on weeknum()-1 to convert from the standard week in Excel to actual ISO week) and sums only the quantities in a specific cell within each week, and I need to set a formula that automatically looks at each week following the current week (and including the current week) that sums only the quantities in a specific cell within those weeks.


I have a spreadsheet which I use to enter notes related to particular invoice numbers. A v-lookup pulls the notes into another tab. What I'm running into is that the notes can be quite lengthy - in excess of 1,000 characters. When the notes get too long, they don't all appear in a single cell (unless it's extremely wide).

I'm wondering if there may be a way to maintain my notes in a word doc instead? Maybe in a table that pulls in using a v-lookup? But I'm not able to figure out how to direct a v-lookup to a word doc.

Any suggestions?

Thanks in advance.


Hi all,

I have a table which has

Row 1 = Months (mmm)
Row 3 = week numbers
Row 4 = A formula that states "current week" when it is applicabl

below this is all my data.

My Requirements.

I need to add 2 columns after week 52 for the following:-
1 - Month to date
2 - year to date

For the current year this is easy because any weeks after the current week will be blank therefore a simple sumif works for the month to date and a simple sum works for YTD, however for the prior year when all 52 weeks are populated it is proving more difficult.

1 - Is there a simple way to lookup the current week in row 4 and sum up all of the weeks in the month prior or equal to the current week.
2 - For the year to date I obviously require the formula to add up all weeks equal to and prior to the current week.

All help appreciated.

Regards,
Stu


I need to assign a calendar week number (1-52) to an array of order data. I have order data arrayed in rows on a data tab. Each row pertains to a single order. The date that order was placed is one of the cells in the rows. I have a separate lookup table that arrays the week numbers vertically. Arrayed in the two columns to the right of the week numbers are the Start Dates (the Sunday dates that begin the week) and the End Dates (the Saturday dates that end the week). I am familiar with the use of the VLOOKUP function, but do not know how to lookup a value (week #) between two numbers (Start Date and End Date), and return the associated week number.


Hi. I am trying to figure out a formula to compare numbers from the current week against numbers from the previous week. Here is an example of what I need:

Last week
M - 5
T - 4
W - 5
R - 2
F - 3

This week
M - 7
T - 2
W - 4

I want a formula for a cumulative comparison for a sum of the numbers this week to date against the same days from the previous week. So, if I ran the report today, it would show that we are down ~7% from last week. I started with a SUM and tried to nest IF and ISBLANK, but am having no luck.

Any help is much appreciated. Thanks!


Hi,

I need to have a lookup table of past ten years, with week numbers in next column. the problem is how do i arrange the table dynamically deciding on whether it has 52 weeks or 53 weeks?

have attached the worksheet, have also given an example how the usage of table is.

Kindly help me out




Thanks & Regards,
Shruthi


Hi All -

I seem to have a very weird problem. I am various workbooks that I update every few months. Each time I update, I essentially use the same tabs in each workbook, just with different numbers and have to insert new tabs every now and then.

Recently, I have been having issues with one tab in particular. To make explaining easier, lets call this tab "Current."

Whenever I insert a new worksheet and try to link "Current" to a number in the new worksheet, Excel gives me an error and crashes. However, if I pick any other tab and try to link it to the new worksheet, OR try to link "Current" to another existing tab, its fine.

This problem happens no matter what workbook I am in and only with the specific tab. Every "Current" tab in every workbook cannot seem to link to newly inserted worksheets. The only thing in common that the "Current" tab has in each workbook is essentially the formatting.

If anyone has any suggestions, Id greatly appreciate it!!


Hello,

I am trying to create a table of numbers for an economics journal article, i.e. something like

Table 1: Table of Numbers
Row 1 2.3 3.4
Row 2 3.4 4.5
Notes: This is a table of numbers which may be important for many
different things. I received this table of numbers from Jones et al. (2007).

For the "Notes" entry beneath the table, I merge together several different cells, and then type the text in. The problem is that oftentimes the text in "Notes" will simply show up as a bunch of number signs (#) in Excel. I am currently using Excel for Mac 2008, but the problem also popped up previously on my previous PC computer (I just swithced over to Mac about 3 weeks ago) on which I ran either Excel 2003 or Excel for XP (I can't remember which one offhand).

I've tried a number of different things including deleting the cell and repasting the Notes text from a Word file into a new cell; unmerging the cells and pasting in; and typing in the text again by hand. Only the last option, typing in again by hand seems sometimes to do any good. It seems that if too much text is entered at once, then Excel is likely to display the text as a bunch of number signs.

Does anyone have any suggestions on how I might be able to avoid this problem and type in text beneath the table?

Thanks a lot.


friends,
i have problem, need help. cause i m not to strong with excel. (version 2003)
i have 2 excel files, one full of sorted data, product numbers, its descriptions, week its coming, and qty.
and one file with empty table.

first file:
in column A components numbers
in column B its description
in cells K4, N4, Q4, T4, W4 - there are week numbers.
in columns L, O, R, U, X - how many components will come.
and some other data around that i not needed yet.

so in other file id like to create table with next conditions:
if i'll write component number in K28, so formula will check data file and fill :
in F28 week number when components are coming.
in H28 how many components.
in L28 its description.
and so on.

but it should work with closed data file as well.

and the second part, i dont like this ugly warning window popping up that asking you to update some links etc, id like to have silent file update, in case of error, just skip it quiet.
is it possible??

thank you for your help, i really need it.



Hi

I have a list in F10:51 which contains the series 1 down to 42. These are week numbers. In Z7 I have the value of the current week number. The week numbers are not in line with a normal calender but set to my specification.

What I want is a dynamic range which starts from week 1 and expands down to the current week eg. 8.

I have similiar dynamic range but is based on dates withthe range expanding to the current date.

Would they be similar in principle?


Hi Everyone,

I have attached a basic example of what I am trying to do. I am trying to create a report that when the month changes it looks up the relevant month column and sums certain numbers, when I do a vlookup it only returns a certain number. I could do a sumif for each month on a different table and then do the vlookup on that table, however in the actual version there is a lot of data and that would take ages! If I could get a formula to do it automatically that would be brilliant!

Any help much appreciated.

Thanks,
James

I am looking to do some calculations (such as average, min, max, etc) on every other row of a column of numbers in a in a worksheet that I plan to add rows to in the future.

In my case I have a sheet with week-to-date numbers followed by year-to-date numbers, then followed by another week-to-date number, then year-to-date and so on. I want to determine the average, etc of say the week-to-date numbers.

When I just had the week-to-date numbers in a table alone I used the AVERAGE and MIN and MAX function formula to calculate the results nicely. But obviously this won't work with extra data every other row.

Is there a way to still use these functions somehow and ignore every other row? Or some other way considering that the number of rows will be changing as the years go by, and I don't want to have to update the formula as I add data? Thanks


Hi,

I have the table as attached.

The dates in row 3 represents the start date for the particular week of the month. I would like only the current week in row 4 to be highlighted based on the current date.

Example: If the current date is Nov 14 2010, then only cell C4 should be highlighted.


Is there a formula I could use in conditional formatting ?

I am using Excel 2007.

I have a cross post he

http://www.mrexcel.com/forum/showthread.php?t=508924


I am trying to use lookup function for 6 digit numbers but it is not returning the correct value. As you can see in the attached sheet it works fine for small numbers but give incorrect values for large numbers. Why?


I need help with (I believe) some sort of lookup. I have one table that shows Team numbers for column headings, with the Week as the row labels. Below it, I have Lane numbers for column headings, and again Weeks for the row labels.

I need formulas to populate the bottom table so I don't have to enter everything manually... for example, cell C9 should look at the top table and return that team 1 is on lane 3 in week 1. Since the top table is not sorted, I haven't been able to find a lookup that works properly. I've played with COLUMN and the various LOOKUPs, but nothing has worked.

Can anyone help me out? Thanks in advance.

******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E F G H I J 1 * * Team 2 * * 1 2 3 4 5 6 7 8 3 Week 1 3 4 5 6 7 8 9 10 4 2 10 9 4 8 3 5 7 6 5 3 9 5 10 7 6 4 8 3 6 4 7 6 3 10 4 9 5 8 7 * * Lane 8 * * 3 4 5 6 7 8 9 10 9 Week 1 1 2 3 4 5 6 7 8 10 2 5 3 6 8 7 4 2 1 11 3 8 6 2 5 4 7 1 3 12 4 3 5 7 2 1 8 6 4 Schedule *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hi,

can anyone help me with the problem i am facing when sorting numbers in order. i attached the excel file explaining the problem in a simple way.

please help.

- kalaiarasan


Having trouble getting my hands around this problem.

I receive sales numbers from stores every week, I have built a workbook that
totals all the weekly numbers from each file on one sheet.

I would like to have a worksheet for Month, Qtr, YTD, I would like these
numbers to accumulate from the weekly #'s I bring in each week. My problem is
I don't want to keep the weekly numbers every week, I'd like to zero them
each week and bring in the new (adding to the totals on the other worksheets)

Is this possible, well I'm sure it is, but how is it possible.

Thanks for your help.



Hi guys,

Here's my current formula in "Design View":
Between DateAdd("d",-Weekday(Date()),Date()+2) And Date()

This helps me sums up my data from the Monday of the current week until now.

However I am looking to compare to the same week of the previous year, any ideas where I can start?

Thanks in advance.


Is there a way to write a SUM() formula so that it sums only the
positive numbers in the range and ignores the negative numbers?

I have a .csv download of my credit card transactions. I'd like to
easily sum the purchase transactions while ignoring the payment
transactions that are interspersed with the purchases.

Thanks for your help,

Fred Holmes



Hi guys,

I have something that resembles the layout below on a sheet.
I'd like to be able to at will be able to select which of the 3 (height, weight, notes) I can see at any one time. I know this is possible with a pivot table, but I cannot use one because the notes are always text and not numbers... anyone know a good way to tackle this?

Thanks

HTML Code:

Name     Height     Weight      Notes
Name 1    178         75           Notes abt p1
Name 2     82          82          Notes abt p2
Name 3    171         63           Notes abt p3





I have created a pivot table which searches through a list of approx 1000 records and it provides a number total of records which finish in a week number based on its finish date. from week 01 to week 12.


however, some of the week numbers do not appear in the table as there are no records which fall into that particular week, so the column titles which represent the week numbers appear as

1 2 4 6 7 8 12



I would like the pivot table column titles to include all the week numbers from 1 to 12 and just show the empty columns underneath them as well as the ones which will have record totals in them

any ideas?


I tried this with macro recording and it didnt work like I wanted it to.... heres the problem

I have a pivot table that looks at a data set which contains a list of people's names, a corresponding project that they are working on, and how many hours they work on that project. Each person can have any where from 1 to 5 different enteries. The pivot table tells me the sum of the hours each person is working. The pivot table also filters based on date reported. Everyone always reports on Friday. The spreadsheet where I log this information does not get overwritten every week, just appended with a date stamp. So each week I need to filter the pivot table based on the current week, ignoring all the historical data.

Leading to my ask, I need a macro that will filter the pivot table based on only the value in cell B3 (the current reporting date). Is there any way to do this?

When I attempted to record the macro, it made the values I did not want invisable, problem was I needed to specify those values. I couldnt just say

.PivotItems(ALL).Visible = False
.PivotItems(B3).Visible = True

(something like this would be desired.)

Anyhelp would be greatly appreciated!


Hey everyone,

I'm trying to use a V-lookup that will combine 2 values as the lookup value instead of just one.

I'm comparing 2 weeks worth of cheques we write out, and seeing which cheques were added between the 2 weeks by using a V-Lookup with the lookup value being an invoice number. This way, whatever invoice numbers do not appear on last week's payments, but do appear on this week's would be a new cheque added.

My problem is, occasionally I'll have the same invoice number but for a different customer. I need to do a V-Lookup that uses both the invoice number AND customer name.

See attached dummy file.

In my dummy file, I would take this as the last 2 invoices were new cheques, however, the first invoice is also new, but is returning the data from another cheque completely due to the same invoice number. I want to make sure it only picks up the 1124 invoice from Walmart, regardless if the invoice number is the same for a different customer.

Thanks!


Hello!

I've been trying to make a Excel spreadsheet which shows vocal range in terms of notes/octaves. Each note uses the note named and followed by a number which represents which octave it is. There are twelve notes in each octave. The notes go C, C#, D, Eb, F, F#, G, G#, A, Bb, and B. So these notes are in ascending order: B1, C2, C#2, D2, Eb2, F2, F#2, G2, G#2, A2, Bb2, B2, C3, etc.

What I would like to do is something which works like this:



With all columns sortable. If these were just plain numbers then it would be simple enough, however is there a way to configure Excel, with a formula, format, or otherwise, to recognize the notes as numbers for this purpose?

Cheers


I was wondering if you have any ideas on this

See attached sheet!

I want to take only the D1 to D19 values and look them up in the table in row A then sum the values!, the problem is it has to ignore Numbers and Other values not starting with a D! These should total up in row 25 under each date!

The same with column K it should total the hours for D values Horizontaly!

To add a twist D1-D99 lookup column b in the table, D101-D199 lookup column C, D201-D299 lookup column D.

The Sheet is in Excel 2002 format!

Thanks

Darren!