I maintain a table with projects and their respective costs / revenues.
I have a formula that automatically sets the forecast and Year-to- Date periods based on the month and date.
I need to automate the year-to-date sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of Jan-Sept in the YTD column(for this month).
A sample workbook is attached.
I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.
So column A = age
and column B = cancel date
Let's so for January 2006 I'd like to find the average age.
Logically this is how I see it...
Average Column A (If column B >= 1/1/2006 and < 2/1/2006)
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.
I tried the following:
=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))
Column E would contain a date, such as 01-07-07 or 1st July 2007.
In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?
I am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..
For further information, check workbook attached...
SUM 3 MODELS MONTH.xlsx‎
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
View 2 Replies View RelatedI 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.
Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.
Order Tracker 2.xlsx‎
check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).
I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........
I have a Excel-spreadsheet (sheet1) which has dates in column G. I am looking for autofiltering data by year or month based on date value in column G. In my userform I have two comboboxes, one has years from 2009-2014 and another one are months from Jan-Dec. How can I write a code that shows only rows which match either year or month value based on combobox selection?
View 3 Replies View RelatedI have 3 or 4 criteria and one data range to sum based on these criteria. If I remove date as a criteria, I can make a sumproduct formula work, and even if I include a specific month it works as well, I would like to write a formula that uses the most recent month with data as a criteria. My reasons are that some banks pay our reps monthly, some quarterly, some semi-annually, and some whenever they damn feel like it. I need to take a snapshot of what each rep has invested in each bank: So for Bank A his last Statement came in March, but Bank B sent a statement in January. Here's what I have so far:
=SUMPRODUCT(Month('2007 Data'!$F4:F$97),'2007 Data'!$G$4:$G$97=Sheet1!$A2,'2007 Data'!$B$4:$B$97=Sheet1!$G$1,'2007 Data'!$H$4:$H$97)/(month('2007 Data'!$F$4:$F$97)
I know that the Month function here is incorrect but I'm going bonkers trying to wrap my head around this.
FYI, 2007 Data is my Data page, Col F = Date Range, Col G = the Rep ID, Col B, = the Bank name, Col H = Assets Invested; Sheet1 = my results page.
I know, I KNOW, many of you will scratch your heads and say this idiot should just use a Pivot table, I know. I have. Let's just say some of the end-users are not comfortable with Pivot Tables. I've come up with a simple listbox/command button to populate the axes of the chart (Column A = Reps, Row 1 = Banks)and I need this formula inside the chart.
After seeing and learning from many of the posts on this site, I'm sure someone knows how to acheive what I'm after, I think it's just a matter of my communicating it as well as I can.
I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.
I have never used the SUMIFS before and having trouble, here is the equation I'm using.
=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")
*F2:F204238 is the range that needs to be summed up
*the month(referenced range) is in date form
*I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.
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 ...
I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
I have attached a small copy of the worksheet.
I need to count when the following set of 3 criteria criteria are met:
1: The Assessor Initials = DS
2: the month = Mar
3: Check 4 Ranges = columns I, K, M, O
I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.
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.
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
Below is my function. I use it to count the number of times a value in A9 appears in Sheet2. I would like to edit my function to count the value in A9 that appeared in a given month in U6.(U6 is 12 for decemeber)
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),"")
This is what I thought it would have been but I think I'm wrong because the function is not counting by month.
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),IF(MONTH(Sheet2!D14:D9000)=U6,"")
I'm trying to use the SUMIF function based on a specific month. This month formula has to choose from a range of dates. Currently, the month to report (5) is the only one that works. If I have dates in June and choose 6, it doesn't work.
This is the formula I'm using right now:
=IF(MONTH('Drafting Errors'!C:C)='Error Metrics'!C1,SUMIF('Drafting Errors'!A:A,C5,'Drafting Errors'!N:N),"No data for this month")
I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far:
=SUMIF('2008'!E4:E149,(LEFT('2008'!E4:E149)="06/"),'2008'!I4:I149)
My table looks something like:
Log NoShip DateQty
000953/26/20085
000963/28/20081
000974/28/20081
000985/28/20082
000995/29/20081
001006/1/2008
001016/14/20084
001026/20/20083
The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedI have attached a sample excel sheet.
I want data to appear on sheet 2 from B10 onwards based on month selection and removing duplicate entries and sorting in ascending order
When no data is found to display message box "data not found"
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.
I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
I have a sheet with the following values:
280315 085B 10:24 2R
The '085' is the 85th day of the year. I would like a formula that reads that part of the data, and return the month that day is in, in the "jan", "feb", "mar", etc. format.
I have a field where the month is written as text. I would like to highlight row whose month column has passed. My conditional formating code is:
View 4 Replies View RelatedI am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month
For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on
I have a column of dates and want to count how many times there is an entry for a particular month. example:
(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009
I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.
=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.
i have big sheet with lot of data and in column B there is date dd-mm-yy.
How can i count specific product (column A) based on date (column B) for each month) on sheet B.
