Calculating Multiple Columns into one Cell

M

Michael

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?
 
L

Luke M

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.
 
G

Gary''s Student

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top