I have a table of (talk, venue, timeslot) tuples, alongside a column that indicates if that combination should be part of a schedule. Here’s how it looks
| Talk | Venue | Timeslot | Scheduled? |
|---|---|---|---|
| python | Aspen | 9 | |
| python | Aspen | 10 | 1 |
| python | Aspen | 11 | |
| python | Madrona | 9 | 1 |
| … | … | … | … |
| js | Fir | 9 | |
| js | Fir | 10 |
I have a named range for each of the columns, to make it easier to read the formulas. The Scheduled? column is called X.
I’m trying to answer questions like “Is ‘python’ scheduled anywhere at 9am?” using a formula. I have a few constructions that I believe should be equivalent, but only some of them give the right answer.
| Is “python” scheduled anywhere at 9am? | formula |
|---|---|
| 0 | =SUMIF((talk = “python”) * (timeslot = 9), “>= 1”, X) |
| 1 | =SUMIFS(X, talk, “python”, timeslot , 9) |
| 0 | =SUM(X * (talk = “python”) * (timeslot = 9)) |
| 0 | =COUNT(FILTER(X, talk = “python”, timeslot = 9, X)) |
| 1 | =COUNTIFS(talk, “python”, timeslot, 9, X, 1) |
I expect the answer to be 1, which is given by the SUMIFS and COUNTIFS formulas. What am I doing wrong in the others? Or where am I misunderstanding how the formulas work?
Here’s a google sheet with a small dataset reproduction of the problem: SUMIF vs SUMIFS.
