| 1 | package calc |
| 2 | |
| 3 | import ( |
| 4 | "strconv" |
| 5 | "strings" |
| 6 | ) |
| 7 | |
| 8 | // fn_cond.go: SUMIF, COUNTIF, AVERAGEIF and their *IFS plurals. |
| 9 | // |
| 10 | // These register as nodeFuncs for the SHAPE reason registry.go's |
| 11 | // nodeFunc comment gives, and it bites harder here than anywhere else in |
| 12 | // the package. `SUMIF(A1:A9, ">5", B1:B9)` has to pair each cell of the |
| 13 | // criteria range with the cell at the SAME OFFSET in the sum range, and |
| 14 | // a flattened []Value has thrown away both the boundary between the two |
| 15 | // arguments and the width that makes "same offset" mean anything. There |
| 16 | // is no recovering it afterwards. |
| 17 | // |
| 18 | // The pairing is by OFFSET, not by address, which is Excel's rule and |
| 19 | // the one that surprises people: if the sum range is written smaller |
| 20 | // than the criteria range, Excel reads it as though it had the criteria |
| 21 | // range's shape, anchored at its top-left. So SUMIF(A1:A9,">5",B1) sums |
| 22 | // B1:B9. Implemented deliberately rather than inherited — see sumRange. |
| 23 | |
| 24 | func init() { |
| 25 | registerNode("SUMIF", fnSumIf) |
| 26 | registerNode("COUNTIF", fnCountIf) |
| 27 | registerNode("AVERAGEIF", fnAverageIf) |
| 28 | registerNode("SUMIFS", fnSumIfs) |
| 29 | registerNode("COUNTIFS", fnCountIfs) |
| 30 | registerNode("AVERAGEIFS", fnAverageIfs) |
| 31 | } |
| 32 | |
| 33 | // rect is a range's four bounds, in positions. |
| 34 | type rect struct{ r0, r1, c0, c1 int } |
| 35 | |
| 36 | func rectOf(n Node) (rect, bool) { |
| 37 | // A bare cell reference is a 1x1 rectangle, and accepting one is |
| 38 | // what makes the file header's own example true: SUMIF(A1:A9,">5",B1) |
| 39 | // sums B1:B9, because offsetRect then grows this to the criteria |
| 40 | // range's shape. Refusing it — which the first version did, since |
| 41 | // it required a *Range — made that documented behaviour a #VALUE! |
| 42 | // and the comment describing it a lie. Caught by writing the test |
| 43 | // the comment implied. |
| 44 | if ref, ok := n.(*Ref); ok { |
| 45 | return rect{ref.RowPos, ref.RowPos, ref.ColPos, ref.ColPos}, true |
| 46 | } |
| 47 | rng, ok := n.(*Range) |
| 48 | if !ok { |
| 49 | return rect{}, false |
| 50 | } |
| 51 | r0, r1, c0, c1, ok := boundedRangeBounds(rng) |
| 52 | return rect{r0, r1, c0, c1}, ok |
| 53 | } |
| 54 | |
| 55 | // offsetRect reshapes `to` to have `like`'s width and height, anchored |
| 56 | // at its own top-left. This is the Excel rule described in the file |
| 57 | // comment: a sum range is only ever read for its ORIGIN. |
| 58 | func offsetRect(to, like rect) rect { |
| 59 | return rect{ |
| 60 | r0: to.r0, r1: to.r0 + (like.r1 - like.r0), |
| 61 | c0: to.c0, c1: to.c0 + (like.c1 - like.c0), |
| 62 | } |
| 63 | } |
| 64 | |
| 65 | func (x rect) cells() int { return (x.r1 - x.r0 + 1) * (x.c1 - x.c0 + 1) } |
| 66 | |
| 67 | // ---- criteria ------------------------------------------------------- |
| 68 | |
| 69 | // criterion is a parsed Excel criteria argument: an optional comparison |
| 70 | // operator and the value to compare against. |
| 71 | // |
| 72 | // Excel writes these as text — ">5", "<>", "<=2026-01-01", "apple", |
| 73 | // "app*" — with the operator inside the string rather than as syntax. |
| 74 | // A bare value with no operator means equality. |
| 75 | type criterion struct { |
| 76 | op string // "", "=", "<>", ">", "<", ">=", "<=" |
| 77 | num float64 |
| 78 | isNum bool |
| 79 | text string |
| 80 | } |
| 81 | |
| 82 | // parseCriterion reads one criteria argument. |
| 83 | // |
| 84 | // The order of the operator tests matters: ">=" must be tried before |
| 85 | // ">", or ">=5" parses as ">" against the text "=5" and matches |
| 86 | // nothing, silently, for every row. |
| 87 | func parseCriterion(v Value) criterion { |
| 88 | if v.Kind != KindStr { |
| 89 | if v.Kind == KindNum { |
| 90 | return criterion{op: "=", num: v.Num, isNum: true} |
| 91 | } |
| 92 | // A BLANK criterion cell matches nothing. COUNTIF(range, D3) |
| 93 | // where D3 is empty is 0 in LibreOffice and in excelize, and |
| 94 | // the first version answered 1 because it turned the blank into |
| 95 | // the empty-string criterion — which is a different thing, and |
| 96 | // the one written as COUNTIF(range, ""). |
| 97 | if v.Kind == KindEmpty { |
| 98 | return criterion{op: "none"} |
| 99 | } |
| 100 | return criterion{op: "=", text: valueText(v)} |
| 101 | } |
| 102 | s := v.Str |
| 103 | for _, op := range []string{">=", "<=", "<>", ">", "<", "="} { |
| 104 | if strings.HasPrefix(s, op) { |
| 105 | return finishCriterion(op, strings.TrimPrefix(s, op)) |
| 106 | } |
| 107 | } |
| 108 | return finishCriterion("=", s) |
| 109 | } |
| 110 | |
| 111 | func finishCriterion(op, rest string) criterion { |
| 112 | if n, err := strconv.ParseFloat(strings.TrimSpace(rest), 64); err == nil { |
| 113 | return criterion{op: op, num: n, isNum: true} |
| 114 | } |
| 115 | return criterion{op: op, text: rest} |
| 116 | } |
| 117 | |
| 118 | // valueText renders a value the way a criteria comparison sees it. |
| 119 | func valueText(v Value) string { |
| 120 | switch v.Kind { |
| 121 | case KindStr: |
| 122 | return v.Str |
| 123 | case KindNum: |
| 124 | return strconv.FormatFloat(v.Num, 'g', -1, 64) |
| 125 | case KindBool: |
| 126 | if v.Bool { |
| 127 | return "TRUE" |
| 128 | } |
| 129 | return "FALSE" |
| 130 | } |
| 131 | return "" |
| 132 | } |
| 133 | |
| 134 | // matches reports whether one cell satisfies the criterion. |
| 135 | // |
| 136 | // An error cell never matches anything, including "<>" — an error is not |
| 137 | // a value that is unequal to something, it is the absence of one, and |
| 138 | // counting it as "not equal to 5" would quietly inflate every COUNTIF |
| 139 | // over a column with one bad formula in it. |
| 140 | func (c criterion) matches(v Value) bool { |
| 141 | if c.op == "none" { |
| 142 | return false |
| 143 | } |
| 144 | if v.Kind == KindErr { |
| 145 | // An error cell is "not equal to" anything, and only that. |
| 146 | // |
| 147 | // The first version refused every error for every criterion, |
| 148 | // with a rationale — that counting one as not-equal-to-5 would |
| 149 | // inflate a COUNTIF over a column with one bad formula in it — |
| 150 | // that was an opinion rather than a measurement. LibreOffice |
| 151 | // 26.8 counts an error for "<>" and for "<>20", and so does |
| 152 | // Excel. Corrected after review, 2026-09-04. |
| 153 | return c.op == "<>" |
| 154 | } |
| 155 | if c.isNum { |
| 156 | if v.Kind != KindNum { |
| 157 | // A blank is not zero, and text is not a number: only "<>" |
| 158 | // is true of a cell that cannot be compared numerically. |
| 159 | return c.op == "<>" |
| 160 | } |
| 161 | switch c.op { |
| 162 | case "=", "": |
| 163 | return v.Num == c.num |
| 164 | case "<>": |
| 165 | return v.Num != c.num |
| 166 | case ">": |
| 167 | return v.Num > c.num |
| 168 | case "<": |
| 169 | return v.Num < c.num |
| 170 | case ">=": |
| 171 | return v.Num >= c.num |
| 172 | case "<=": |
| 173 | return v.Num <= c.num |
| 174 | } |
| 175 | return false |
| 176 | } |
| 177 | // Text comparison is case-insensitive, as everywhere in Excel, and |
| 178 | // an empty criterion ("=" with nothing after it) means an empty |
| 179 | // cell rather than the empty string. |
| 180 | if c.text == "" && (c.op == "=" || c.op == "") { |
| 181 | return v.Kind == KindEmpty |
| 182 | } |
| 183 | if c.text == "" && c.op == "<>" { |
| 184 | return v.Kind != KindEmpty |
| 185 | } |
| 186 | got := strings.ToUpper(valueText(v)) |
| 187 | want := strings.ToUpper(c.text) |
| 188 | switch c.op { |
| 189 | case "=", "": |
| 190 | return wildcardMatch(want, got) |
| 191 | case "<>": |
| 192 | return !wildcardMatch(want, got) |
| 193 | case ">": |
| 194 | return got > want |
| 195 | case "<": |
| 196 | return got < want |
| 197 | case ">=": |
| 198 | return got >= want |
| 199 | case "<=": |
| 200 | return got <= want |
| 201 | } |
| 202 | return false |
| 203 | } |
| 204 | |
| 205 | // wildcardMatch implements Excel's two wildcards: * for any run and ? |
| 206 | // for exactly one character. A pattern with neither is a plain equality, |
| 207 | // which is the overwhelmingly common case and is checked first so the |
| 208 | // matcher below never runs for it. |
| 209 | func wildcardMatch(pattern, s string) bool { |
| 210 | if !strings.ContainsAny(pattern, "*?") { |
| 211 | return pattern == s |
| 212 | } |
| 213 | return globMatch([]rune(pattern), []rune(s)) |
| 214 | } |
| 215 | |
| 216 | // globMatch is the ordinary two-index backtracking matcher. Recursion on |
| 217 | // '*' would be O(2^n) on a pathological pattern against a long cell, and |
| 218 | // a cell is caller-supplied. |
| 219 | func globMatch(p, s []rune) bool { |
| 220 | var pi, si, star, mark int |
| 221 | star = -1 |
| 222 | for si < len(s) { |
| 223 | switch { |
| 224 | case pi < len(p) && (p[pi] == '?' || p[pi] == s[si]): |
| 225 | pi++ |
| 226 | si++ |
| 227 | case pi < len(p) && p[pi] == '*': |
| 228 | star = pi |
| 229 | mark = si |
| 230 | pi++ |
| 231 | case star >= 0: |
| 232 | pi = star + 1 |
| 233 | mark++ |
| 234 | si = mark |
| 235 | default: |
| 236 | return false |
| 237 | } |
| 238 | } |
| 239 | for pi < len(p) && p[pi] == '*' { |
| 240 | pi++ |
| 241 | } |
| 242 | return pi == len(p) |
| 243 | } |
| 244 | |
amadan