UKC

So the only reason I'm posting here is that's its kind of related to climbing, but I guess i'll try and find an excel forum somewhere else too.

I've got a really elaborate spreadsheet for tracking all my climbing, with lots of bells and whistles and so on. I've got a graph that is built by counting the numbers, e.g. 10 6a's, but obviously this can't determine whether I actually finished the climb or not, so I want to use a countif(+and?) function that would work like this:

Count numbers of a particular grade in one column, and then check the next column if it met any of a range of conditions "lead, toprope, second, boulder, whatever" and then include it in the final tally if it did. If it didn't meet those conditions (e.g DNF did not finish, or hangdogged) then it wouldn't be included.

I hope that makes sense :P

the formula I'm using is: =SUM(COUNTIF('Outdoor climbing'!D,{"HVS***"})) for example.

Sachi

In reply to jsmcfarland:

If your excel is after 2007 then you need to look at the countifs function, note extra s at the end. This is a multi criteria countif that allows you to look in multiple ranges for criteria.

In older versions a pivot table may be best as you can end up creating an array formula of the form


Sum(if(range1=CRITERIA1,If(range=criteria2,1,0),0))
This needs to be entered with ctrl,shift and enter together, if you need to add extra tests then add another if statement where the 1is.

If you can use the countifs!

In reply to DaveN:

Thanks for replying, I found what I was looking for elsewhere and after some fiddling adapted it to my needs

=SUM(COUNTIFS(E31:E35,{"lead","toprope","boulder","solo" , "second"},D3135,"6a")) - Anyway this bit of code would scan column D for the route grade 6a, and if it had been lead, toproped, bouldered, solo'd or seconded then it would count it towards the total of 6a routes I had freed, and exclude dogged or unfinished climbs.

Pretty much the same as yours except written differently I think, though excel is just a nightmare sometimes. I don't think I will ever understand the logic of it intuitively, once i see an example of something that is the same situation as mine it's fine but I just can't do it on the fly. Grrr.

even though it drives me crazy, I love excel.

In reply to jsmcfarland:

I know you already got your answer, but fwiw I would have split the if and the count.

Just a different way of looking at it.

In reply to jsmcfarland:

If you've got the opportunity, why not experiment with Access?

Takes a bit more getting into than Excel, but it's the proper tool for the job and the skills you would learn would not be product specific - they would apply to any relational database you might encounter in the future.