Calculate Sum Of Values Based On Month

May 21, 2014

I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.

Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.

Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.

View 3 Replies


ADVERTISEMENT

Calculate Year To Date Based On Known Month

Dec 6, 2009

I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

View 3 Replies View Related

How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

View 4 Replies View Related

Calculate Number Of Dates Within A Column Based On Month

May 15, 2009

I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.

So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.

View 6 Replies View Related

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

Jan 28, 2010

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View 9 Replies View Related

Using Offset From Latest Month To Calculate 3-month Average Within A Range

Jul 1, 2009

I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........

View 9 Replies View Related

How To Calculate Average Of Values Against Selected Date Of Each Month Within Date Range

Jan 2, 2014

I have a table that looks like this (its basically a historical data of a stock exchange):

Date
Index
January 4, 2010

[Code]....

The List continues till the current Date.

I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.

Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.

View 1 Replies View Related

How To Find Top 10 / Lowest 10 Values Based On Month

Jun 18, 2014

I have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.

So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W

I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.

Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.

Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.

View 4 Replies View Related

Formula To Calculate Based On Start Month Number And Duration Number Of Months

Mar 14, 2014

I need a formula that will calucalte the monthly total based on the following conditions:

Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.

Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx

View 3 Replies View Related

Calculate Values Based On Category?

Jan 30, 2014

I have 2 tables:

IDTypeValue
1A3
2A3
3A4
4B4
5B3
6C2
7A4
8A3


And

TypeTotal
A
B
C

I need to "total" column to automatically display the sum of the values for the categories A, B and C. Is there a formula I can use for this?

View 2 Replies View Related

Sum Year To Date Based On Month Chosen, Rank Values & Compare Rankings

Dec 22, 2008

1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.

2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.

3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).

View 5 Replies View Related

Calculate Average Based On Matching Values From Two Worksheets

Jan 22, 2010

Worksheet 1

I am calculating group averages for the following performers - very good, good, average, low, very low - for a series of factors.

Worksheet 2

Contains the same factors with the values for which Im trying to work out the average. Each factor has a performance rating above it, either very good, good, average, low, very low.

I need a formula which will match the performance rating from worksheet 1 (I3, J3, K3, L3, M3) to worksheet 2 and then calculate the averages of each factors based on those matches.

View 10 Replies View Related

Nested IF (index And Match): Calculate A Charge In Cell K15 Based On Values In B9 And C10 Referencing The Table I8:R9

Jan 21, 2010

I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).

View 2 Replies View Related

Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

View 2 Replies View Related

Function To Fill All Days Of Month To End Of Month Based On Workdays

May 1, 2006

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday

to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.

View 11 Replies View Related

UDF To Calculate The Month

Aug 13, 2007

I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb

View 9 Replies View Related

Calculate Fiscal Month

Oct 7, 2008

I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.

View 4 Replies View Related

How To Calculate (first Of The Month After 60 Days)

Apr 17, 2013

I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.

Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?

View 8 Replies View Related

Auto-calculate Last Day For A Month

Nov 28, 2006

I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).

View 9 Replies View Related

Calculate Interest Per Month

Jan 12, 2010

Sample data:

02-06-09 to 18-06-09 earned $2500
19-06-09 to 23-08-09 earned $3500
24-08-09 to 31-12-09 earned $4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

View 9 Replies View Related

Calculate The 6th Month From Start Date?

Dec 25, 2013

I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.

ie: start date: 26/12/13 -- End date: 25/06/14

View 3 Replies View Related

Auto Calculate The Sart And End Of Each Month

Dec 9, 2008

I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.

View 4 Replies View Related

Calculate 1st Tuesday Or 3rd Friday Of Month

Nov 11, 2009

I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

View 3 Replies View Related

Calculate Date Of First Business Day Of A Month

Sep 19, 2012

I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.

For example:

I have a workbook with two worksheets in it.

The first worksheet is the input page.

On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.

Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12
Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)
(in this example, this should be 3) and displayed as 03-Sep-12
Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1

The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.

The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.

Daily
Date
USD

29 June 2012
1.0191

1 August 2012
1.0507

[Code] ........

Worksheet 1, Cell C1 should display the value of 1.0266

View 5 Replies View Related

Calculate Third Wednesday Of Month Plus Interval

May 21, 2013

For any given year, I would like to calculate the date of the third Wednesday of each month for that year, plus the interval (in weeks) between two consecutive months (Which will be either 4 or 5).

Example:
Enter 2013 in cell A1
Output would be:
A2 - Jan 16
A3 - Feb 20
A4 - Mar 20
A5 - Apr 17
..
A13 - Dec 18

AND

B3 - 5 weeks (interval Jan-Feb)
B4 - 4 weeks (interval Feb-Mar)
B5 - 4 weeks (interval Mar-Apr)
..
B13 - 4 weeks (interval Nov-Dec)

View 2 Replies View Related

Selecting A Month To Calculate Invoice

Jan 16, 2008

Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates.

H I J
27 Qauntity: Cost: Amount:
28 55 R 185.00 R 10,175
29
30 Net amount: R 10,175
31 Vat: R 1,424.50
32 Amount Due: R 11,599.50

Invoice
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
H28 =SUM(Divenic!G5:G5004)
J28 =SUM(H28*I28)
J30 =J28
J31 =J30*0.14
J32 =J30+J31

[Table-It] version 09 by Erik Van Geit

View 9 Replies View Related

Calculate Sum Of A Column If Month And Year Is Correct

Jun 19, 2014

So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.

Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for February 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.

See attached file for example and for my =sumproduct formula.

Excelforumexample1.xlsx‎

View 5 Replies View Related

Calculate Number Of Calendar Days For Each Month?

Mar 17, 2014

I have a list of months in column A and would like a formula in column B that calculates the number of calendar days for each month (i.e. Jan=31, Feb=28 etc.)

View 9 Replies View Related

Date Function Query (calculate The Last Day Of The Month)

Nov 12, 2008

I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)

The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)

This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.

View 4 Replies View Related

Calculate The Quartiles With The Condition Of The Year Or The Month Or Even Both

Oct 16, 2008

i have a dataset which is like a timeseries with 3 columns
first is year second month third values
so lets say like this

year month day value
2004 12 29 100
2004 12 30 200
2004 12 31 300
2005 01 01 50
2005 01 02 60
.....

I need to calculate the quartiles with the condition of the year or the month or even both... I suppose i need something like an array but i couldnt make it work untill now.

View 2 Replies View Related







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