formualizer_eval/builtins/math/
criteria_aggregates.rs

1use super::super::utils::{ARG_ANY_ONE, coerce_num, criteria_match};
2use crate::args::ArgSchema;
3use crate::compute_prelude::{boolean, cmp, filter_array};
4use crate::function::Function;
5use crate::traits::{ArgumentHandle, FunctionContext};
6use arrow::compute::kernels::aggregate::sum_array;
7use arrow_array::types::Float64Type;
8use arrow_array::{Array as _, BooleanArray, Float64Array};
9use formualizer_common::{ExcelError, LiteralValue};
10use formualizer_macros::func_caps;
11
12#[cfg(test)]
13pub(crate) mod test_hooks {
14    use std::cell::Cell;
15
16    thread_local! {
17        static CACHED_MASK_SLICE_FAST: Cell<usize> = const { Cell::new(0) };
18        static CACHED_MASK_PAD_PARTIAL: Cell<usize> = const { Cell::new(0) };
19        static CACHED_MASK_PAD_ALL_FILL: Cell<usize> = const { Cell::new(0) };
20    }
21
22    pub fn reset_cached_mask_counters() {
23        CACHED_MASK_SLICE_FAST.with(|c| c.set(0));
24        CACHED_MASK_PAD_PARTIAL.with(|c| c.set(0));
25        CACHED_MASK_PAD_ALL_FILL.with(|c| c.set(0));
26    }
27
28    pub fn cached_mask_counters() -> (usize, usize, usize) {
29        let a = CACHED_MASK_SLICE_FAST.with(|c| c.get());
30        let b = CACHED_MASK_PAD_PARTIAL.with(|c| c.get());
31        let d = CACHED_MASK_PAD_ALL_FILL.with(|c| c.get());
32        (a, b, d)
33    }
34
35    pub(crate) fn inc_slice_fast() {
36        CACHED_MASK_SLICE_FAST.with(|c| c.set(c.get() + 1));
37    }
38    pub(crate) fn inc_pad_partial() {
39        CACHED_MASK_PAD_PARTIAL.with(|c| c.set(c.get() + 1));
40    }
41    pub(crate) fn inc_pad_all_fill() {
42        CACHED_MASK_PAD_ALL_FILL.with(|c| c.set(c.get() + 1));
43    }
44}
45
46/*
47Criteria-driven aggregation functions:
48  - SUMIF(range, criteria, [sum_range])
49  - SUMIFS(sum_range, criteria_range1, criteria1, ...)
50  - COUNTIF(range, criteria)
51  - COUNTIFS(criteria_range1, criteria1, ...)
52  - AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)  (moved here from aggregate.rs)
53  - COUNTA(value1, value2, ...)
54  - COUNTBLANK(range_or_values...)
55
56Design notes:
57  * Validation of shape parity for multi-criteria aggregations (#VALUE! on mismatch).
58  * Criteria parsing reused via crate::args::parse_criteria and criteria_match helper in utils.
59  * Streaming optimization deferred (TODO(perf)).
60*/
61
62#[derive(Debug, Clone, Copy, PartialEq, Eq)]
63enum AggregationType {
64    Sum,
65    Count,
66    Average,
67}
68
69fn eval_if_family<'a, 'b>(
70    args: &[ArgumentHandle<'a, 'b>],
71    ctx: &dyn FunctionContext<'b>,
72    agg_type: AggregationType,
73    multi: bool,
74) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
75    let mut sum_view: Option<crate::engine::range_view::RangeView<'_>> = None;
76    let mut sum_scalar: Option<LiteralValue> = None;
77    let mut crit_specs = Vec::new();
78
79    if !multi {
80        // Single criterion: IF(range, criteria, [target_range])
81        if args.len() < 2 || args.len() > 3 {
82            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
83                ExcelError::new_value().with_message(format!(
84                    "Function expects 2 or 3 arguments, got {}",
85                    args.len()
86                )),
87            )));
88        }
89        let pred = crate::args::parse_criteria(&args[1].value()?.into_literal())?;
90        let crit_rv = args[0].range_view().ok();
91        let crit_val = if crit_rv.is_none() {
92            Some(args[0].value()?.into_literal())
93        } else {
94            None
95        };
96        crit_specs.push((crit_rv, pred, crit_val));
97
98        if agg_type != AggregationType::Count {
99            if args.len() == 3 {
100                if let Ok(v) = args[2].range_view() {
101                    let crit_dims = crit_specs[0].0.as_ref().map(|v| v.dims()).unwrap_or((1, 1));
102                    sum_view = Some(v.expand_to(crit_dims.0, crit_dims.1));
103                } else {
104                    sum_scalar = Some(args[2].value()?.into_literal());
105                }
106            } else {
107                // Default target is criteria range
108                if let Ok(v) = args[0].range_view() {
109                    sum_view = Some(v);
110                } else {
111                    sum_scalar = Some(args[0].value()?.into_literal());
112                }
113            }
114        }
115    } else {
116        // Multi criteria: IFS(target_range, crit_range1, crit1, ...) or COUNTIFS(crit_range1, crit1, ...)
117        if agg_type == AggregationType::Count {
118            if args.len() < 2 || !args.len().is_multiple_of(2) {
119                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
120                    ExcelError::new_value().with_message(format!(
121                        "COUNTIFS expects N pairs (criteria_range, criteria); got {} args",
122                        args.len()
123                    )),
124                )));
125            }
126            for i in (0..args.len()).step_by(2) {
127                let mut rv = args[i].range_view().ok();
128                let mut val: Option<LiteralValue> = None;
129
130                // Broadcast semantics: treat 1x1 criteria ranges as scalar criteria.
131                if let Some(ref view) = rv {
132                    let (r, c) = view.dims();
133                    if r == 1 && c == 1 {
134                        val = Some(view.as_1x1().unwrap_or(LiteralValue::Empty));
135                        rv = None;
136                    }
137                }
138
139                if val.is_none() && rv.is_none() {
140                    val = Some(args[i].value()?.into_literal());
141                }
142
143                let pred = crate::args::parse_criteria(&args[i + 1].value()?.into_literal())?;
144                crit_specs.push((rv, pred, val));
145            }
146        } else {
147            if args.len() < 3 || !(args.len() - 1).is_multiple_of(2) {
148                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
149                    ExcelError::new_value().with_message(format!(
150                        "Function expects 1 target_range followed by N pairs (criteria_range, criteria); got {} args",
151                        args.len()
152                    )),
153                )));
154            }
155            if let Ok(v) = args[0].range_view() {
156                sum_view = Some(v);
157            } else {
158                sum_scalar = Some(args[0].value()?.into_literal());
159            }
160            for i in (1..args.len()).step_by(2) {
161                let mut rv = args[i].range_view().ok();
162                let mut val: Option<LiteralValue> = None;
163
164                // Broadcast semantics: treat 1x1 criteria ranges as scalar criteria.
165                if let Some(ref view) = rv {
166                    let (r, c) = view.dims();
167                    if r == 1 && c == 1 {
168                        val = Some(view.as_1x1().unwrap_or(LiteralValue::Empty));
169                        rv = None;
170                    }
171                }
172
173                if val.is_none() && rv.is_none() {
174                    val = Some(args[i].value()?.into_literal());
175                }
176
177                let pred = crate::args::parse_criteria(&args[i + 1].value()?.into_literal())?;
178                crit_specs.push((rv, pred, val));
179            }
180        }
181    }
182
183    // Determine union dimensions
184    let mut dims = (1usize, 1usize);
185    if let Some(ref sv) = sum_view {
186        dims = sv.dims();
187    }
188    for (rv, _, _) in &crit_specs {
189        if let Some(v) = rv {
190            let vd = v.dims();
191            dims.0 = dims.0.max(vd.0);
192            dims.1 = dims.1.max(vd.1);
193        }
194    }
195
196    // Excel SUMIF rules: if target_range is given, it expands from its top-left to match criteria range dims
197    // SUMIFS rules: all ranges must have same dims.
198    // Our implementation will use dims as the iteration space and broadcast/pad.
199
200    let mut total_sum = 0.0f64;
201    let mut total_count = 0i64;
202
203    // Use a driver view for chunked iteration. Prefer sum_view, else first criteria range.
204    let driver = sum_view
205        .as_ref()
206        .or_else(|| crit_specs.iter().find_map(|(rv, _, _)| rv.as_ref()));
207
208    if let Some(drv) = driver {
209        // We can't easily iterate over union dims if they are larger than driver.
210        // But for most cases they are same.
211        // If driver is smaller, we'll miss some rows.
212        // Actually, if it's SUMIF, we want to iterate over criteria range dims.
213        let driver = if !multi && crit_specs[0].0.is_some() {
214            crit_specs[0].0.as_ref().unwrap()
215        } else {
216            drv
217        };
218
219        for res in driver.iter_row_chunks() {
220            let cs = res?;
221            let row_start = cs.row_start;
222            let row_len = cs.row_len;
223            if row_len == 0 {
224                continue;
225            }
226
227            // Get slices for all criteria and sum range
228            let mut crit_num_slices = Vec::with_capacity(crit_specs.len());
229            let mut crit_text_slices = Vec::with_capacity(crit_specs.len());
230            for (rv, _, _) in &crit_specs {
231                if let Some(v) = rv {
232                    crit_num_slices.push(Some(v.slice_numbers(row_start, row_len)));
233                    crit_text_slices.push(Some(v.slice_lowered_text(row_start, row_len)));
234                } else {
235                    crit_num_slices.push(None);
236                    crit_text_slices.push(None);
237                }
238            }
239
240            let sum_slices = sum_view
241                .as_ref()
242                .map(|v| v.slice_numbers(row_start, row_len));
243
244            for c in 0..dims.1 {
245                let mut mask_opt: Option<BooleanArray> = None;
246                let mut impossible = false;
247
248                for (j, (_, pred, scalar_val)) in crit_specs.iter().enumerate() {
249                    if crit_specs[j].0.is_none() {
250                        if let Some(sv) = scalar_val {
251                            if !criteria_match(pred, sv) {
252                                impossible = true;
253                                break;
254                            }
255                            continue;
256                        }
257                        if !criteria_match(pred, &LiteralValue::Empty) {
258                            impossible = true;
259                            break;
260                        }
261                        continue;
262                    }
263
264                    // Try cache
265                    let cur_cached = if let Some(ref view) = crit_specs[j].0 {
266                        ctx.get_criteria_mask(view, c, pred).map(|m| {
267                            let fill = criteria_match(pred, &LiteralValue::Empty);
268                            let m_len = m.len();
269
270                            // The cached mask may be shorter than the current driver's chunk
271                            // (e.g., whole-column references trimmed to different used-regions).
272                            // Treat out-of-bounds rows as Empty cells.
273                            if row_start + row_len <= m_len {
274                                #[cfg(test)]
275                                test_hooks::inc_slice_fast();
276                                let sl = m.slice(row_start, row_len);
277                                return sl
278                                    .as_any()
279                                    .downcast_ref::<arrow_array::BooleanArray>()
280                                    .expect("cached criteria mask slice downcast")
281                                    .clone();
282                            }
283
284                            let mut bb =
285                                arrow_array::builder::BooleanBuilder::with_capacity(row_len);
286                            if row_start < m_len {
287                                #[cfg(test)]
288                                test_hooks::inc_pad_partial();
289                                let take_len = row_len.min(m_len - row_start);
290                                let sl = m.slice(row_start, take_len);
291                                let ba = sl
292                                    .as_any()
293                                    .downcast_ref::<arrow_array::BooleanArray>()
294                                    .expect("cached criteria mask slice downcast");
295                                bb.append_array(ba);
296                                bb.append_n(row_len - take_len, fill);
297                            } else {
298                                #[cfg(test)]
299                                test_hooks::inc_pad_all_fill();
300                                bb.append_n(row_len, fill);
301                            }
302
303                            bb.finish()
304                        })
305                    } else {
306                        None
307                    };
308
309                    if let Some(cm) = cur_cached {
310                        mask_opt = Some(match mask_opt {
311                            None => cm,
312                            Some(prev) => boolean::and_kleene(&prev, &cm).unwrap(),
313                        });
314                        continue;
315                    }
316
317                    // Compute mask for this chunk
318                    let num_col = crit_num_slices[j]
319                        .as_ref()
320                        .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
321                    let text_col = crit_text_slices[j]
322                        .as_ref()
323                        .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
324
325                    let m = match (pred, num_col, text_col) {
326                        (crate::args::CriteriaPredicate::Gt(n), Some(nc), _) => {
327                            cmp::gt(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
328                        }
329                        (crate::args::CriteriaPredicate::Ge(n), Some(nc), _) => {
330                            cmp::gt_eq(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
331                        }
332                        (crate::args::CriteriaPredicate::Lt(n), Some(nc), _) => {
333                            cmp::lt(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
334                        }
335                        (crate::args::CriteriaPredicate::Le(n), Some(nc), _) => {
336                            cmp::lt_eq(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
337                        }
338                        (crate::args::CriteriaPredicate::Eq(v), nc, tc) => {
339                            match v {
340                                LiteralValue::Number(x) => {
341                                    let nx = *x;
342                                    if let Some(nc) = nc {
343                                        let m0 =
344                                            cmp::eq(nc.as_ref(), &Float64Array::new_scalar(nx))
345                                                .unwrap();
346                                        if m0.null_count() == 0 {
347                                            m0
348                                        } else {
349                                            // Fill nulls using per-cell matching so blanks can still match numeric
350                                            // criteria (e.g. blank == 0 in Excel criteria semantics).
351                                            let view = crit_specs[j].0.as_ref().unwrap();
352                                            let mut bb =
353                                                arrow_array::builder::BooleanBuilder::with_capacity(
354                                                    row_len,
355                                                );
356                                            for i in 0..row_len {
357                                                if m0.is_valid(i) {
358                                                    bb.append_value(m0.value(i));
359                                                } else {
360                                                    bb.append_value(criteria_match(
361                                                        pred,
362                                                        &view.get_cell(row_start + i, c),
363                                                    ));
364                                                }
365                                            }
366                                            bb.finish()
367                                        }
368                                    } else {
369                                        // If the criteria range has no numeric fast-path column (e.g. text column
370                                        // or mixed types), fall back to per-cell matching so numeric criteria can
371                                        // still match blanks / numeric text values (Excel semantics).
372                                        let mut bb =
373                                            arrow_array::builder::BooleanBuilder::with_capacity(
374                                                row_len,
375                                            );
376                                        let view = crit_specs[j].0.as_ref().unwrap();
377                                        for i in 0..row_len {
378                                            bb.append_value(criteria_match(
379                                                pred,
380                                                &view.get_cell(row_start + i, c),
381                                            ));
382                                        }
383                                        bb.finish()
384                                    }
385                                }
386                                LiteralValue::Int(x) => {
387                                    let nx = *x as f64;
388                                    if let Some(nc) = nc {
389                                        let m0 =
390                                            cmp::eq(nc.as_ref(), &Float64Array::new_scalar(nx))
391                                                .unwrap();
392                                        if m0.null_count() == 0 {
393                                            m0
394                                        } else {
395                                            let view = crit_specs[j].0.as_ref().unwrap();
396                                            let mut bb =
397                                                arrow_array::builder::BooleanBuilder::with_capacity(
398                                                    row_len,
399                                                );
400                                            for i in 0..row_len {
401                                                if m0.is_valid(i) {
402                                                    bb.append_value(m0.value(i));
403                                                } else {
404                                                    bb.append_value(criteria_match(
405                                                        pred,
406                                                        &view.get_cell(row_start + i, c),
407                                                    ));
408                                                }
409                                            }
410                                            bb.finish()
411                                        }
412                                    } else {
413                                        let mut bb =
414                                            arrow_array::builder::BooleanBuilder::with_capacity(
415                                                row_len,
416                                            );
417                                        let view = crit_specs[j].0.as_ref().unwrap();
418                                        for i in 0..row_len {
419                                            bb.append_value(criteria_match(
420                                                pred,
421                                                &view.get_cell(row_start + i, c),
422                                            ));
423                                        }
424                                        bb.finish()
425                                    }
426                                }
427                                _ => {
428                                    // Use fallback for text and other types to ensure Excel parity (e.g. blank matching)
429                                    let mut bb =
430                                        arrow_array::builder::BooleanBuilder::with_capacity(
431                                            row_len,
432                                        );
433                                    let view = crit_specs[j].0.as_ref().unwrap();
434                                    for i in 0..row_len {
435                                        bb.append_value(criteria_match(
436                                            pred,
437                                            &view.get_cell(row_start + i, c),
438                                        ));
439                                    }
440                                    bb.finish()
441                                }
442                            }
443                        }
444                        (crate::args::CriteriaPredicate::Ne(v), nc, tc) => match v {
445                            LiteralValue::Number(x) => {
446                                let nx = *x;
447                                if let Some(nc) = nc {
448                                    let m0 = cmp::neq(nc.as_ref(), &Float64Array::new_scalar(nx))
449                                        .unwrap();
450                                    if m0.null_count() == 0 {
451                                        m0
452                                    } else {
453                                        let view = crit_specs[j].0.as_ref().unwrap();
454                                        let mut bb =
455                                            arrow_array::builder::BooleanBuilder::with_capacity(
456                                                row_len,
457                                            );
458                                        for i in 0..row_len {
459                                            if m0.is_valid(i) {
460                                                bb.append_value(m0.value(i));
461                                            } else {
462                                                bb.append_value(criteria_match(
463                                                    pred,
464                                                    &view.get_cell(row_start + i, c),
465                                                ));
466                                            }
467                                        }
468                                        bb.finish()
469                                    }
470                                } else {
471                                    let mut bb =
472                                        arrow_array::builder::BooleanBuilder::with_capacity(
473                                            row_len,
474                                        );
475                                    let view = crit_specs[j].0.as_ref().unwrap();
476                                    for i in 0..row_len {
477                                        bb.append_value(criteria_match(
478                                            pred,
479                                            &view.get_cell(row_start + i, c),
480                                        ));
481                                    }
482                                    bb.finish()
483                                }
484                            }
485                            LiteralValue::Int(x) => {
486                                let nx = *x as f64;
487                                if let Some(nc) = nc {
488                                    let m0 = cmp::neq(nc.as_ref(), &Float64Array::new_scalar(nx))
489                                        .unwrap();
490                                    if m0.null_count() == 0 {
491                                        m0
492                                    } else {
493                                        let view = crit_specs[j].0.as_ref().unwrap();
494                                        let mut bb =
495                                            arrow_array::builder::BooleanBuilder::with_capacity(
496                                                row_len,
497                                            );
498                                        for i in 0..row_len {
499                                            if m0.is_valid(i) {
500                                                bb.append_value(m0.value(i));
501                                            } else {
502                                                bb.append_value(criteria_match(
503                                                    pred,
504                                                    &view.get_cell(row_start + i, c),
505                                                ));
506                                            }
507                                        }
508                                        bb.finish()
509                                    }
510                                } else {
511                                    let mut bb =
512                                        arrow_array::builder::BooleanBuilder::with_capacity(
513                                            row_len,
514                                        );
515                                    let view = crit_specs[j].0.as_ref().unwrap();
516                                    for i in 0..row_len {
517                                        bb.append_value(criteria_match(
518                                            pred,
519                                            &view.get_cell(row_start + i, c),
520                                        ));
521                                    }
522                                    bb.finish()
523                                }
524                            }
525                            _ => {
526                                let mut bb =
527                                    arrow_array::builder::BooleanBuilder::with_capacity(row_len);
528                                let view = crit_specs[j].0.as_ref().unwrap();
529                                for i in 0..row_len {
530                                    bb.append_value(criteria_match(
531                                        pred,
532                                        &view.get_cell(row_start + i, c),
533                                    ));
534                                }
535                                bb.finish()
536                            }
537                        },
538                        (crate::args::CriteriaPredicate::TextLike { .. }, _, _) => {
539                            let mut bb =
540                                arrow_array::builder::BooleanBuilder::with_capacity(row_len);
541                            let view = crit_specs[j].0.as_ref().unwrap();
542                            for i in 0..row_len {
543                                bb.append_value(criteria_match(
544                                    pred,
545                                    &view.get_cell(row_start + i, c),
546                                ));
547                            }
548                            bb.finish()
549                        }
550                        _ => {
551                            // Fallback for any other case
552                            let mut bb =
553                                arrow_array::builder::BooleanBuilder::with_capacity(row_len);
554                            if let Some(ref view) = crit_specs[j].0 {
555                                for i in 0..row_len {
556                                    bb.append_value(criteria_match(
557                                        pred,
558                                        &view.get_cell(row_start + i, c),
559                                    ));
560                                }
561                            } else {
562                                let val = scalar_val.as_ref().unwrap_or(&LiteralValue::Empty);
563                                let matches = criteria_match(pred, val);
564                                for _ in 0..row_len {
565                                    bb.append_value(matches);
566                                }
567                            }
568                            bb.finish()
569                        }
570                    };
571
572                    mask_opt = Some(match mask_opt {
573                        None => m,
574                        Some(prev) => boolean::and_kleene(&prev, &m).unwrap(),
575                    });
576                }
577
578                if impossible {
579                    continue;
580                }
581
582                match mask_opt {
583                    Some(mask) => {
584                        if agg_type == AggregationType::Count {
585                            total_count += (0..mask.len())
586                                .filter(|&i| mask.is_valid(i) && mask.value(i))
587                                .count() as i64;
588                        } else {
589                            let target_col = sum_slices
590                                .as_ref()
591                                .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
592                            if let Some(tc) = target_col {
593                                let filtered = filter_array(tc.as_ref(), &mask).unwrap();
594                                let f64_arr =
595                                    filtered.as_any().downcast_ref::<Float64Array>().unwrap();
596                                if let Some(s) = sum_array::<Float64Type, _>(f64_arr) {
597                                    total_sum += s;
598                                }
599                                total_count += f64_arr.len() as i64 - f64_arr.null_count() as i64;
600                            } else if let Some(ref s) = sum_scalar
601                                && let Ok(n) = coerce_num(s)
602                            {
603                                let count = (0..mask.len())
604                                    .filter(|&i| mask.is_valid(i) && mask.value(i))
605                                    .count() as i64;
606                                total_sum += n * count as f64;
607                                total_count += count;
608                            }
609                        }
610                    }
611                    None => {
612                        // No masks: everything matches
613                        if agg_type == AggregationType::Count {
614                            total_count += row_len as i64;
615                        } else {
616                            let target_col = sum_slices
617                                .as_ref()
618                                .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
619                            if let Some(tc) = target_col {
620                                if let Some(s) = sum_array::<Float64Type, _>(tc.as_ref()) {
621                                    total_sum += s;
622                                }
623                                total_count += tc.len() as i64 - tc.null_count() as i64;
624                            } else if let Some(ref s) = sum_scalar
625                                && let Ok(n) = coerce_num(s)
626                            {
627                                total_sum += n * row_len as f64;
628                                total_count += row_len as i64;
629                            }
630                        }
631                    }
632                }
633            }
634        }
635    } else {
636        // Scalar driver fallback
637        let mut all_match = true;
638        for (_, pred, scalar_val) in &crit_specs {
639            let val = scalar_val.as_ref().unwrap_or(&LiteralValue::Empty);
640            if !criteria_match(pred, val) {
641                all_match = false;
642                break;
643            }
644        }
645        if all_match {
646            if agg_type == AggregationType::Count {
647                total_count = (dims.0 * dims.1) as i64;
648            } else if let Some(ref s) = sum_scalar
649                && let Ok(n) = coerce_num(s)
650            {
651                total_sum = n * (dims.0 * dims.1) as f64;
652                total_count = (dims.0 * dims.1) as i64;
653            }
654        }
655    }
656
657    match agg_type {
658        AggregationType::Sum => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
659            total_sum,
660        ))),
661        AggregationType::Count => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
662            total_count as f64,
663        ))),
664        AggregationType::Average => {
665            if total_count == 0 {
666                Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
667                    ExcelError::new_div(),
668                )))
669            } else {
670                Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
671                    total_sum / total_count as f64,
672                )))
673            }
674        }
675    }
676}
677
678/* ─────────────────────────── AVERAGEIF() ──────────────────────────── */
679#[derive(Debug)]
680pub struct AverageIfFn;
681impl Function for AverageIfFn {
682    func_caps!(
683        PURE,
684        REDUCTION,
685        WINDOWED,
686        STREAM_OK,
687        PARALLEL_ARGS,
688        PARALLEL_CHUNKS
689    );
690    fn name(&self) -> &'static str {
691        "AVERAGEIF"
692    }
693    fn min_args(&self) -> usize {
694        2
695    }
696    fn variadic(&self) -> bool {
697        true
698    }
699    fn arg_schema(&self) -> &'static [ArgSchema] {
700        &ARG_ANY_ONE[..]
701    }
702    fn eval<'a, 'b, 'c>(
703        &self,
704        args: &'c [ArgumentHandle<'a, 'b>],
705        ctx: &dyn FunctionContext<'b>,
706    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
707        eval_if_family(args, ctx, AggregationType::Average, false)
708    }
709}
710
711/* ─────────────────────────── SUMIF() ──────────────────────────── */
712#[derive(Debug)]
713pub struct SumIfFn;
714impl Function for SumIfFn {
715    func_caps!(
716        PURE,
717        REDUCTION,
718        WINDOWED,
719        STREAM_OK,
720        PARALLEL_ARGS,
721        PARALLEL_CHUNKS
722    );
723    fn name(&self) -> &'static str {
724        "SUMIF"
725    }
726    fn min_args(&self) -> usize {
727        2
728    }
729    fn variadic(&self) -> bool {
730        true
731    }
732    fn arg_schema(&self) -> &'static [ArgSchema] {
733        &ARG_ANY_ONE[..]
734    }
735    fn eval<'a, 'b, 'c>(
736        &self,
737        args: &'c [ArgumentHandle<'a, 'b>],
738        ctx: &dyn FunctionContext<'b>,
739    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
740        eval_if_family(args, ctx, AggregationType::Sum, false)
741    }
742}
743
744/* ─────────────────────────── COUNTIF() ──────────────────────────── */
745#[derive(Debug)]
746pub struct CountIfFn;
747impl Function for CountIfFn {
748    func_caps!(
749        PURE,
750        REDUCTION,
751        WINDOWED,
752        STREAM_OK,
753        PARALLEL_ARGS,
754        PARALLEL_CHUNKS
755    );
756    fn name(&self) -> &'static str {
757        "COUNTIF"
758    }
759    fn min_args(&self) -> usize {
760        2
761    }
762    fn variadic(&self) -> bool {
763        false
764    }
765    fn arg_schema(&self) -> &'static [ArgSchema] {
766        &ARG_ANY_ONE[..]
767    }
768    fn eval<'a, 'b, 'c>(
769        &self,
770        args: &'c [ArgumentHandle<'a, 'b>],
771        ctx: &dyn FunctionContext<'b>,
772    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
773        eval_if_family(args, ctx, AggregationType::Count, false)
774    }
775}
776
777/* ─────────────────────────── SUMIFS() ──────────────────────────── */
778#[derive(Debug)]
779pub struct SumIfsFn; // SUMIFS(sum_range, criteria_range1, criteria1, ...)
780impl Function for SumIfsFn {
781    func_caps!(
782        PURE,
783        REDUCTION,
784        WINDOWED,
785        STREAM_OK,
786        PARALLEL_ARGS,
787        PARALLEL_CHUNKS
788    );
789    fn name(&self) -> &'static str {
790        "SUMIFS"
791    }
792    fn min_args(&self) -> usize {
793        3
794    }
795    fn variadic(&self) -> bool {
796        true
797    }
798    fn arg_schema(&self) -> &'static [ArgSchema] {
799        &ARG_ANY_ONE[..]
800    }
801    fn eval<'a, 'b, 'c>(
802        &self,
803        args: &'c [ArgumentHandle<'a, 'b>],
804        ctx: &dyn FunctionContext<'b>,
805    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
806        eval_if_family(args, ctx, AggregationType::Sum, true)
807    }
808}
809
810/* ─────────────────────────── COUNTIFS() ──────────────────────────── */
811#[derive(Debug)]
812pub struct CountIfsFn; // COUNTIFS(criteria_range1, criteria1, ...)
813impl Function for CountIfsFn {
814    func_caps!(
815        PURE,
816        REDUCTION,
817        WINDOWED,
818        STREAM_OK,
819        PARALLEL_ARGS,
820        PARALLEL_CHUNKS
821    );
822    fn name(&self) -> &'static str {
823        "COUNTIFS"
824    }
825    fn min_args(&self) -> usize {
826        2
827    }
828    fn variadic(&self) -> bool {
829        true
830    }
831    fn arg_schema(&self) -> &'static [ArgSchema] {
832        &ARG_ANY_ONE[..]
833    }
834    fn eval<'a, 'b, 'c>(
835        &self,
836        args: &'c [ArgumentHandle<'a, 'b>],
837        ctx: &dyn FunctionContext<'b>,
838    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
839        eval_if_family(args, ctx, AggregationType::Count, true)
840    }
841}
842
843/* ─────────────────────────── AVERAGEIFS() (moved) ──────────────────────────── */
844#[derive(Debug)]
845pub struct AverageIfsFn;
846impl Function for AverageIfsFn {
847    func_caps!(
848        PURE,
849        REDUCTION,
850        WINDOWED,
851        STREAM_OK,
852        PARALLEL_ARGS,
853        PARALLEL_CHUNKS
854    );
855    fn name(&self) -> &'static str {
856        "AVERAGEIFS"
857    }
858    fn min_args(&self) -> usize {
859        3
860    }
861    fn variadic(&self) -> bool {
862        true
863    }
864    fn arg_schema(&self) -> &'static [ArgSchema] {
865        &ARG_ANY_ONE[..]
866    }
867    fn eval<'a, 'b, 'c>(
868        &self,
869        args: &'c [ArgumentHandle<'a, 'b>],
870        ctx: &dyn FunctionContext<'b>,
871    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
872        eval_if_family(args, ctx, AggregationType::Average, true)
873    }
874}
875
876/* ─────────────────────────── COUNTA() ──────────────────────────── */
877#[derive(Debug)]
878pub struct CountAFn; // counts non-empty (including empty text "")
879impl Function for CountAFn {
880    func_caps!(PURE, REDUCTION);
881    fn name(&self) -> &'static str {
882        "COUNTA"
883    }
884    fn min_args(&self) -> usize {
885        1
886    }
887    fn variadic(&self) -> bool {
888        true
889    }
890    fn arg_schema(&self) -> &'static [ArgSchema] {
891        &ARG_ANY_ONE[..]
892    }
893    fn eval<'a, 'b, 'c>(
894        &self,
895        args: &'c [ArgumentHandle<'a, 'b>],
896        _ctx: &dyn FunctionContext<'b>,
897    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
898        let mut cnt = 0i64;
899        for a in args {
900            if let Ok(view) = a.range_view() {
901                for res in view.type_tags_slices() {
902                    let (_, _, tag_cols) = res?;
903                    for col in tag_cols {
904                        for i in 0..col.len() {
905                            if col.value(i) != crate::arrow_store::TypeTag::Empty as u8 {
906                                cnt += 1;
907                            }
908                        }
909                    }
910                }
911            } else {
912                let v = a.value()?.into_literal();
913                if !matches!(v, LiteralValue::Empty) {
914                    cnt += 1;
915                }
916            }
917        }
918        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
919            cnt as f64,
920        )))
921    }
922}
923
924/* ─────────────────────────── COUNTBLANK() ──────────────────────────── */
925#[derive(Debug)]
926pub struct CountBlankFn; // counts truly empty cells and empty text
927impl Function for CountBlankFn {
928    func_caps!(PURE, REDUCTION);
929    fn name(&self) -> &'static str {
930        "COUNTBLANK"
931    }
932    fn min_args(&self) -> usize {
933        1
934    }
935    fn variadic(&self) -> bool {
936        true
937    }
938    fn arg_schema(&self) -> &'static [ArgSchema] {
939        &ARG_ANY_ONE[..]
940    }
941    fn eval<'a, 'b, 'c>(
942        &self,
943        args: &'c [ArgumentHandle<'a, 'b>],
944        _ctx: &dyn FunctionContext<'b>,
945    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
946        let mut cnt = 0i64;
947        for a in args {
948            if let Ok(view) = a.range_view() {
949                let mut tag_it = view.type_tags_slices();
950                let mut text_it = view.text_slices();
951
952                while let (Some(tag_res), Some(text_res)) = (tag_it.next(), text_it.next()) {
953                    let (_, _, tag_cols) = tag_res?;
954                    let (_, _, text_cols) = text_res?;
955
956                    for (tc, xc) in tag_cols.into_iter().zip(text_cols.into_iter()) {
957                        let text_arr = xc
958                            .as_any()
959                            .downcast_ref::<arrow_array::StringArray>()
960                            .unwrap();
961                        for i in 0..tc.len() {
962                            let is_blank = tc.value(i) == crate::arrow_store::TypeTag::Empty as u8
963                                || (tc.value(i) == crate::arrow_store::TypeTag::Text as u8
964                                    && !text_arr.is_null(i)
965                                    && text_arr.value(i).is_empty());
966                            if is_blank {
967                                cnt += 1;
968                            }
969                        }
970                    }
971                }
972            } else {
973                let v = a.value()?.into_literal();
974                match v {
975                    LiteralValue::Empty => cnt += 1,
976                    LiteralValue::Text(s) if s.is_empty() => cnt += 1,
977                    _ => {}
978                }
979            }
980        }
981        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
982            cnt as f64,
983        )))
984    }
985}
986
987pub fn register_builtins() {
988    use std::sync::Arc;
989    crate::function_registry::register_function(Arc::new(SumIfFn));
990    crate::function_registry::register_function(Arc::new(CountIfFn));
991    crate::function_registry::register_function(Arc::new(AverageIfFn));
992    crate::function_registry::register_function(Arc::new(SumIfsFn));
993    crate::function_registry::register_function(Arc::new(CountIfsFn));
994    crate::function_registry::register_function(Arc::new(AverageIfsFn));
995    crate::function_registry::register_function(Arc::new(CountAFn));
996    crate::function_registry::register_function(Arc::new(CountBlankFn));
997}
998
999#[cfg(test)]
1000mod tests {
1001    use super::*;
1002    use crate::test_workbook::TestWorkbook;
1003    use crate::traits::ArgumentHandle;
1004    use formualizer_common::LiteralValue;
1005    use formualizer_parse::parser::{ASTNode, ASTNodeType};
1006    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1007        wb.interpreter()
1008    }
1009    fn lit(v: LiteralValue) -> ASTNode {
1010        ASTNode::new(ASTNodeType::Literal(v), None)
1011    }
1012
1013    #[test]
1014    fn sumif_basic() {
1015        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1016        let ctx = interp(&wb);
1017        let range = lit(LiteralValue::Array(vec![vec![
1018            LiteralValue::Int(1),
1019            LiteralValue::Int(2),
1020            LiteralValue::Int(3),
1021        ]]));
1022        let crit = lit(LiteralValue::Text(">1".into()));
1023        let args = vec![
1024            ArgumentHandle::new(&range, &ctx),
1025            ArgumentHandle::new(&crit, &ctx),
1026        ];
1027        let f = ctx.context.get_function("", "SUMIF").unwrap();
1028        assert_eq!(
1029            f.dispatch(&args, &ctx.function_context(None))
1030                .unwrap()
1031                .into_literal(),
1032            LiteralValue::Number(5.0)
1033        );
1034    }
1035
1036    #[test]
1037    fn sumif_with_sum_range() {
1038        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1039        let ctx = interp(&wb);
1040        let range = lit(LiteralValue::Array(vec![vec![
1041            LiteralValue::Int(1),
1042            LiteralValue::Int(0),
1043            LiteralValue::Int(1),
1044        ]]));
1045        let sum_range = lit(LiteralValue::Array(vec![vec![
1046            LiteralValue::Int(10),
1047            LiteralValue::Int(20),
1048            LiteralValue::Int(30),
1049        ]]));
1050        let crit = lit(LiteralValue::Text("=1".into()));
1051        let args = vec![
1052            ArgumentHandle::new(&range, &ctx),
1053            ArgumentHandle::new(&crit, &ctx),
1054            ArgumentHandle::new(&sum_range, &ctx),
1055        ];
1056        let f = ctx.context.get_function("", "SUMIF").unwrap();
1057        assert_eq!(
1058            f.dispatch(&args, &ctx.function_context(None))
1059                .unwrap()
1060                .into_literal(),
1061            LiteralValue::Number(40.0)
1062        );
1063    }
1064
1065    #[test]
1066    fn sumif_numeric_zero_matches_blank_in_text_column() {
1067        // Regression test: if the criteria range is text-typed (no numeric fast-path column),
1068        // numeric criteria should still match blanks (Excel semantics: blank coerces to 0).
1069        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1070        let ctx = interp(&wb);
1071
1072        // Criteria range is a 1x2 row with (blank, "x") so the column is non-numeric.
1073        let range = lit(LiteralValue::Array(vec![vec![
1074            LiteralValue::Empty,
1075            LiteralValue::Text("x".into()),
1076        ]]));
1077        let sum_range = lit(LiteralValue::Array(vec![vec![
1078            LiteralValue::Int(5),
1079            LiteralValue::Int(7),
1080        ]]));
1081        let crit = lit(LiteralValue::Int(0));
1082
1083        let args = vec![
1084            ArgumentHandle::new(&range, &ctx),
1085            ArgumentHandle::new(&crit, &ctx),
1086            ArgumentHandle::new(&sum_range, &ctx),
1087        ];
1088        let f = ctx.context.get_function("", "SUMIF").unwrap();
1089        assert_eq!(
1090            f.dispatch(&args, &ctx.function_context(None))
1091                .unwrap()
1092                .into_literal(),
1093            LiteralValue::Number(5.0)
1094        );
1095    }
1096
1097    #[test]
1098    fn sumif_mismatched_ranges_now_pad_with_empty() {
1099        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1100        let ctx = interp(&wb);
1101        // sum_range: 2x2
1102        let sum = lit(LiteralValue::Array(vec![
1103            vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1104            vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1105        ]));
1106        // criteria range: 3x2 (extra row should be ignored due to iterating sum_range dims)
1107        let crit_range = lit(LiteralValue::Array(vec![
1108            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1109            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1110            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1111        ]));
1112        let crit = lit(LiteralValue::Text("=1".into()));
1113        let args = vec![
1114            ArgumentHandle::new(&crit_range, &ctx),
1115            ArgumentHandle::new(&crit, &ctx),
1116            ArgumentHandle::new(&sum, &ctx),
1117        ];
1118        let f = ctx.context.get_function("", "SUMIF").unwrap();
1119        assert_eq!(
1120            f.dispatch(&args, &ctx.function_context(None))
1121                .unwrap()
1122                .into_literal(),
1123            LiteralValue::Number(10.0)
1124        );
1125    }
1126
1127    #[test]
1128    fn countif_text_wildcard() {
1129        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1130        let ctx = interp(&wb);
1131        let rng = lit(LiteralValue::Array(vec![vec![
1132            LiteralValue::Text("alpha".into()),
1133            LiteralValue::Text("beta".into()),
1134            LiteralValue::Text("alphabet".into()),
1135        ]]));
1136        let crit = lit(LiteralValue::Text("al*".into()));
1137        let args = vec![
1138            ArgumentHandle::new(&rng, &ctx),
1139            ArgumentHandle::new(&crit, &ctx),
1140        ];
1141        let f = ctx.context.get_function("", "COUNTIF").unwrap();
1142        assert_eq!(
1143            f.dispatch(&args, &ctx.function_context(None))
1144                .unwrap()
1145                .into_literal(),
1146            LiteralValue::Number(2.0)
1147        );
1148    }
1149
1150    #[test]
1151    fn sumifs_multiple_criteria() {
1152        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1153        let ctx = interp(&wb);
1154        let sum = lit(LiteralValue::Array(vec![vec![
1155            LiteralValue::Int(10),
1156            LiteralValue::Int(20),
1157            LiteralValue::Int(30),
1158            LiteralValue::Int(40),
1159        ]]));
1160        let city = lit(LiteralValue::Array(vec![vec![
1161            LiteralValue::Text("Bellevue".into()),
1162            LiteralValue::Text("Issaquah".into()),
1163            LiteralValue::Text("Bellevue".into()),
1164            LiteralValue::Text("Issaquah".into()),
1165        ]]));
1166        let beds = lit(LiteralValue::Array(vec![vec![
1167            LiteralValue::Int(2),
1168            LiteralValue::Int(3),
1169            LiteralValue::Int(4),
1170            LiteralValue::Int(5),
1171        ]]));
1172        let c_city = lit(LiteralValue::Text("Bellevue".into()));
1173        let c_beds = lit(LiteralValue::Text(">=4".into()));
1174        let args = vec![
1175            ArgumentHandle::new(&sum, &ctx),
1176            ArgumentHandle::new(&city, &ctx),
1177            ArgumentHandle::new(&c_city, &ctx),
1178            ArgumentHandle::new(&beds, &ctx),
1179            ArgumentHandle::new(&c_beds, &ctx),
1180        ];
1181        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1182        assert_eq!(
1183            f.dispatch(&args, &ctx.function_context(None))
1184                .unwrap()
1185                .into_literal(),
1186            LiteralValue::Number(30.0)
1187        );
1188    }
1189
1190    #[test]
1191    fn countifs_basic() {
1192        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1193        let ctx = interp(&wb);
1194        let city = lit(LiteralValue::Array(vec![vec![
1195            LiteralValue::Text("a".into()),
1196            LiteralValue::Text("b".into()),
1197            LiteralValue::Text("a".into()),
1198        ]]));
1199        let beds = lit(LiteralValue::Array(vec![vec![
1200            LiteralValue::Int(1),
1201            LiteralValue::Int(2),
1202            LiteralValue::Int(3),
1203        ]]));
1204        let c_city = lit(LiteralValue::Text("a".into()));
1205        let c_beds = lit(LiteralValue::Text(">1".into()));
1206        let args = vec![
1207            ArgumentHandle::new(&city, &ctx),
1208            ArgumentHandle::new(&c_city, &ctx),
1209            ArgumentHandle::new(&beds, &ctx),
1210            ArgumentHandle::new(&c_beds, &ctx),
1211        ];
1212        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1213        assert_eq!(
1214            f.dispatch(&args, &ctx.function_context(None))
1215                .unwrap()
1216                .into_literal(),
1217            LiteralValue::Number(1.0)
1218        );
1219    }
1220
1221    #[test]
1222    fn averageifs_div0() {
1223        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1224        let ctx = interp(&wb);
1225        let avg = lit(LiteralValue::Array(vec![vec![
1226            LiteralValue::Int(1),
1227            LiteralValue::Int(2),
1228        ]]));
1229        let crit_rng = lit(LiteralValue::Array(vec![vec![
1230            LiteralValue::Int(0),
1231            LiteralValue::Int(0),
1232        ]]));
1233        let crit = lit(LiteralValue::Text(">0".into()));
1234        let args = vec![
1235            ArgumentHandle::new(&avg, &ctx),
1236            ArgumentHandle::new(&crit_rng, &ctx),
1237            ArgumentHandle::new(&crit, &ctx),
1238        ];
1239        let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1240        match f
1241            .dispatch(&args, &ctx.function_context(None))
1242            .unwrap()
1243            .into_literal()
1244        {
1245            LiteralValue::Error(e) => assert_eq!(e, "#DIV/0!"),
1246            _ => panic!("expected div0"),
1247        }
1248    }
1249
1250    #[test]
1251    fn counta_and_countblank() {
1252        let wb = TestWorkbook::new()
1253            .with_function(std::sync::Arc::new(CountAFn))
1254            .with_function(std::sync::Arc::new(CountBlankFn));
1255        let ctx = interp(&wb);
1256        let arr = lit(LiteralValue::Array(vec![vec![
1257            LiteralValue::Empty,
1258            LiteralValue::Text("".into()),
1259            LiteralValue::Int(5),
1260        ]]));
1261        let args = vec![ArgumentHandle::new(&arr, &ctx)];
1262        let counta = ctx.context.get_function("", "COUNTA").unwrap();
1263        let countblank = ctx.context.get_function("", "COUNTBLANK").unwrap();
1264        assert_eq!(
1265            counta
1266                .dispatch(&args, &ctx.function_context(None))
1267                .unwrap()
1268                .into_literal(),
1269            LiteralValue::Number(2.0)
1270        );
1271        assert_eq!(
1272            countblank
1273                .dispatch(&args, &ctx.function_context(None))
1274                .unwrap()
1275                .into_literal(),
1276            LiteralValue::Number(2.0)
1277        );
1278    }
1279
1280    // ───────── Parity tests (window vs scalar) ─────────
1281    #[test]
1282    fn sumifs_broadcasts_1x1_criteria_over_range() {
1283        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1284        let ctx = interp(&wb);
1285        // sum_range: column vector [10, 20]
1286        let sum = lit(LiteralValue::Array(vec![
1287            vec![LiteralValue::Int(10)],
1288            vec![LiteralValue::Int(20)],
1289        ]));
1290        // criteria_range: column vector ["A", "B"]
1291        let tags = lit(LiteralValue::Array(vec![
1292            vec![LiteralValue::Text("A".into())],
1293            vec![LiteralValue::Text("B".into())],
1294        ]));
1295        // criteria: 1x1 array acting as scalar "A"
1296        let c_tag = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1297            "A".into(),
1298        )]]));
1299        let args = vec![
1300            ArgumentHandle::new(&sum, &ctx),
1301            ArgumentHandle::new(&tags, &ctx),
1302            ArgumentHandle::new(&c_tag, &ctx),
1303        ];
1304        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1305        assert_eq!(
1306            f.dispatch(&args, &ctx.function_context(None))
1307                .unwrap()
1308                .into_literal(),
1309            LiteralValue::Number(10.0)
1310        );
1311    }
1312
1313    #[test]
1314    fn countifs_broadcasts_1x1_criteria_over_row() {
1315        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1316        let ctx = interp(&wb);
1317        // criteria_range: row [1,2,3,4]
1318        let nums = lit(LiteralValue::Array(vec![vec![
1319            LiteralValue::Int(1),
1320            LiteralValue::Int(2),
1321            LiteralValue::Int(3),
1322            LiteralValue::Int(4),
1323        ]]));
1324        // criteria: 1x1 array ">=3"
1325        let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1326            ">=3".into(),
1327        )]]));
1328        let args = vec![
1329            ArgumentHandle::new(&nums, &ctx),
1330            ArgumentHandle::new(&crit, &ctx),
1331        ];
1332        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1333        assert_eq!(
1334            f.dispatch(&args, &ctx.function_context(None))
1335                .unwrap()
1336                .into_literal(),
1337            LiteralValue::Number(2.0)
1338        );
1339    }
1340
1341    #[test]
1342    fn sumifs_empty_ranges_with_1x1_criteria_produce_zero() {
1343        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1344        let ctx = interp(&wb);
1345        // Empty ranges (0x0) simulate unused whole-column resolved empty
1346        let empty = lit(LiteralValue::Array(Vec::new()));
1347        // 1x1 criteria (array)
1348        let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1349            "X".into(),
1350        )]]));
1351        let args = vec![
1352            ArgumentHandle::new(&empty, &ctx),
1353            ArgumentHandle::new(&empty, &ctx),
1354            ArgumentHandle::new(&crit, &ctx),
1355        ];
1356        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1357        assert_eq!(
1358            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1359            LiteralValue::Number(0.0)
1360        );
1361    }
1362
1363    #[test]
1364    fn sumifs_mismatched_ranges_now_pad_with_empty() {
1365        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1366        let ctx = interp(&wb);
1367        // sum_range: 2x2
1368        let sum = lit(LiteralValue::Array(vec![
1369            vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1370            vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1371        ]));
1372        // criteria_range: 3x2 (different rows - extra row will match against padded empty values)
1373        let crit_range = lit(LiteralValue::Array(vec![
1374            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1375            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1376            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1377        ]));
1378        // scalar criterion
1379        let crit = lit(LiteralValue::Text("=1".into()));
1380        let args = vec![
1381            ArgumentHandle::new(&sum, &ctx),
1382            ArgumentHandle::new(&crit_range, &ctx),
1383            ArgumentHandle::new(&crit, &ctx),
1384        ];
1385        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1386        // With padding, sum_range gets padded with empties for row 3
1387        // Rows 1-2 match criteria (all 1s), row 3 has empties which don't match =1
1388        // So we sum: 1 + 2 + 3 + 4 = 10
1389        assert_eq!(
1390            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1391            LiteralValue::Number(10.0)
1392        );
1393    }
1394
1395    #[test]
1396    fn countifs_mismatched_ranges_pad_and_broadcast() {
1397        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1398        let ctx = interp(&wb);
1399        // criteria_range1: 2x1 -> [1,1]
1400        let r1 = lit(LiteralValue::Array(vec![
1401            vec![LiteralValue::Int(1)],
1402            vec![LiteralValue::Int(1)],
1403        ]));
1404        // criteria1: "=1"
1405        let c1 = lit(LiteralValue::Text("=1".into()));
1406        // criteria_range2: 3x1 -> [1,1,1]
1407        let r2 = lit(LiteralValue::Array(vec![
1408            vec![LiteralValue::Int(1)],
1409            vec![LiteralValue::Int(1)],
1410            vec![LiteralValue::Int(1)],
1411        ]));
1412        // criteria2: "=1"
1413        let c2 = lit(LiteralValue::Text("=1".into()));
1414        let args = vec![
1415            ArgumentHandle::new(&r1, &ctx),
1416            ArgumentHandle::new(&c1, &ctx),
1417            ArgumentHandle::new(&r2, &ctx),
1418            ArgumentHandle::new(&c2, &ctx),
1419        ];
1420        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1421        // Union rows = 3; row3 has r1=Empty (padded), which doesn't match =1; expect 2
1422        assert_eq!(
1423            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1424            LiteralValue::Number(2.0)
1425        );
1426    }
1427
1428    #[test]
1429    fn averageifs_mismatched_ranges_pad() {
1430        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1431        let ctx = interp(&wb);
1432        // avg_range: 2x1 -> [10,20]
1433        let avg = lit(LiteralValue::Array(vec![
1434            vec![LiteralValue::Int(10)],
1435            vec![LiteralValue::Int(20)],
1436        ]));
1437        // criteria_range: 3x1 -> [1,1,2]
1438        let r1 = lit(LiteralValue::Array(vec![
1439            vec![LiteralValue::Int(1)],
1440            vec![LiteralValue::Int(1)],
1441            vec![LiteralValue::Int(2)],
1442        ]));
1443        let c1 = lit(LiteralValue::Text("=1".into()));
1444        let args = vec![
1445            ArgumentHandle::new(&avg, &ctx),
1446            ArgumentHandle::new(&r1, &ctx),
1447            ArgumentHandle::new(&c1, &ctx),
1448        ];
1449        let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1450        // Only first two rows match; expect (10+20)/2 = 15
1451        assert_eq!(
1452            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1453            LiteralValue::Number(15.0)
1454        );
1455    }
1456
1457    #[test]
1458    fn criteria_scientific_notation() {
1459        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1460        let ctx = interp(&wb);
1461        let nums = lit(LiteralValue::Array(vec![vec![
1462            LiteralValue::Number(1000.0),
1463            LiteralValue::Number(1500.0),
1464            LiteralValue::Number(999.0),
1465        ]]));
1466        let crit = lit(LiteralValue::Text(">1e3".into())); // should parse as >1000
1467        let args = vec![
1468            ArgumentHandle::new(&nums, &ctx),
1469            ArgumentHandle::new(&crit, &ctx),
1470        ];
1471        let f = ctx.context.get_function("", "SUMIF").unwrap();
1472        // >1000 matches 1500 only (strict greater)
1473        assert_eq!(
1474            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1475            LiteralValue::Number(1500.0)
1476        );
1477    }
1478}
formualizer_eval/builtins/math/criteria_aggregates.rs

formualizer_eval/builtins/math/
criteria_aggregates.rs
