SUMIF of a RANGE NAME BASED ON MONTH

**Big_Tater** · 05-25-2010, 02:28 PM

I have multiple tables of data separated by range names. Each Range contains month/day for the full year Jan 01-Dec 31. Each range also has 8 columns of data along with the date range. Is there a way for me to pull just the monthly sum based on a range name for a certain column?

eg:

RANGE_ONE
01/01/2010 25 20 10
01/02/2010 25 20 10
01/03/2010 25 20 10
01/04/2010 25 20 10
01/05/2010 25 20 10
01/06/2010 25 20 10
01/07/2010 25 20 10

On Sheet2! I would like to maybe have a cell where I type in "1" for January, "2"
for February, etc. and the sum of the month would be pulled in for column 2 or 3 or 4 or whichever column I ask to total.

I use this on a different sheet for something else, so I'm wondering if this can be modified to use RANGE NAMES with a vlookup to specify the column or something.
=SUMPRODUCT(--(MONTH($A$4:$A$368)=$B$3),--(C$4:C$368))

Let me know if I need to include a sample sheet.

Thanks for your help.
David

**daddylonglegs** · 05-25-2010, 02:38 PM

You can use INDEX with the range name to get a specific column so for column 4 of RANGE_ONE you can use INDEX(RANGE_ONE,0,4), so if you want to sum column 5 where the date in column 1 is in May

=SUMPRODUCT(--(MONTH(INDEX(RANGE_ONE,0,1))=5),INDEX(RANGE_ONE,0,5))