PDA

View Full Version : SUMIF by year and also grade



Dave T
05-05-2011, 11:22 PM
Hello All,

I am trying to get my head around how to do this...

I keep my rock climbing records in a spreadsheet and I want to produce a summary page of climbs I have done for a specific year and within a grade range. i.e.

I have a table layout as follows...
Date | Area | Climb Name | Grade
1/10/2010 | | | 15
1/10/2010 | | | 12

Formula results:

Sum of lead climbing metres for a specific year (column A) with a grade greater than or equal to 16 (column D)
Sum of lead climbing metres for a specific year with a grade greater than or equal to 12 and less than 16Any help or suggestions would be appreciated.

Regards,
Dave T

Bob Phillips
05-06-2011, 01:42 AM
Try

=SUMPRODUCT(--(YEAR(date_range)=2010),--(grade_range>=16),climb_range)

and so on

Dave T
05-06-2011, 07:49 AM
Hello xld,

Thanks for the reply.

I was going to use dynamic named ranges, so thank you for that, but I am having a hard time getting my head around your formula.

Am I correct in assuming the following:
Column A = date_range
Column D = grade_range
What column would be the climb_range??

Regards,
Dave T

Bob Phillips
05-06-2011, 07:59 AM
I don't know Dave, it is your workbook and I haven't seen it, but climb_range would be the cells with the metres climbed values.