How do I calculate a value based on dates?

[Guest]

I have a worksheet that calculates YTD (year-to-date) totals from 3
categories...labor, travel, and other expenses. I want to add a row that
will separate monies spent from a range of dates (i.e. 1st of the month to
the last Friday of the month) in order to calculate monies spent MTD
(month-to-date). I am willing to change the monthly values if need be.

 

[Dave O]

Hi, John-
I arrived at a solution for this by mocking up some data:
~Column headers "Date" "Type" and "Amount" in A3:C3
~Date entries from Feb to May in A4:A15
~Line identifiers "Labor" "Travel" and "Other" in the Type column,
from B4:B15
~Dollar amounts in C4:C15
A summary section:
~A "Month to Date" column header in A17
~The date 2/1/2005 entered in A18:A20 formatted to display as Feb 2005
~Line identifiers "Labor" "Travel" and "Other" in the Type column,
from B8:B20

I entered this formula in C18:
=SUMPRODUCT(--(MONTH($A$4:$A$15)=MONTH(A18)),--($B$4:$B$15=B18),$C$4:$C$15)

The SUMPRODUCT() formula used in this context allows you to specify
parameters used to sum a column of data. The "--" in the formula is a
double unary operator that causes Excel to evaluate a True or False
value as a 1 or 0. For a given line, if the Month parameter is True
and the Line Identifier is True, Excel multiplies 1 x 1 x the value in
column C, and augments the sum. If one of the parameters is False, a 0
multiplier is used: since any number times 0 is 0, the sum is not
augmented.

 

[Guest]

Will the SUMPRODUCT formula calculate information I've entered between the
range of dates? I'm looking to take an hourly total of labor (labor hours
times labor rate), a total of figures I've entered manually for travel and
other expenses and see if I can calculate a total based on the range of 1st
of the month to last Friday of the month. I'm growing even more confused by
the minute.

 

[Dave O]

Sorry for the delayed response- I didn't see your post.

SUMPRODUCT will do whatever you tell it to do- I had mocked up some
data on the assumption that the LABOR entry had a date associated with
it, and the SUMPRODUCT formula I created added all the entries
pertaining to a particular month.

Post again showing some of your actual data (or faked data using your
actual layout) and your desired result.

 

[Guest]

I was working with your idea using SUMPRODUCT when it hit me to do it simple
by linking previous Excel spreads. Simple sum formula was commonly used as
an end result. Thanks for your help.

 

[Guest]

Dave hope you can help, i have trades i make (forex) listed by day and may
make 2-3 trades per day on different currencies ie

14-mar -20 Gbp
14-Mar +43 CHF
14-Mar +12 Eur

I have a column which summerises the daily profit/loss on each day, but i
have to manually change the field range on my sum product function each time
i add new trades to the spread sheet is there anyway to automatically update
the spreadsheet as new entriea are made. Also i would like the equity curve
graph i have based on the daily profit/loss to be updated automatically
hope you can help

thank you