Can someone who knows Excel better than I do tell me why the formula in cell M2 does not work? It is supposed to sum up the values in column L where the corresponding cell in column D says either "Rest Stop" or "Snack Break" or "Trail Maintenance". (The cells it should sum are highlighted in blue in column L.)

The value in cell M2 should be 0.11, not 0.00.

I won't bore you with trying to explain what this spreadsheet does, other than to say that in combination with my Master Database it allows me to preview a hike and have a very close idea of expected elapsed time, total distance traveled, and total elevation gain. The cells highlighted in column L are to compensate for what I call odometer creep: stand perfectly still with your handheld GPS and it will accumulate distance at about 13 feet per minute as satellites come into and out of range. There is no averaging out of plus/minus because distance traveled is cumulative as an absolute value.

Oh, and the reason the "Difference" column is so far off of expectations at the beginning of the hike is because I got involved taking my notes for the Master Database and walked off without my hiking poles for the first leg of the hike and had to rush back and get them and then go like hell to catch the group.

Anyway, that's neither here nor there. The question is: Why doesn't M2 work properly?

tanstaafl.

Because SUMIFS requires all criteria to be true. i.e.

"Criteria 1 AND Criteria 2 AND Criteria 3" must be true

This is impossible because each cell in D5:D24 can't be three different things at once.

You want

Christian -- once again you have bailed me out. This is at least the third time you have come to my rescue. The last time was to "help" (you wrote the whole thing!) with an Excel macro to reformat the comments in the book title cells of my library index.

It just wouldn't have occurred to me that the SUMIFS criteria were "AND" and not "OR", but now that you point it out, it is obvious why this must be so, otherwise the function would be of limited usefulness.

That being the case, I could just as well use the simpler "SUMIF" function and daisy-chain them like you did with the SUMIFS in your example formula. At least I think I could.

Thank you once again for your help. Just when I think I am pretty good with Excel, someone like you comes along and points out my limitations! Did you have to think about it at all, or is this something you just knew about the SUMIFS function?

tanstaafl.

Yeah SUMIF would be fine too. I didn't think much more than getting it working

I didn't *know* it straight away but simply removed all but the first criteria to see if that worked. Then I figured it must be an AND. I'm a programmer by trade so things like this make sense to me and so come to me fairly easily. I use various bits of Excel a lot but it's not my #1 tool by any means.

Also I did finally check at the help here:
https://support.office.com/en-us/article/SUMIFS-function-c9e748f5-7ea7-455d-9406-611cebce642b
says:


I wouldn't call that definitive - it could do with a "simultaneously" or "all" or similar in the initial description. The examples do mention meeting "both" conditions.

I do think an OR version would be useful in some cases (yours is clearly one) but there's obviously workarounds which don't increase complexity very much.