# SUMIF: Need formula to use month to determine sum_range

1. ## SUMIF: Need formula to use month to determine sum_range

So I need the sum_range part of the function to reference a column depending on the month. So, for example, I want a formula that will make the sum_range part of a SUMIF equal column L (12th column) if cell A1 is 12/31/2013 (the 12th month).

Thanks!  Register To Reply

2. ## Re: SUMIF: Need formula to use month to determine sum_range

there are a couple of ways to do it. Yet it would be helpful if you assemble a sample and the desired result  Register To Reply

3. ## Re: SUMIF: Need formula to use month to determine sum_range

Hmm I am having trouble uploading a table or image but I'll do my best. Say the date in A1 is 6/30/2013 and I am doing a formula on the below data set:

A 1 2 3 4 5
A 10 11 12 13 14
B 20 21 22 23 24
D 30 31 32 33 34
D 40 41 42 43 44

I would want SUMIF(A:A,"D", ? ) to equal 78. If the date in A1 were 5/31/2013, I would want the result to be 76, etc.  Register To Reply

4. ## Re: SUMIF: Need formula to use month to determine sum_range  Register To Reply

5. ## Re: SUMIF: Need formula to use month to determine sum_range

could you attach an excel file with the data?  Register To Reply

6. ## Re: SUMIF: Need formula to use month to determine sum_range

Try this...

Data Range
 A B C D E F 1 5/31/2013 76 2 ----------- ----------- ----------- ----------- ----------- ----------- 3 A 1 2 3 4 5 4 A 10 11 12 13 14 5 B 20 21 22 23 24 6 D 30 31 32 33 34 7 D 40 41 42 43 44

Formula in B1:

=SUMIF(A3:A7,"D",INDEX(B3:F7,,MONTH(A1)-1))  Register To Reply

7. ## Re: SUMIF: Need formula to use month to determine sum_range

Thanks for the help all, turns out an Offset was the way to go rather than a sumif.  Register To Reply

8. ## Re: SUMIF: Need formula to use month to determine sum_range

Good deal. Thanks for the feedback! In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.  Register To Reply