SUMIF by year and also grade

[Dave T]

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:

1. Sum of lead climbing metres for a specific year (column A) with a grade greater than or equal to 16 (column D)
2. Sum of lead climbing metres for a specific year with a grade greater than or equal to 12 and less than 16

Any help or suggestions would be appreciated.

Regards,
Dave T

[Bob Phillips]

Try

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

and so on

[Dave T]

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]

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.