How Do I Add Dates in a formula?

[Rogue]

I'm trying to get the sum of dollars spent in each week of the year. The
range is compiled of invoice data, with non consecutive dates. Column A
contains dates, and column G shows the corresponding dollar amounts. I
want to return the total dollars spent for the first week of the month
in cell E20. (I will end up having all the weeks of the year listed
with the dollars spent in that week.) Should this be a SUMIF function,
and how should dates be entered into the formula?

 

[Ron Rosenfeld]

I'm trying to get the sum of dollars spent in each week of the year. The
range is compiled of invoice data, with non consecutive dates. Column A
contains dates, and column G shows the corresponding dollar amounts. I
want to return the total dollars spent for the first week of the month
in cell E20. (I will end up having all the weeks of the year listed
with the dollars spent in that week.) Should this be a SUMIF function,
and how should dates be entered into the formula?

You can use SUMIF.

Let us say you have your WeekStartDates in Z2:Z54, and your range of Data goes
and Dates goes from Row2--Row1000.

The general form of the formula would be:

=SUMIF(A2:A1000,">="&Z2,G2:G1000) -
SUMIF(A2:A1000,">"&Z2+7,G2:G1000)

Format the result as Currency.


--ron

 

[Ragdyer]

One way to approach this is to enter the starting date of the first week in
say H1,
And the ending date of the first week in I1.
Then, enter the starting and ending dates of the second week in H2 and I2.

Now, select *all 4* cells, and drag down to copy, as needed.
This gives you a datalist of your weeks, which you can reference in a
formula.

Enter this formula in J1, and copy down as needed:

=SUMPRODUCT(($A$1:$A$100>=H1)*($A$1:$A$100<=I1)*$G$1:$G$100)

 

[zorvek]

Assuming your dates and dollar amounts start in row 2 (you have a header
row), the following formula, placed in cell E20, will return the sum of
sales for the first ISO week of the year. Copy the formula to cell E21 to
get the total for ISO week 2, and so on.

=SUMPRODUCT((1+INT((A$2:A$100-DATE(YEAR(A$2:A$100+4-WEEKDAY(A$2:A$100+6)),1,5)+WEEKDAY(DATE(YEAR(A$2:A$100+4-WEEKDAY(A$2:A$100+6)),1,3)))/7)=(ROW()-19))*G$2:G$100)

Note that the ranges A$2:A$100 and G$2:G$100 must match the range of
existing data. Any empty or non-numeric cells in those ranges will produce a
#NUM! error. Also note that the ranges should only span the current year.
Since the formula works with ISO week numbers, expanding to more than one
year's worth of data might include multiple years in a specific week.

Kevin