Sum By Condition For Each Month

Feb 11, 2008

I have a spreadsheet for to control different types of personnel expenses. For every person there are 17 rows each column. The spreadsheet itself contains a named range called "database" which in turn contains 14 columns. The second column contains the name of the expense, the first column the number of the expense (going from 1 to 17), the last twelve columns show the monthly expenses. Every month or so two or three new persons are added to the spreadsheet, and for all the others the costs are updated in the respective column.
I would like to have the monthly total for each expense type below the dynamic range. I already tried using the SUMIF- Function and it yielded good results, but unfortunately, it stops summing after the 1125 row. Reason unknown. Now I've been trying to get the DSUM()-Function working, but no luck here either. I've read the thread "Add or Sum every nth cell" and changed it slightly, so that the DSum() would compare the number of expense to a given value (1, 2, 3, etc.), and if TRUE, sum it up. Let's assume the named range starts in A2 already and the criteria are in E1:E2.

Example: Total for cost type 1 for October (3rd column)
=DBSUM(database;3;E1-E2)
Criteria:
=A2=1
The formula is acting weird and giving me the wrong sum. It doesn't change when I change the cost amount either. Ideally, I would like to have a formula that sums every cell, where the cost type =x, or every cell where the rownumber according to the named range =x (then using the MOD and ROW functions).

View 5 Replies


ADVERTISEMENT

Excel 2010 :: CountIf For Given Month And Given Condition

Mar 20, 2014

MS Excel Version : 2010
Attached File name : countif for a given month.xlsx

I want to a sum the given condition xxx / yyy according to the month in yellow cells.

View 6 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

Multiple Condition If Formula That Will Take Into Consideration Month Only In Date

Mar 25, 2014

formula that will compute contract amounts that are paying out for a given month, depending on what is listed as the contract renew date and how often the contract renews.

Assume the contract date is in C1, contract renew date is in C2, Contract term is a drop down menu in C3 that has two options of text, 6 Months or 12 Months. C4 gives the contract amount. There are then dollar amounts in C5 and C6 that are only taken into consideration if they are $0.00. In B1 will be the run date or the date I want to pull my info for.

I basically need a formula in C7 that will return the contract amount from C4 taking into account these conditions: If there is a dollar amount in either C5 or C6, return "$0.00" in C7. But if C5 and C6 are both zero, than: If C3 = "12 Months" and B1 is in the same month as C2 (I don't want it to take into account the year of day of the date), than return the amount listed in C4 in cell C7. Or if C3 = "6 Months", and B1 is in the same month as C2 or if B1 is 6 months ahead of the month listed in C2 (again not taking into account the day or year), than return the amount in C4 in cell C7. So basically if the contract amount in C4 was $100, and B1 3/1/2014 and the contract renew date is C2 is 9/15/2013, I still want $100 to appear in C7, even though there is only technically 5 and a half months in between the dates of 9/15/2013 and 3/1/2014. I want it to look at these dates and add six months to the month of September and then look at cell B1 and as long as that date is a date in March, return the contract amount in cell C7. I also want $100 to appear in C7 if the date in B1 was a September date. In the situation where C3 is set to 12 months, if C2 is a September date than $100 will only appear in C7 if B1 is also a September date.

And one last thing, I'm going to somewhat withdraw my request that the year shouldn't be taken into account. This is only partially true. It just occurred to me that the $100 should only appear provided the date in B1 is in the same year or in a subsequent year AFTER the year of the contract renew date in C2. So for instance if, C2 is 9/15/2013 and I put in 03/01/2013 in B1, it should NOT calculate the $100 in C7 since the contract technically would not have renewed in March of '13 as it didn't exist until Sept of '13.

View 1 Replies View Related

Multi Condition Date Cell Test For Month & Year

May 18, 2009

Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.

View 3 Replies View Related

Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria

May 23, 2008

need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.

View 7 Replies View Related

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

Cell Equals 0 Changes When Condition Met - Leave Unchanged When Condition False

Mar 24, 2014

I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.

The Cell formula that I am trying to use is as follows:

=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))

A3 contains TODAY()

H114 is the cell being calculated

AA118 is the cell containing the calculated value for the current month.

View 2 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

View 6 Replies View Related

Conditional Formatting :: Condition 1 Override Condition 2?

Feb 29, 2008

I'm having trouble with the two formulas working in conjuction with each other.

Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))

Condition 2:
=TODAY()>=A1

"Condition 1" seems to override "Condition 2"? Ever see that?

View 9 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

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

Insert All Dates For A Month Weekly When User Inputs Month And Year?

Jul 10, 2014

I have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.

So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...

So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.

Attached File : Time and Attendance.xlsx‎

View 2 Replies View Related

Using Month Function To Obtain Month And Copy It To Next Empty Column

Apr 4, 2013

I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.

I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.

VB:

Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")

Do While Not IsEmpty(Cell)
If Cell = "Date" Then

[Code] .....

View 5 Replies View Related

Formula To Distinguish Month Year From Prior Month Years

Feb 13, 2010

This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View 4 Replies View Related

Automatically Get All The Dates Of A Month Entering Specific Year And Month

Dec 1, 2012

In a sheet I enter the following:

... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)

How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?

View 3 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

Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)

Mar 20, 2009

I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data

On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.

Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

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

Automatically Bold And Highlight The Current Month’s Total And Month Name

Jan 21, 2010

I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.

Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?

View 9 Replies View Related

Attached Worksheet Automatically Shade Out All The Saturdays & Sundays In Any Given Month Everytime You Change The Month/Year Cell

Jul 19, 2006

Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.

View 2 Replies View Related

Excel 2007 :: How To Convert Month Number To Month Name

Feb 16, 2012

I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...

January 1 2012 to January 31 2012

The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.

View 14 Replies View Related

Auto Format Spreadsheet With Various Rows Month To Month

Feb 4, 2009

I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.

Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).

Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?

I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.

View 2 Replies View Related

Converting Month Numbers To Month Names For Pivots

Oct 24, 2011

My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.

How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.

I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.

If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.

I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?

View 6 Replies View Related

Total Liters Sold Per Month In 24 Month Period

Aug 14, 2012

I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.

I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))

However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.

View 7 Replies View Related

Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)

Jul 28, 2008

I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?

View 9 Replies View Related

How To Count Condition Based On Having Another Condition

Apr 12, 2014

So I have one sheet that needs to pull data through to another sheet (which is a stats summary)

I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.

For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.

View 9 Replies View Related

Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

Jan 15, 2014

Here's a simplified example:

ColA
ColB
ColC

Row1
A
Y
A

Row2
B
N
D

[Code] .........

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

View 7 Replies View Related

Reflect Month Name When Column A Date Is Same Day And Month

Jan 8, 2014

How to correct this formula to get correct output.

=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))

It's working fine if the year is 2014 but not if the year is different.

Conditions:

If the date is today then only the month should display else blank. The year should be ignore.

For example:

08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell

The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.

View 10 Replies View Related

Forcing Month / Year Entry To Last Day Of Month?

Jun 10, 2013

I use a certain portion of our workbooks to enter the due dates of certain equipment, which are always listed in month/year (e.g. 4/17), and which means that they are good until the last day of said month. That is, a due date of "5/16" is good through May 31, 2016.

I'd like for my users to be able to enter "5/16" and have that cell properly identify as May, 2016 (instead of May 16 of the current year)...more specifically, the last day of May, 2016. All of this so that I can conditionally format any cell where that date has past - that is, if my user enters "5/16" on May 15, 2016, it won't flag.

View 8 Replies View Related







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