Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#368451 - 02/03/2017 00:47 An Excel puzzle
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5539
Loc: Ajijic, Mexico
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. blush

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

tanstaafl.


Attachments
94A Hike Root Canal Agave La Floresta Allen Lloyd.XLSX (59 downloads)

_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#368453 - 02/03/2017 05:22 Re: An Excel puzzle [Re: tanstaafl.]
Shonky
pooh-bah

Registered: 12/01/2002
Posts: 2009
Loc: Brisbane, Australia
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.
_________________________
Christian
#40104192 120Gb (no longer in my E36 M3, won't fit the E46 M3)

Top
#368454 - 02/03/2017 05:24 Re: An Excel puzzle [Re: tanstaafl.]
Shonky
pooh-bah

Registered: 12/01/2002
Posts: 2009
Loc: Brisbane, Australia
You want

Code:
=SUMIFS(L5:L24,D5:D24,"=Rest Stop")+SUMIFS(L5:L24,D5:D24,"=Snack Break")+SUMIFS(L5:L24,D5:D24,"=Trail Maintenance")
_________________________
Christian
#40104192 120Gb (no longer in my E36 M3, won't fit the E46 M3)

Top
#368456 - 02/03/2017 13:52 Re: An Excel puzzle [Re: Shonky]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5539
Loc: Ajijic, Mexico
Originally Posted By: Shonky
"Criteria 1 AND Criteria 2 AND Criteria 3" must be true
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.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#368457 - 03/03/2017 02:00 Re: An Excel puzzle [Re: tanstaafl.]
Shonky
pooh-bah

Registered: 12/01/2002
Posts: 2009
Loc: Brisbane, Australia
Yeah SUMIF would be fine too. I didn't think much more than getting it working smile

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:
Quote:
adds all of its arguments that meet multiple criteria


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.
_________________________
Christian
#40104192 120Gb (no longer in my E36 M3, won't fit the E46 M3)

Top