Calculating Multiple Columns into one Cell
 

I have a Column of Amounts and a column of Dates. I want to find the total
Amount spent in the first week of Januray, Second week, etc.

I can write a complex "=if(and" statement but I'm trying to limit the imput
in the cell to make it easier over the year. Is there a better way to
calculate it?

ie. "If Cell A1:A31 = january 4 thru january 10 returen the sum of cell
B1:B31"
My problem is I only want the cells that correlate with the cells that fit
the range?

So A1 and A15 might apply so I'd want B1 and B15 to add togther?

Calculating Multiple Columns into one Cell
 

I'm assuming you have dates entered as 'dates' and not just text. For
versatility, I'll assume dates you want as guildelines are in cells c1 & c2

=SUMPRODUCT((A1:A31=C1)*(A1:A31<=C2)*(B1:B31))

Its easier to use cell references this way because you'd have to convert the
dates to serial numbers, which can get confusing.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Michael" wrote:

I have a Column of Amounts and a column of Dates. I want to find the total
Amount spent in the first week of Januray, Second week, etc.

I can write a complex "=if(and" statement but I'm trying to limit the imput
in the cell to make it easier over the year. Is there a better way to
calculate it?

ie. "If Cell A1:A31 = january 4 thru january 10 returen the sum of cell
B1:B31"
My problem is I only want the cells that correlate with the cells that fit
the range?

So A1 and A15 might apply so I'd want B1 and B15 to add togther?

Calculating Multiple Columns into one Cell
 

Say our data is in cols A & B:

1/1/2009 46
1/2/2009 28
1/3/2009 22
1/4/2009 40
1/5/2009 24
1/6/2009 10
1/7/2009 0
1/8/2009 44
1/9/2009 42
1/10/2009 30
1/11/2009 38
1/12/2009 45
1/13/2009 34
1/14/2009 36
1/15/2009 35
1/16/2009 11
1/17/2009 48
1/18/2009 9
1/19/2009 39
1/20/2009 25
1/21/2009 37
1/22/2009 16
1/23/2009 22
1/24/2009 2
1/25/2009 14
1/26/2009 16
1/27/2009 40
1/28/2009 9
1/29/2009 4
1/30/2009 50

In C1 we enter:
=WEEKNUM(A1) and copy down:

1/1/2009 46 1
1/2/2009 28 1
1/3/2009 22 1
1/4/2009 40 2
1/5/2009 24 2
1/6/2009 10 2
1/7/2009 0 2
1/8/2009 44 2
1/9/2009 42 2
1/10/2009 30 2
1/11/2009 38 3
1/12/2009 45 3
1/13/2009 34 3
1/14/2009 36 3
1/15/2009 35 3
1/16/2009 11 3
1/17/2009 48 3
1/18/2009 9 4
1/19/2009 39 4
1/20/2009 25 4
1/21/2009 37 4
1/22/2009 16 4
1/23/2009 22 4
1/24/2009 2 4
1/25/2009 14 5
1/26/2009 16 5
1/27/2009 40 5
1/28/2009 9 5
1/29/2009 4 5
1/30/2009 50 5

Now that we have the week numbers, we can calculate the weekly sums. In D1,
enter:

=SUMPRODUCT(--(C$1:C$30=ROW())*(B$1:B$30)) and copy down to display the
weekly sums:

96
190
247
150
133


--
Gary''s Student - gsnu200824


"Michael" wrote:

I have a Column of Amounts and a column of Dates. I want to find the total
Amount spent in the first week of Januray, Second week, etc.

I can write a complex "=if(and" statement but I'm trying to limit the imput
in the cell to make it easier over the year. Is there a better way to
calculate it?

ie. "If Cell A1:A31 = january 4 thru january 10 returen the sum of cell
B1:B31"
My problem is I only want the cells that correlate with the cells that fit
the range?

So A1 and A15 might apply so I'd want B1 and B15 to add togther?