# Sum Formula: Count Of How Many Gallons Of Gas I Put In Car Each Week

Mar 25, 2009

I need to be able to keep a running count of how many gallons of gas i put in my car each week. Each week is one column. Column A is where i want the total to show.
column B,C,D,E...etc is where i put the numbers in this is all in row 1 for now.

currently i have =sum(B1:BB1). But something is not right because it is not adding the numbers together it will only add what is already there not any numbers that i put in after the formula is made. Do i have the wrong formula or something else wrong. My goal is to see how many gallons i put in at the end of the year, month, quarter, and so i have several other reason for this info.

## Formula To Count Value In Column That Was One Week Ago From Today?

Apr 28, 2014

I have 2 columns in my spreadsheet:

B:B is a column of dates.
C:C is a list of names

formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days

## Formula - Week Product Level Count

Jan 5, 2014

How to solve my problem in attached file : Week Product Level Count.xlsx

## Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week

Apr 9, 2009

I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

## Auto-updating Formula For Week Over Week Change?

Dec 11, 2013

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

## Convert Gallons To Litres

Jul 23, 2007

I am working on a spreadsheet showing pricing in gallons and pounds. Someone has requested the entire pricesheet also show pricing for these products in kg. and liters. an easy formula to enter that would copy and change pricing as requested in the entire spreadsheet.

## Formula To Tell When Its Week 1 Or Week 2 Of A Any Given Month

Jun 16, 2014

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

## Count 4 And 5 Week Months?

Sep 26, 2013

I have a spreadsheet that I am using for capacity planning. We want to automatically figure out which months have 4 weeks and which months have 5 weeks.

The weeks in a month are defined by work week (Mon to Fri), and if the work week has 3 or more days, it is considered a week of that month, if it has two or less days, it is not counted as a week during that month (it will be counted part of the following month).

ie: April 2014 would be a 5 week month, May 2014 would be a 4 week month and June would be a 4 week month.

Apr-14
May-14
Jun-14
Jul-14
Aug-14
Sep-14
Oct-14
Nov-14
Dec-14
Jan-15
Feb-15
Mar-15

[code].....

## Count Employee For Each Week Ending?

Jan 29, 2014

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

## Count All Rows (dates) That Will Come Due In A Week

Mar 27, 2009

Worksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.

## Match Week And Count Rows With Yes Or No.

Jan 12, 2010

I am trying to produce a excel spreadsheet on Excel 2003 that has sales data. At the end I am to produce where my supervisor can produce a summary of the data showing the last weeks work (and only the last weeks) and how many yes rows there were and how many no rows there were for that week. There is a lot more data involved but the only relevant data for the problem I have is the date and Yes/No fields.
I have looked on other sites and the only thing that I can find that is close is the following formulas.

=COUNTIF(Sheet1!A:A,">"&E1-7)-COUNTIF(Sheet1!A:A,">"&E1)
=SUMIF(Sheet1!A:A,">"&E1-7,Sheet1!C:C)-SUMIF(Sheet1!A:A,">"&E1,Sheet1!C:C)

Apparently these will count the rows and sum them. However they don't differentuate between yes and no. I have attached a very basic spreadsheet with the problem.

## How To Count Dates Based On Week

Jul 18, 2012

I have a worksheet that lasts for a year that is something look like this:

Date Name
1/1 John
2/1 Erwin
3/1 Robert
4/1 John
16/1 Erwin
17/1 Erwin
17/1 John

In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week

Name Week1 Week3
John 2 1
Erwin 1 2
Robert 1 0

## How To Count Number Of Events By Week

Jun 27, 2014

I am trying to count the number of events by week. My column is title weekof and of it I have made a defined range...weekof. My formula is simple countif(weekof, "2/9/14")

The formula providing the 2/9/14 is =\$E2-WEEKDAY(\$E2)+1

Countif seems not to recognize the date at all. I found a posting showing this to work =COUNTIF(Weekof,"

## Count Numerical Day In A Range Of Dates - Not Day Of Week

Jul 8, 2014

I want to find the number of times the 23rd happens between a range of dates.

Let's say I invoice a customer on 5/23/12 (Column C) and I've sent an invoice every month on the 23rd. I want to count how many invoices I've sent during the past few months. I need a formula which tells me how many times the 23rd of the month happens between 5/1/12 (in column A) and 12/15/12 (in column B).

I thought it might be =COUNTIFS(A1:B1,DAY(C1)) but that formula is for counting cells in a range.

I found formulas for counting the specific day of the week (like Tuesday) and for counting the number of cells containing a number, but not this.

## Head Count: If It Is Greater Then 7 Then It Would Be 1 For Current Week

Aug 20, 2007

Currently having problems getting correct head count. I have formula that works for rows 6-8 but fails in row9. The should be answers are in rows 17-20.

Conditions used in formula
*Start date > Start FY =0
* Current Week > End Date =0
* End Date < Start FY =0

The date difference is divided by 7 because there are 7 working days in a week. If it is greater then 7 then it would be 1 for current week. I tried zipping the file but I could not shrink it to required size. find on weblink below: http://maxupload.com/E759C9D9

## Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

## Formula For Week # Of Month

Mar 12, 2008

what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?

For example (Sunday being the first day of the week):
January 5th 2008 = Week 1 of January
January 6th 2008 = Week 2 of January
February 2nd 2008 = Week 1 of February
February 3rd 2008 = Week 2 of February

WEEKNUM perfectly calculates this, but it is applicable for the whole year.

## Displaying A Certain Day Of The Week Formula

Apr 3, 2007

Is there a way to create a date formula that will only display the Friday date with in any given week?

## Formula To Get Date From Month And / Or Week

May 30, 2014

Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.

The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).

So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).

## Formula To Automatically Add Values Each Week

Jul 6, 2009

I need a formula on Cell C3 on the attached Sheet1.

This should add numbers from the Actual columns as they are updated; i.e., as soon as I populate 'Actual' columns such as F, I, L, O, R... Cell C3 should add up the numbers automatically. This way I don't have to update the Cell C3 manually each week I populate the Acutal columns.

## Formula To Return Value Of Week From Date

Mar 26, 2012

Is there a formula to return the value of week from 9-6-09 i have on cell a1 and 1-2-10 on cell a2 and i want the total number of weeks on a3.

## How To Create Week Ending Formula

Jul 24, 2012

I am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this?

## Changing Cell Formula Each Week

Oct 3, 2006

I have a sheet (sheet2) that this week has the formula
Cell B2
=Sheet1!D2
Cell B3
=Sheet1!B2

Next week I want the formula to be
Cell B2
=Sheet1!F2
Cell B3
=Sheet1!D2

And so forth.

Each Column has the Week ending date (a sunday) in Row 1. So D2 represents this week and B2 Last week, until next week when D2 becomes the 'last week' and F2 becomes the this week.

The inbetween letters contain another set of data for those weeks so i will apply the same formula to these.

## Excel 2003 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:

(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)

(this is not the actual formula but simplified, that's not really important).

It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):

I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula

(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:
(0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).

Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective

I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.

## Adjust Formula To Show A Selected Day Of Week?

Feb 25, 2014

I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.

=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))

Is there any way to adjust it to do this the drop down box for the day will be in F9.

## How To Get Week Numbers For Particular Item Using Excel Formula

Apr 1, 2014

In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.

sortbyDates.xlsx‎

## Setting A Formula To Relating To Days Of The Week

Oct 6, 2009

Sheet1!A1 = Sat

Sheet2! A2 = I need this to equal Sun....

I tried =sheet1!A1+1 but got #value

## Formula To Get Header Values Of Week No For Less Than 85% In Range

Aug 7, 2014

I have set of range contains in column partner name & Wks from 1 to 5 In row range. i want get result into L2:P10 range from wks table

criteria is <85% where find in between wk 1-5 get result count of <85% and get % & what week the value is?

For example: if One partner contains 2 wk less than <85% then result like 1st 56% WK-1,2nd 58% WK-2

Find the attachment : Pack.xlsb‎

## Embed Formula In VBA (calculate The Absolute Week )

Dec 28, 2009

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

## Formula To Automatically Expand Each Week As New Data Comes In

May 17, 2007

I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.

All details are mentioned in the attached ZIP file...so I won't repeat myself here.