formualizer_eval/builtins/math/
criteria_aggregates.rs

1use super::super::utils::{ARG_ANY_ONE, coerce_num, criteria_match};
2use crate::args::ArgSchema;
3use crate::function::Function;
4use crate::traits::{ArgumentHandle, FunctionContext};
5use formualizer_common::{ExcelError, LiteralValue};
6use formualizer_macros::func_caps;
7
8/*
9Criteria-driven aggregation functions:
10  - SUMIF(range, criteria, [sum_range])
11  - SUMIFS(sum_range, criteria_range1, criteria1, ...)
12  - COUNTIF(range, criteria)
13  - COUNTIFS(criteria_range1, criteria1, ...)
14  - AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)  (moved here from aggregate.rs)
15  - COUNTA(value1, value2, ...)
16  - COUNTBLANK(range_or_values...)
17
18Design notes:
19  * Validation of shape parity for multi-criteria aggregations (#VALUE! on mismatch).
20  * Criteria parsing reused via crate::args::parse_criteria and criteria_match helper in utils.
21  * Streaming optimization deferred (TODO(perf)).
22*/
23
24/* ─────────────────────────── SUMIF() ──────────────────────────── */
25#[derive(Debug)]
26pub struct SumIfFn;
27impl Function for SumIfFn {
28    func_caps!(
29        PURE,
30        REDUCTION,
31        WINDOWED,
32        STREAM_OK,
33        PARALLEL_ARGS,
34        PARALLEL_CHUNKS
35    );
36    fn name(&self) -> &'static str {
37        "SUMIF"
38    }
39    fn min_args(&self) -> usize {
40        2
41    }
42    fn variadic(&self) -> bool {
43        true
44    }
45    fn arg_schema(&self) -> &'static [ArgSchema] {
46        &ARG_ANY_ONE[..]
47    }
48    fn eval_scalar<'a, 'b>(
49        &self,
50        args: &'a [ArgumentHandle<'a, 'b>],
51        ctx: &dyn FunctionContext,
52    ) -> Result<LiteralValue, ExcelError> {
53        if args.len() < 2 || args.len() > 3 {
54            return Ok(LiteralValue::Error(ExcelError::new_value().with_message(
55                format!("SUMIF expects 2 or 3 arguments, got {}", args.len()),
56            )));
57        }
58
59        let pred = crate::args::parse_criteria(args[1].value()?.as_ref())?;
60
61        // Resolve criteria source via RangeView or scalar
62        let crit_view = args[0].range_view().ok();
63        let crit_scalar = if crit_view.is_none() {
64            Some(args[0].value()?.into_owned())
65        } else {
66            None
67        };
68
69        // Resolve sum source and iteration dims
70        let mut sum_view_opt: Option<crate::engine::range_view::RangeView<'_>> = None;
71        let mut sum_scalar_opt: Option<LiteralValue> = None;
72        let dims: (usize, usize);
73
74        if args.len() == 3 {
75            if let Ok(v) = args[2].range_view() {
76                dims = v.dims();
77                sum_view_opt = Some(v);
78            } else {
79                let sv = args[2].value()?.into_owned();
80                // If criteria is a range, iterate over its dims; else single cell
81                dims = crit_view.as_ref().map(|v| v.dims()).unwrap_or((1, 1));
82                sum_scalar_opt = Some(sv);
83            }
84        } else {
85            // No sum_range: sum over the criteria range itself or scalar
86            if let Ok(v) = args[0].range_view() {
87                dims = v.dims();
88                sum_view_opt = Some(v);
89            } else {
90                let sv = args[0].value()?.into_owned();
91                sum_scalar_opt = Some(sv);
92                dims = (1, 1);
93            }
94        }
95
96        // Optimized numeric-only path when summing from a numeric-only view
97        if let Some(ref sum_view) = sum_view_opt
98            && sum_view.kind_probe() == crate::engine::range_view::RangeKind::NumericOnly
99        {
100            let mut total = 0.0f64;
101            for row in 0..dims.0 {
102                for col in 0..dims.1 {
103                    // Criteria value (padded/broadcast)
104                    let cval = if let Some(ref v) = crit_view {
105                        v.get_cell(row, col)
106                    } else if let Some(ref s) = crit_scalar {
107                        s.clone()
108                    } else {
109                        LiteralValue::Empty
110                    };
111                    if !criteria_match(&pred, &cval) {
112                        continue;
113                    }
114                    match sum_view.get_cell(row, col) {
115                        LiteralValue::Number(n) => total += n,
116                        LiteralValue::Int(i) => total += i as f64,
117                        _ => {}
118                    }
119                }
120            }
121            return Ok(LiteralValue::Number(total));
122        }
123
124        // General path (mixed types, or scalar sum)
125        let mut total = 0.0f64;
126        for row in 0..dims.0 {
127            for col in 0..dims.1 {
128                let cval = if let Some(ref v) = crit_view {
129                    v.get_cell(row, col)
130                } else if let Some(ref s) = crit_scalar {
131                    s.clone()
132                } else {
133                    LiteralValue::Empty
134                };
135                if !criteria_match(&pred, &cval) {
136                    continue;
137                }
138                let sval = if let Some(ref v) = sum_view_opt {
139                    v.get_cell(row, col)
140                } else if let Some(ref s) = sum_scalar_opt {
141                    s.clone()
142                } else {
143                    LiteralValue::Empty
144                };
145                if let Ok(n) = coerce_num(&sval) {
146                    total += n;
147                }
148            }
149        }
150        Ok(LiteralValue::Number(total))
151    }
152}
153
154/* ─────────────────────────── COUNTIF() ──────────────────────────── */
155#[derive(Debug)]
156pub struct CountIfFn;
157impl Function for CountIfFn {
158    func_caps!(
159        PURE,
160        REDUCTION,
161        WINDOWED,
162        STREAM_OK,
163        PARALLEL_ARGS,
164        PARALLEL_CHUNKS
165    );
166    fn name(&self) -> &'static str {
167        "COUNTIF"
168    }
169    fn min_args(&self) -> usize {
170        2
171    }
172    fn variadic(&self) -> bool {
173        false
174    }
175    fn arg_schema(&self) -> &'static [ArgSchema] {
176        &ARG_ANY_ONE[..]
177    }
178    fn eval_scalar<'a, 'b>(
179        &self,
180        args: &'a [ArgumentHandle<'a, 'b>],
181        ctx: &dyn FunctionContext,
182    ) -> Result<LiteralValue, ExcelError> {
183        if args.len() != 2 {
184            return Ok(LiteralValue::Error(ExcelError::new_value().with_message(
185                format!("COUNTIF expects 2 arguments, got {}", args.len()),
186            )));
187        }
188        let pred = crate::args::parse_criteria(args[1].value()?.as_ref())?;
189        // Use RangeView if possible
190        if let Ok(view) = args[0].range_view() {
191            let mut cnt = 0i64;
192            let _ = view.for_each_cell(&mut |cell| {
193                if criteria_match(&pred, cell) {
194                    cnt += 1;
195                }
196                Ok(())
197            });
198            return Ok(LiteralValue::Number(cnt as f64));
199        }
200        // Scalar fallback
201        let v = args[0].value()?.into_owned();
202        let matches = criteria_match(&pred, &v);
203        Ok(LiteralValue::Number(if matches { 1.0 } else { 0.0 }))
204    }
205}
206
207/* ─────────────────────────── SUMIFS() ──────────────────────────── */
208#[derive(Debug)]
209pub struct SumIfsFn; // SUMIFS(sum_range, criteria_range1, criteria1, ...)
210impl Function for SumIfsFn {
211    func_caps!(
212        PURE,
213        REDUCTION,
214        WINDOWED,
215        STREAM_OK,
216        PARALLEL_ARGS,
217        PARALLEL_CHUNKS
218    );
219    fn name(&self) -> &'static str {
220        "SUMIFS"
221    }
222    fn min_args(&self) -> usize {
223        3
224    }
225    fn variadic(&self) -> bool {
226        true
227    }
228    fn arg_schema(&self) -> &'static [ArgSchema] {
229        &ARG_ANY_ONE[..]
230    }
231    fn eval_scalar<'a, 'b>(
232        &self,
233        args: &'a [ArgumentHandle<'a, 'b>],
234        ctx: &dyn FunctionContext,
235    ) -> Result<LiteralValue, ExcelError> {
236        #[cfg(feature = "tracing")]
237        let _span = tracing::info_span!("SUMIFS").entered();
238        if args.len() < 3 || !(args.len() - 1).is_multiple_of(2) {
239            return Ok(LiteralValue::Error(
240                ExcelError::new_value().with_message(format!(
241                    "SUMIFS expects 1 sum_range followed by N pairs (criteria_range, criteria); got {} args",
242                    args.len()
243                )),
244            ));
245        }
246
247        // Get the sum range as a RangeView
248        let sum_view = match args[0].range_view() {
249            Ok(v) => v,
250            Err(_) => {
251                // Scalar fallback
252                let val = args[0].value()?;
253                let mut total = 0.0f64;
254                // For scalar, we need all criteria to be scalar and match
255                for i in (1..args.len()).step_by(2) {
256                    let crit_val = args[i].value()?;
257                    let pred = crate::args::parse_criteria(args[i + 1].value()?.as_ref())?;
258                    if !criteria_match(&pred, crit_val.as_ref()) {
259                        return Ok(LiteralValue::Number(0.0));
260                    }
261                }
262                if let Ok(n) = coerce_num(val.as_ref()) {
263                    total = n;
264                }
265                return Ok(LiteralValue::Number(total));
266            }
267        };
268
269        let dims = sum_view.dims();
270
271        // Collect criteria ranges and predicates
272        let mut crit_views = Vec::new();
273        let mut preds = Vec::new();
274        // Build views/preds
275        let mut arg_i = 1usize;
276        while arg_i + 1 < args.len() {
277            let crit_arg = arg_i;
278            let pred_arg = arg_i + 1;
279            match args[crit_arg].range_view() {
280                Ok(v) => {
281                    if v.dims() == (1, 1) {
282                        let scalar_val = v.get_cell(0, 0);
283                        crit_views.push(None);
284                        let p = crate::args::parse_criteria(args[pred_arg].value()?.as_ref())?;
285                        preds.push((p, Some(scalar_val)));
286                    } else {
287                        crit_views.push(Some(v));
288                        let p = crate::args::parse_criteria(args[pred_arg].value()?.as_ref())?;
289                        preds.push((p, None));
290                    }
291                }
292                Err(_) => {
293                    let val = args[crit_arg].value()?.into_owned();
294                    crit_views.push(None);
295                    let p = crate::args::parse_criteria(args[pred_arg].value()?.as_ref())?;
296                    preds.push((p, Some(val)));
297                }
298            }
299            arg_i += 2;
300        }
301
302        #[cfg(feature = "tracing")]
303        tracing::debug!(
304            rows = dims.0,
305            cols = dims.1,
306            criteria = preds.len(),
307            "sumifs_dims"
308        );
309
310        // Arrow fast path (guarded by config): numeric comparators and basic text (eq/neq, wildcard) over Arrow-backed views.
311        // Supports N-criteria with array/scalar comparisons.
312        if ctx.arrow_fastpath_enabled()
313            && let Some(sum_av) = sum_view.as_arrow()
314        {
315            use crate::compute_prelude::{boolean, cmp, concat_arrays, filter_array};
316            use arrow::compute::kernels::aggregate::sum_array;
317            use arrow::compute::kernels::comparison::{ilike, nilike};
318            use arrow_array::types::Float64Type;
319            use arrow_array::{Array as _, ArrayRef, BooleanArray, Float64Array, StringArray};
320            // Validate criteria are supported and Arrow-backed when range-like, with identical dims
321            let mut ok = true;
322            for (j, (pred, scalar_val)) in preds.iter().enumerate() {
323                match pred {
324                    // Numeric support
325                    crate::args::CriteriaPredicate::Gt(_)
326                    | crate::args::CriteriaPredicate::Ge(_)
327                    | crate::args::CriteriaPredicate::Lt(_)
328                    | crate::args::CriteriaPredicate::Le(_) => {}
329                    crate::args::CriteriaPredicate::Eq(v)
330                    | crate::args::CriteriaPredicate::Ne(v) => match v {
331                        LiteralValue::Number(_) | LiteralValue::Int(_) => {}
332                        LiteralValue::Text(_) => {}
333                        _ => {
334                            ok = false;
335                        }
336                    },
337                    crate::args::CriteriaPredicate::TextLike { .. } => {}
338                    _ => {
339                        ok = false;
340                    }
341                }
342                if !ok {
343                    break;
344                }
345                if let Some(ref v) = crit_views[j]
346                    && (v.as_arrow().is_none() || v.dims() != dims)
347                {
348                    ok = false;
349                    break;
350                }
351            }
352
353            if ok {
354                // Helper: materialize per-column Float64 arrays by concatenating row-slices
355                let build_cols = |av: &crate::arrow_store::ArrowRangeView<'_>| -> Vec<std::sync::Arc<Float64Array>> {
356                        let cols = dims.1;
357                        let mut segs: Vec<Vec<std::sync::Arc<Float64Array>>> = vec![Vec::new(); cols];
358                        for (_rs, _rl, cols_seg) in av.numbers_slices() {
359                            for c in 0..cols {
360                                segs[c].push(cols_seg[c].clone());
361                            }
362                        }
363                        let mut out: Vec<std::sync::Arc<Float64Array>> = Vec::with_capacity(cols);
364                        for mut parts in segs.into_iter() {
365                            if parts.is_empty() {
366                                out.push(std::sync::Arc::new(Float64Array::new_null(dims.0)));
367                            } else if parts.len() == 1 {
368                                out.push(parts.remove(0));
369                            } else {
370                                let anys: Vec<&dyn arrow_array::Array> =
371                                    parts.iter().map(|a| a.as_ref() as &dyn arrow_array::Array).collect();
372                                let conc: ArrayRef = concat_arrays(&anys).expect("concat");
373                                let fa = conc.as_any().downcast_ref::<Float64Array>().unwrap().clone();
374                                out.push(std::sync::Arc::new(fa));
375                            }
376                        }
377                        out
378                    };
379
380                let sum_cols = build_cols(sum_av);
381                // Pre-build criterion columns when range-like
382                let mut crit_cols: Vec<Option<Vec<std::sync::Arc<Float64Array>>>> =
383                    Vec::with_capacity(preds.len());
384                let mut crit_text_cols: Vec<Option<Vec<std::sync::Arc<StringArray>>>> =
385                    Vec::with_capacity(preds.len());
386                for (j, (_pred, scalar_val)) in preds.iter().enumerate() {
387                    // Numeric lane
388                    if let Some(ref v) = crit_views[j] {
389                        let av = v.as_arrow().unwrap();
390                        crit_cols.push(Some(build_cols(av)));
391                    } else {
392                        crit_cols.push(None);
393                    }
394                    // Text lane
395                    if let Some(ref v) = crit_views[j] {
396                        let av = v.as_arrow().unwrap();
397                        // Build lowered text columns using ArrowRangeView cache-aware path
398                        let arrays = av.lowered_text_columns();
399                        let mut out: Vec<std::sync::Arc<StringArray>> = Vec::with_capacity(dims.1);
400                        for a in arrays.into_iter() {
401                            let sa = a.as_any().downcast_ref::<StringArray>().unwrap().clone();
402                            out.push(std::sync::Arc::new(sa));
403                        }
404                        crit_text_cols.push(Some(out));
405                    } else {
406                        crit_text_cols.push(None);
407                    }
408                }
409
410                // Helper: lowercase a StringArray (preserve nulls)
411                fn lower_string_array(a: &StringArray) -> StringArray {
412                    let mut b =
413                        arrow_array::builder::StringBuilder::with_capacity(a.len(), a.len() * 8);
414                    for i in 0..a.len() {
415                        if a.is_null(i) {
416                            b.append_null();
417                        } else {
418                            b.append_value(a.value(i).to_ascii_lowercase());
419                        }
420                    }
421                    b.finish()
422                }
423
424                let mut total = 0.0f64;
425                // Process per column; build combined mask and sum filtered values
426                for c in 0..dims.1 {
427                    let values = &sum_cols[c];
428                    // Build mask per criterion for this column
429                    let mut mask_opt: Option<BooleanArray> = None;
430                    for (j, (pred, scalar_val)) in preds.iter().enumerate() {
431                        // Determine the criterion column data or scalar
432                        let col_arr: Option<&Float64Array> =
433                            crit_cols[j].as_ref().map(|cols| cols[c].as_ref());
434                        let col_text: Option<&StringArray> =
435                            crit_text_cols[j].as_ref().map(|cols| cols[c].as_ref());
436                        // Try cached mask if criterion range is Arrow-backed
437                        let cur_cached: Option<BooleanArray> = if let Some(ref view) = crit_views[j]
438                        {
439                            if let Some(av) = view.as_arrow() {
440                                ctx.get_criteria_mask(av, c, pred).map(|m| (*m).clone())
441                            } else {
442                                None
443                            }
444                        } else {
445                            None
446                        };
447
448                        let cur = if let Some(cm) = cur_cached {
449                            Some(cm)
450                        } else {
451                            match pred {
452                                crate::args::CriteriaPredicate::Gt(n) => Some(
453                                    cmp::gt(col_arr.unwrap(), &Float64Array::new_scalar(*n))
454                                        .unwrap(),
455                                ),
456                                crate::args::CriteriaPredicate::Ge(n) => Some(
457                                    cmp::gt_eq(col_arr.unwrap(), &Float64Array::new_scalar(*n))
458                                        .unwrap(),
459                                ),
460                                crate::args::CriteriaPredicate::Lt(n) => Some(
461                                    cmp::lt(col_arr.unwrap(), &Float64Array::new_scalar(*n))
462                                        .unwrap(),
463                                ),
464                                crate::args::CriteriaPredicate::Le(n) => Some(
465                                    cmp::lt_eq(col_arr.unwrap(), &Float64Array::new_scalar(*n))
466                                        .unwrap(),
467                                ),
468                                crate::args::CriteriaPredicate::Eq(v) => {
469                                    match v {
470                                        LiteralValue::Number(x) => Some(
471                                            cmp::eq(
472                                                col_arr.unwrap(),
473                                                &Float64Array::new_scalar(*x),
474                                            )
475                                            .unwrap(),
476                                        ),
477                                        LiteralValue::Int(i) => Some(
478                                            cmp::eq(
479                                                col_arr.unwrap(),
480                                                &Float64Array::new_scalar(*i as f64),
481                                            )
482                                            .unwrap(),
483                                        ),
484                                        LiteralValue::Text(t) => {
485                                            let lt = t.to_ascii_lowercase();
486                                            let lowered = col_text.unwrap();
487                                            let pat = StringArray::new_scalar(lt);
488                                            // Special case: equality to empty string treats nulls as equal in Excel
489                                            let mut m = ilike(&lowered, &pat).unwrap();
490                                            if t.is_empty() {
491                                                // Build nulls mask and OR it
492                                                let mut bb = arrow_array::builder::BooleanBuilder::with_capacity(lowered.len());
493                                                for i in 0..lowered.len() {
494                                                    bb.append_value(lowered.is_null(i));
495                                                }
496                                                let add = bb.finish();
497                                                m = boolean::or_kleene(&m, &add).unwrap();
498                                            }
499                                            Some(m)
500                                        }
501                                        _ => {
502                                            ok = false;
503                                            break;
504                                        }
505                                    }
506                                }
507                                crate::args::CriteriaPredicate::Ne(v) => match v {
508                                    LiteralValue::Number(x) => Some(
509                                        cmp::neq(col_arr.unwrap(), &Float64Array::new_scalar(*x))
510                                            .unwrap(),
511                                    ),
512                                    LiteralValue::Int(i) => Some(
513                                        cmp::neq(
514                                            col_arr.unwrap(),
515                                            &Float64Array::new_scalar(*i as f64),
516                                        )
517                                        .unwrap(),
518                                    ),
519                                    LiteralValue::Text(t) => {
520                                        let lt = t.to_ascii_lowercase();
521                                        let lowered = col_text.unwrap();
522                                        let pat = StringArray::new_scalar(lt);
523                                        Some(nilike(&lowered, &pat).unwrap())
524                                    }
525                                    _ => {
526                                        ok = false;
527                                        break;
528                                    }
529                                },
530                                crate::args::CriteriaPredicate::TextLike { pattern, .. } => {
531                                    let lp = pattern
532                                        .replace('*', "%")
533                                        .replace('?', "_")
534                                        .to_ascii_lowercase();
535                                    let lowered = col_text.unwrap();
536                                    let pat = StringArray::new_scalar(lp);
537                                    Some(ilike(&lowered, &pat).unwrap())
538                                }
539                                _ => {
540                                    ok = false;
541                                    break;
542                                }
543                            }
544                        };
545
546                        if !ok {
547                            break;
548                        }
549
550                        if let Some(cur_mask) = cur {
551                            mask_opt = Some(match mask_opt {
552                                None => cur_mask,
553                                Some(prev) => boolean::and_kleene(&prev, &cur_mask).unwrap(),
554                            });
555                        }
556                    }
557
558                    if !ok {
559                        break;
560                    }
561
562                    if let Some(mask) = mask_opt {
563                        let filtered = filter_array(values.as_ref(), &mask).unwrap();
564                        let f64_arr = filtered.as_any().downcast_ref::<Float64Array>().unwrap();
565                        if let Some(part) = sum_array::<Float64Type, _>(f64_arr) {
566                            total += part;
567                        }
568                    }
569                }
570
571                if ok {
572                    return Ok(LiteralValue::Number(total));
573                }
574            }
575        }
576
577        // Check if we can use the optimized numeric path
578        if sum_view.kind_probe() == crate::engine::range_view::RangeKind::NumericOnly {
579            // Optimized path for numeric-only sum range
580            let mut total = 0.0f64;
581
582            // We'll iterate by rows for union dims (padding via get_cell)
583            for row in 0..dims.0 {
584                for col in 0..dims.1 {
585                    // Check all criteria
586                    let mut all_match = true;
587                    for (j, (pred, scalar_val)) in preds.iter().enumerate() {
588                        let crit_val = if let Some(ref view) = crit_views[j] {
589                            view.get_cell(row, col)
590                        } else if let Some(scalar) = scalar_val {
591                            scalar.clone()
592                        } else {
593                            LiteralValue::Empty
594                        };
595
596                        if !criteria_match(pred, &crit_val) {
597                            all_match = false;
598                            break;
599                        }
600                    }
601
602                    if all_match {
603                        match sum_view.get_cell(row, col) {
604                            LiteralValue::Number(n) => total += n,
605                            LiteralValue::Int(i) => total += i as f64,
606                            _ => {}
607                        }
608                    }
609                }
610            }
611
612            return Ok(LiteralValue::Number(total));
613        }
614
615        // General path for mixed or non-numeric ranges over union dims
616        let mut total = 0.0f64;
617        for row in 0..dims.0 {
618            for col in 0..dims.1 {
619                // Check all criteria
620                let mut all_match = true;
621                for (j, (pred, scalar_val)) in preds.iter().enumerate() {
622                    let crit_val = if let Some(ref view) = crit_views[j] {
623                        view.get_cell(row, col)
624                    } else if let Some(scalar) = scalar_val {
625                        scalar.clone()
626                    } else {
627                        LiteralValue::Empty
628                    };
629                    if !criteria_match(pred, &crit_val) {
630                        all_match = false;
631                        break;
632                    }
633                }
634                if all_match {
635                    let sum_val = sum_view.get_cell(row, col);
636                    if let Ok(n) = coerce_num(&sum_val) {
637                        total += n;
638                    }
639                }
640            }
641        }
642        Ok(LiteralValue::Number(total))
643    }
644}
645
646/* ─────────────────────────── COUNTIFS() ──────────────────────────── */
647#[derive(Debug)]
648pub struct CountIfsFn; // COUNTIFS(criteria_range1, criteria1, ...)
649impl Function for CountIfsFn {
650    func_caps!(
651        PURE,
652        REDUCTION,
653        WINDOWED,
654        STREAM_OK,
655        PARALLEL_ARGS,
656        PARALLEL_CHUNKS
657    );
658    fn name(&self) -> &'static str {
659        "COUNTIFS"
660    }
661    fn min_args(&self) -> usize {
662        2
663    }
664    fn variadic(&self) -> bool {
665        true
666    }
667    fn arg_schema(&self) -> &'static [ArgSchema] {
668        &ARG_ANY_ONE[..]
669    }
670    fn eval_scalar<'a, 'b>(
671        &self,
672        args: &'a [ArgumentHandle<'a, 'b>],
673        ctx: &dyn FunctionContext,
674    ) -> Result<LiteralValue, ExcelError> {
675        #[cfg(feature = "tracing")]
676        let _span = tracing::info_span!("COUNTIFS").entered();
677        if args.len() < 2 || !args.len().is_multiple_of(2) {
678            return Ok(LiteralValue::Error(ExcelError::new_value().with_message(
679                format!(
680                    "COUNTIFS expects N pairs (criteria_range, criteria); got {} args",
681                    args.len()
682                ),
683            )));
684        }
685        // Collect criteria as views or scalars; compute union dims
686        let mut crit_views: Vec<Option<crate::engine::range_view::RangeView<'_>>> = Vec::new();
687        let mut preds = Vec::new();
688        let mut dims = (1usize, 1usize);
689        let mut seen_any_view = false;
690        // Build views/preds and union dims
691        let mut arg_i = 0usize;
692        while arg_i + 1 < args.len() {
693            let pred = crate::args::parse_criteria(args[arg_i + 1].value()?.as_ref())?;
694            match args[arg_i].range_view() {
695                Ok(v) => {
696                    if v.dims() == (1, 1) {
697                        let scalar = v.get_cell(0, 0);
698                        crit_views.push(None);
699                        preds.push((pred, Some(scalar)));
700                    } else {
701                        let vd = v.dims();
702                        if !seen_any_view {
703                            dims = vd;
704                            seen_any_view = true;
705                        } else {
706                            if vd.0 > dims.0 {
707                                dims.0 = vd.0;
708                            }
709                            if vd.1 > dims.1 {
710                                dims.1 = vd.1;
711                            }
712                        }
713                        crit_views.push(Some(v));
714                        preds.push((pred, None));
715                    }
716                }
717                Err(_) => {
718                    let scalar = args[arg_i].value()?.into_owned();
719                    crit_views.push(None);
720                    preds.push((pred, Some(scalar)));
721                }
722            }
723            arg_i += 2;
724        }
725        // Arrow fast path: require all criteria ranges to be Arrow-backed with equal dims
726        if ctx.arrow_fastpath_enabled() {
727            let mut all_arrow = true;
728            for v in &crit_views {
729                if let Some(rv) = v {
730                    if rv.as_arrow().is_none() || rv.dims() != dims {
731                        all_arrow = false;
732                        break;
733                    }
734                } else {
735                    all_arrow = false;
736                    break;
737                }
738            }
739            if all_arrow {
740                use crate::compute_prelude::boolean;
741                use arrow_array::BooleanArray;
742                let mut total: i64 = 0;
743                for c in 0..dims.1 {
744                    let mut mask_opt: Option<BooleanArray> = None;
745                    let mut ok = true;
746                    for (j, (pred, _)) in preds.iter().enumerate() {
747                        let av = crit_views[j].as_ref().unwrap().as_arrow().unwrap();
748                        if let Some(m) = ctx.get_criteria_mask(av, c, pred) {
749                            let cur = (*m).clone();
750                            mask_opt = Some(match mask_opt {
751                                None => cur,
752                                Some(prev) => boolean::and_kleene(&prev, &cur).unwrap(),
753                            });
754                        } else {
755                            ok = false;
756                            break;
757                        }
758                    }
759                    if !ok {
760                        break;
761                    }
762                    if let Some(mask) = mask_opt {
763                        use arrow_array::Array as _;
764                        total += (0..mask.len())
765                            .filter(|&i| mask.is_valid(i) && mask.value(i))
766                            .count() as i64;
767                    }
768                }
769                return Ok(LiteralValue::Number(total as f64));
770            }
771        }
772        let mut cnt = 0i64;
773        for row in 0..dims.0 {
774            for col in 0..dims.1 {
775                let mut all_match = true;
776                for (j, (pred, scalar_val)) in preds.iter().enumerate() {
777                    let crit_val = if let Some(ref view) = crit_views[j] {
778                        view.get_cell(row, col)
779                    } else if let Some(sv) = scalar_val {
780                        sv.clone()
781                    } else {
782                        LiteralValue::Empty
783                    };
784                    if !criteria_match(pred, &crit_val) {
785                        all_match = false;
786                        break;
787                    }
788                }
789                if all_match {
790                    cnt += 1;
791                }
792            }
793        }
794        Ok(LiteralValue::Number(cnt as f64))
795    }
796}
797
798/* ─────────────────────────── AVERAGEIFS() (moved) ──────────────────────────── */
799#[derive(Debug)]
800pub struct AverageIfsFn;
801impl Function for AverageIfsFn {
802    func_caps!(
803        PURE,
804        REDUCTION,
805        WINDOWED,
806        STREAM_OK,
807        PARALLEL_ARGS,
808        PARALLEL_CHUNKS
809    );
810    fn name(&self) -> &'static str {
811        "AVERAGEIFS"
812    }
813    fn min_args(&self) -> usize {
814        3
815    }
816    fn variadic(&self) -> bool {
817        true
818    }
819    fn arg_schema(&self) -> &'static [ArgSchema] {
820        &ARG_ANY_ONE[..]
821    }
822    fn eval_scalar<'a, 'b>(
823        &self,
824        args: &'a [ArgumentHandle<'a, 'b>],
825        ctx: &dyn FunctionContext,
826    ) -> Result<LiteralValue, ExcelError> {
827        if args.len() < 3 || !(args.len() - 1).is_multiple_of(2) {
828            return Ok(LiteralValue::Error(
829                ExcelError::new_value().with_message(format!(
830                    "AVERAGEIFS expects 1 avg_range followed by N pairs (criteria_range, criteria); got {} args",
831                    args.len()
832                )),
833            ));
834        }
835        // Resolve avg range
836        let avg_view = match args[0].range_view() {
837            Ok(v) => v,
838            Err(_) => {
839                // Scalar fallback: require scalar criteria and match; else #DIV/0!
840                let val = args[0].value()?;
841                for i in (1..args.len()).step_by(2) {
842                    let cval = args[i].value()?;
843                    let pred = crate::args::parse_criteria(args[i + 1].value()?.as_ref())?;
844                    if !criteria_match(&pred, cval.as_ref()) {
845                        return Ok(ExcelError::new_div().into());
846                    }
847                }
848                if let Ok(n) = coerce_num(val.as_ref()) {
849                    return Ok(LiteralValue::Number(n));
850                } else {
851                    return Ok(ExcelError::new_div().into());
852                }
853            }
854        };
855
856        // Collect criteria as views or scalars; compute union dims with avg_view (prefer flats)
857        let dims = avg_view.dims();
858        let mut crit_views: Vec<Option<crate::engine::range_view::RangeView<'_>>> = Vec::new();
859        let mut preds = Vec::new();
860        // Build views/preds
861        let mut arg_i = 1usize;
862        while arg_i + 1 < args.len() {
863            let pred = crate::args::parse_criteria(args[arg_i + 1].value()?.as_ref())?;
864            match args[arg_i].range_view() {
865                Ok(v) => {
866                    if v.dims() == (1, 1) {
867                        let scalar = v.get_cell(0, 0);
868                        crit_views.push(None);
869                        preds.push((pred, Some(scalar)));
870                    } else {
871                        // Do not expand avg_range dimensions; pad criteria to avg_range dims
872                        crit_views.push(Some(v));
873                        preds.push((pred, None));
874                    }
875                }
876                Err(_) => {
877                    let scalar = args[arg_i].value()?.into_owned();
878                    crit_views.push(None);
879                    preds.push((pred, Some(scalar)));
880                }
881            }
882            arg_i += 2;
883        }
884
885        let mut sum = 0.0f64;
886        let mut cnt = 0i64;
887        for row in 0..dims.0 {
888            for col in 0..dims.1 {
889                let mut all_match = true;
890                for (j, (pred, scalar_val)) in preds.iter().enumerate() {
891                    let crit_val = if let Some(ref view) = crit_views[j] {
892                        view.get_cell(row, col)
893                    } else if let Some(sv) = scalar_val {
894                        sv.clone()
895                    } else {
896                        LiteralValue::Empty
897                    };
898                    if !criteria_match(pred, &crit_val) {
899                        all_match = false;
900                        break;
901                    }
902                }
903                if all_match {
904                    let v = avg_view.get_cell(row, col);
905                    if let Ok(n) = coerce_num(&v) {
906                        sum += n;
907                        cnt += 1;
908                    }
909                }
910            }
911        }
912        if cnt == 0 {
913            return Ok(LiteralValue::Error(ExcelError::from_error_string(
914                "#DIV/0!",
915            )));
916        }
917        Ok(LiteralValue::Number(sum / cnt as f64))
918    }
919}
920
921/* ─────────────────────────── COUNTA() ──────────────────────────── */
922#[derive(Debug)]
923pub struct CountAFn; // counts non-empty (including empty text "")
924impl Function for CountAFn {
925    func_caps!(PURE, REDUCTION, WINDOWED, STREAM_OK);
926    fn name(&self) -> &'static str {
927        "COUNTA"
928    }
929    fn min_args(&self) -> usize {
930        1
931    }
932    fn variadic(&self) -> bool {
933        true
934    }
935    fn arg_schema(&self) -> &'static [ArgSchema] {
936        &ARG_ANY_ONE[..]
937    }
938    fn eval_scalar<'a, 'b>(
939        &self,
940        args: &'a [ArgumentHandle<'a, 'b>],
941        _ctx: &dyn FunctionContext,
942    ) -> Result<LiteralValue, ExcelError> {
943        let mut cnt = 0i64;
944        for a in args {
945            let (it, _) = materialize_iter(a);
946            for v in it {
947                match v {
948                    LiteralValue::Empty => {}
949                    _ => cnt += 1,
950                }
951            }
952        }
953        Ok(LiteralValue::Number(cnt as f64))
954    }
955}
956
957/* ─────────────────────────── COUNTBLANK() ──────────────────────────── */
958#[derive(Debug)]
959pub struct CountBlankFn; // counts truly empty cells and empty text
960impl Function for CountBlankFn {
961    func_caps!(PURE, REDUCTION, WINDOWED, STREAM_OK);
962    fn name(&self) -> &'static str {
963        "COUNTBLANK"
964    }
965    fn min_args(&self) -> usize {
966        1
967    }
968    fn variadic(&self) -> bool {
969        true
970    }
971    fn arg_schema(&self) -> &'static [ArgSchema] {
972        &ARG_ANY_ONE[..]
973    }
974    fn eval_scalar<'a, 'b>(
975        &self,
976        args: &'a [ArgumentHandle<'a, 'b>],
977        _ctx: &dyn FunctionContext,
978    ) -> Result<LiteralValue, ExcelError> {
979        let mut cnt = 0i64;
980        for a in args {
981            let (it, _) = materialize_iter(a);
982            for v in it {
983                match v {
984                    LiteralValue::Empty => cnt += 1,
985                    LiteralValue::Text(ref s) if s.is_empty() => cnt += 1,
986                    _ => {}
987                }
988            }
989        }
990        Ok(LiteralValue::Number(cnt as f64))
991    }
992}
993
994// Helper: materialize an argument (range or scalar) into an iterator of values and its 2D dims representation.
995fn materialize_iter<'a, 'b>(
996    arg: &'a ArgumentHandle<'a, 'b>,
997) -> (Box<dyn Iterator<Item = LiteralValue> + 'a>, (usize, usize)) {
998    if let Ok(view) = arg.range_view() {
999        let d = view.dims();
1000        let mut values: Vec<LiteralValue> = Vec::with_capacity(d.0 * d.1);
1001        // Re-resolve for borrow: the previous `view` is moved; get a fresh one
1002        if let Ok(rv2) = arg.range_view() {
1003            rv2.for_each_cell(&mut |cell| {
1004                values.push(cell.clone());
1005                Ok(())
1006            })
1007            .ok();
1008        }
1009        (Box::new(values.into_iter()), d)
1010    } else {
1011        let v = arg.value().unwrap().into_owned();
1012        (Box::new(std::iter::once(v)), (1, 1))
1013    }
1014}
1015
1016pub fn register_builtins() {
1017    use std::sync::Arc;
1018    crate::function_registry::register_function(Arc::new(SumIfFn));
1019    crate::function_registry::register_function(Arc::new(CountIfFn));
1020    crate::function_registry::register_function(Arc::new(SumIfsFn));
1021    crate::function_registry::register_function(Arc::new(CountIfsFn));
1022    crate::function_registry::register_function(Arc::new(AverageIfsFn));
1023    crate::function_registry::register_function(Arc::new(CountAFn));
1024    crate::function_registry::register_function(Arc::new(CountBlankFn));
1025}
1026
1027#[cfg(test)]
1028mod tests {
1029    use super::*;
1030    use crate::test_workbook::TestWorkbook;
1031    use crate::traits::ArgumentHandle;
1032    use formualizer_common::LiteralValue;
1033    use formualizer_parse::parser::{ASTNode, ASTNodeType};
1034    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1035        wb.interpreter()
1036    }
1037    fn lit(v: LiteralValue) -> ASTNode {
1038        ASTNode::new(ASTNodeType::Literal(v), None)
1039    }
1040
1041    #[test]
1042    fn sumif_basic() {
1043        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1044        let ctx = interp(&wb);
1045        let range = lit(LiteralValue::Array(vec![vec![
1046            LiteralValue::Int(1),
1047            LiteralValue::Int(2),
1048            LiteralValue::Int(3),
1049        ]]));
1050        let crit = lit(LiteralValue::Text(">1".into()));
1051        let args = vec![
1052            ArgumentHandle::new(&range, &ctx),
1053            ArgumentHandle::new(&crit, &ctx),
1054        ];
1055        let f = ctx.context.get_function("", "SUMIF").unwrap();
1056        assert_eq!(
1057            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1058            LiteralValue::Number(5.0)
1059        );
1060    }
1061
1062    #[test]
1063    fn sumif_with_sum_range() {
1064        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1065        let ctx = interp(&wb);
1066        let range = lit(LiteralValue::Array(vec![vec![
1067            LiteralValue::Int(1),
1068            LiteralValue::Int(0),
1069            LiteralValue::Int(1),
1070        ]]));
1071        let sum_range = lit(LiteralValue::Array(vec![vec![
1072            LiteralValue::Int(10),
1073            LiteralValue::Int(20),
1074            LiteralValue::Int(30),
1075        ]]));
1076        let crit = lit(LiteralValue::Text("=1".into()));
1077        let args = vec![
1078            ArgumentHandle::new(&range, &ctx),
1079            ArgumentHandle::new(&crit, &ctx),
1080            ArgumentHandle::new(&sum_range, &ctx),
1081        ];
1082        let f = ctx.context.get_function("", "SUMIF").unwrap();
1083        assert_eq!(
1084            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1085            LiteralValue::Number(40.0)
1086        );
1087    }
1088
1089    #[test]
1090    fn sumif_mismatched_ranges_now_pad_with_empty() {
1091        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1092        let ctx = interp(&wb);
1093        // sum_range: 2x2
1094        let sum = lit(LiteralValue::Array(vec![
1095            vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1096            vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1097        ]));
1098        // criteria range: 3x2 (extra row should be ignored due to iterating sum_range dims)
1099        let crit_range = lit(LiteralValue::Array(vec![
1100            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1101            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1102            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1103        ]));
1104        let crit = lit(LiteralValue::Text("=1".into()));
1105        let args = vec![
1106            ArgumentHandle::new(&crit_range, &ctx),
1107            ArgumentHandle::new(&crit, &ctx),
1108            ArgumentHandle::new(&sum, &ctx),
1109        ];
1110        let f = ctx.context.get_function("", "SUMIF").unwrap();
1111        assert_eq!(
1112            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1113            LiteralValue::Number(10.0)
1114        );
1115    }
1116
1117    #[test]
1118    fn countif_text_wildcard() {
1119        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1120        let ctx = interp(&wb);
1121        let rng = lit(LiteralValue::Array(vec![vec![
1122            LiteralValue::Text("alpha".into()),
1123            LiteralValue::Text("beta".into()),
1124            LiteralValue::Text("alphabet".into()),
1125        ]]));
1126        let crit = lit(LiteralValue::Text("al*".into()));
1127        let args = vec![
1128            ArgumentHandle::new(&rng, &ctx),
1129            ArgumentHandle::new(&crit, &ctx),
1130        ];
1131        let f = ctx.context.get_function("", "COUNTIF").unwrap();
1132        assert_eq!(
1133            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1134            LiteralValue::Number(2.0)
1135        );
1136    }
1137
1138    #[test]
1139    fn sumifs_multiple_criteria() {
1140        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1141        let ctx = interp(&wb);
1142        let sum = lit(LiteralValue::Array(vec![vec![
1143            LiteralValue::Int(10),
1144            LiteralValue::Int(20),
1145            LiteralValue::Int(30),
1146            LiteralValue::Int(40),
1147        ]]));
1148        let city = lit(LiteralValue::Array(vec![vec![
1149            LiteralValue::Text("Bellevue".into()),
1150            LiteralValue::Text("Issaquah".into()),
1151            LiteralValue::Text("Bellevue".into()),
1152            LiteralValue::Text("Issaquah".into()),
1153        ]]));
1154        let beds = lit(LiteralValue::Array(vec![vec![
1155            LiteralValue::Int(2),
1156            LiteralValue::Int(3),
1157            LiteralValue::Int(4),
1158            LiteralValue::Int(5),
1159        ]]));
1160        let c_city = lit(LiteralValue::Text("Bellevue".into()));
1161        let c_beds = lit(LiteralValue::Text(">=4".into()));
1162        let args = vec![
1163            ArgumentHandle::new(&sum, &ctx),
1164            ArgumentHandle::new(&city, &ctx),
1165            ArgumentHandle::new(&c_city, &ctx),
1166            ArgumentHandle::new(&beds, &ctx),
1167            ArgumentHandle::new(&c_beds, &ctx),
1168        ];
1169        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1170        assert_eq!(
1171            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1172            LiteralValue::Number(30.0)
1173        );
1174    }
1175
1176    #[test]
1177    fn countifs_basic() {
1178        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1179        let ctx = interp(&wb);
1180        let city = lit(LiteralValue::Array(vec![vec![
1181            LiteralValue::Text("a".into()),
1182            LiteralValue::Text("b".into()),
1183            LiteralValue::Text("a".into()),
1184        ]]));
1185        let beds = lit(LiteralValue::Array(vec![vec![
1186            LiteralValue::Int(1),
1187            LiteralValue::Int(2),
1188            LiteralValue::Int(3),
1189        ]]));
1190        let c_city = lit(LiteralValue::Text("a".into()));
1191        let c_beds = lit(LiteralValue::Text(">1".into()));
1192        let args = vec![
1193            ArgumentHandle::new(&city, &ctx),
1194            ArgumentHandle::new(&c_city, &ctx),
1195            ArgumentHandle::new(&beds, &ctx),
1196            ArgumentHandle::new(&c_beds, &ctx),
1197        ];
1198        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1199        assert_eq!(
1200            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1201            LiteralValue::Number(1.0)
1202        );
1203    }
1204
1205    #[test]
1206    fn averageifs_div0() {
1207        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1208        let ctx = interp(&wb);
1209        let avg = lit(LiteralValue::Array(vec![vec![
1210            LiteralValue::Int(1),
1211            LiteralValue::Int(2),
1212        ]]));
1213        let crit_rng = lit(LiteralValue::Array(vec![vec![
1214            LiteralValue::Int(0),
1215            LiteralValue::Int(0),
1216        ]]));
1217        let crit = lit(LiteralValue::Text(">0".into()));
1218        let args = vec![
1219            ArgumentHandle::new(&avg, &ctx),
1220            ArgumentHandle::new(&crit_rng, &ctx),
1221            ArgumentHandle::new(&crit, &ctx),
1222        ];
1223        let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1224        match f.dispatch(&args, &ctx.function_context(None)).unwrap() {
1225            LiteralValue::Error(e) => assert_eq!(e, "#DIV/0!"),
1226            _ => panic!("expected div0"),
1227        }
1228    }
1229
1230    #[test]
1231    fn counta_and_countblank() {
1232        let wb = TestWorkbook::new()
1233            .with_function(std::sync::Arc::new(CountAFn))
1234            .with_function(std::sync::Arc::new(CountBlankFn));
1235        let ctx = interp(&wb);
1236        let arr = lit(LiteralValue::Array(vec![vec![
1237            LiteralValue::Empty,
1238            LiteralValue::Text("".into()),
1239            LiteralValue::Int(5),
1240        ]]));
1241        let args = vec![ArgumentHandle::new(&arr, &ctx)];
1242        let counta = ctx.context.get_function("", "COUNTA").unwrap();
1243        let countblank = ctx.context.get_function("", "COUNTBLANK").unwrap();
1244        assert_eq!(
1245            counta.dispatch(&args, &ctx.function_context(None)).unwrap(),
1246            LiteralValue::Number(2.0)
1247        );
1248        assert_eq!(
1249            countblank
1250                .dispatch(&args, &ctx.function_context(None))
1251                .unwrap(),
1252            LiteralValue::Number(2.0)
1253        );
1254    }
1255
1256    // ───────── Parity tests (window vs scalar) ─────────
1257    #[test]
1258    #[ignore]
1259    fn sumif_window_parity() {
1260        let f = SumIfFn; // direct instance
1261        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1262        let ctx = interp(&wb);
1263        let range = lit(LiteralValue::Array(vec![vec![
1264            LiteralValue::Int(1),
1265            LiteralValue::Int(2),
1266            LiteralValue::Int(3),
1267        ]]));
1268        let crit = lit(LiteralValue::Text(">1".into()));
1269        let args = vec![
1270            ArgumentHandle::new(&range, &ctx),
1271            ArgumentHandle::new(&crit, &ctx),
1272        ];
1273        let fctx = ctx.function_context(None);
1274        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1275            &args,
1276            &fctx,
1277            crate::window_ctx::WindowSpec::default(),
1278        );
1279        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1280        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1281        assert_eq!(window_val, scalar);
1282    }
1283
1284    #[test]
1285    #[ignore]
1286    fn sumifs_window_parity() {
1287        let f = SumIfsFn;
1288        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1289        let ctx = interp(&wb);
1290        let sum = lit(LiteralValue::Array(vec![vec![
1291            LiteralValue::Int(10),
1292            LiteralValue::Int(20),
1293            LiteralValue::Int(30),
1294            LiteralValue::Int(40),
1295        ]]));
1296        let city = lit(LiteralValue::Array(vec![vec![
1297            LiteralValue::Text("Bellevue".into()),
1298            LiteralValue::Text("Issaquah".into()),
1299            LiteralValue::Text("Bellevue".into()),
1300            LiteralValue::Text("Issaquah".into()),
1301        ]]));
1302        let beds = lit(LiteralValue::Array(vec![vec![
1303            LiteralValue::Int(2),
1304            LiteralValue::Int(3),
1305            LiteralValue::Int(4),
1306            LiteralValue::Int(5),
1307        ]]));
1308        let c_city = lit(LiteralValue::Text("Bellevue".into()));
1309        let c_beds = lit(LiteralValue::Text(">=4".into()));
1310        let args = vec![
1311            ArgumentHandle::new(&sum, &ctx),
1312            ArgumentHandle::new(&city, &ctx),
1313            ArgumentHandle::new(&c_city, &ctx),
1314            ArgumentHandle::new(&beds, &ctx),
1315            ArgumentHandle::new(&c_beds, &ctx),
1316        ];
1317        let fctx = ctx.function_context(None);
1318        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1319            &args,
1320            &fctx,
1321            crate::window_ctx::WindowSpec::default(),
1322        );
1323        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1324        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1325        assert_eq!(window_val, scalar);
1326    }
1327
1328    #[test]
1329    fn sumifs_broadcasts_1x1_criteria_over_range() {
1330        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1331        let ctx = interp(&wb);
1332        // sum_range: column vector [10, 20]
1333        let sum = lit(LiteralValue::Array(vec![
1334            vec![LiteralValue::Int(10)],
1335            vec![LiteralValue::Int(20)],
1336        ]));
1337        // criteria_range: column vector ["A", "B"]
1338        let tags = lit(LiteralValue::Array(vec![
1339            vec![LiteralValue::Text("A".into())],
1340            vec![LiteralValue::Text("B".into())],
1341        ]));
1342        // criteria: 1x1 array acting as scalar "A"
1343        let c_tag = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1344            "A".into(),
1345        )]]));
1346        let args = vec![
1347            ArgumentHandle::new(&sum, &ctx),
1348            ArgumentHandle::new(&tags, &ctx),
1349            ArgumentHandle::new(&c_tag, &ctx),
1350        ];
1351        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1352        assert_eq!(
1353            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1354            LiteralValue::Number(10.0)
1355        );
1356    }
1357
1358    #[test]
1359    fn countifs_broadcasts_1x1_criteria_over_row() {
1360        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1361        let ctx = interp(&wb);
1362        // criteria_range: row [1,2,3,4]
1363        let nums = lit(LiteralValue::Array(vec![vec![
1364            LiteralValue::Int(1),
1365            LiteralValue::Int(2),
1366            LiteralValue::Int(3),
1367            LiteralValue::Int(4),
1368        ]]));
1369        // criteria: 1x1 array ">=3"
1370        let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1371            ">=3".into(),
1372        )]]));
1373        let args = vec![
1374            ArgumentHandle::new(&nums, &ctx),
1375            ArgumentHandle::new(&crit, &ctx),
1376        ];
1377        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1378        assert_eq!(
1379            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1380            LiteralValue::Number(2.0)
1381        );
1382    }
1383
1384    #[test]
1385    fn sumifs_empty_ranges_with_1x1_criteria_produce_zero() {
1386        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1387        let ctx = interp(&wb);
1388        // Empty ranges (0x0) simulate unused whole-column resolved empty
1389        let empty = lit(LiteralValue::Array(Vec::new()));
1390        // 1x1 criteria (array)
1391        let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1392            "X".into(),
1393        )]]));
1394        let args = vec![
1395            ArgumentHandle::new(&empty, &ctx),
1396            ArgumentHandle::new(&empty, &ctx),
1397            ArgumentHandle::new(&crit, &ctx),
1398        ];
1399        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1400        assert_eq!(
1401            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1402            LiteralValue::Number(0.0)
1403        );
1404    }
1405
1406    #[test]
1407    fn sumifs_mismatched_ranges_now_pad_with_empty() {
1408        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1409        let ctx = interp(&wb);
1410        // sum_range: 2x2
1411        let sum = lit(LiteralValue::Array(vec![
1412            vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1413            vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1414        ]));
1415        // criteria_range: 3x2 (different rows - extra row will match against padded empty values)
1416        let crit_range = lit(LiteralValue::Array(vec![
1417            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1418            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1419            vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1420        ]));
1421        // scalar criterion
1422        let crit = lit(LiteralValue::Text("=1".into()));
1423        let args = vec![
1424            ArgumentHandle::new(&sum, &ctx),
1425            ArgumentHandle::new(&crit_range, &ctx),
1426            ArgumentHandle::new(&crit, &ctx),
1427        ];
1428        let f = ctx.context.get_function("", "SUMIFS").unwrap();
1429        // With padding, sum_range gets padded with empties for row 3
1430        // Rows 1-2 match criteria (all 1s), row 3 has empties which don't match =1
1431        // So we sum: 1 + 2 + 3 + 4 = 10
1432        assert_eq!(
1433            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1434            LiteralValue::Number(10.0)
1435        );
1436    }
1437
1438    #[test]
1439    fn countifs_mismatched_ranges_pad_and_broadcast() {
1440        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1441        let ctx = interp(&wb);
1442        // criteria_range1: 2x1 -> [1,1]
1443        let r1 = lit(LiteralValue::Array(vec![
1444            vec![LiteralValue::Int(1)],
1445            vec![LiteralValue::Int(1)],
1446        ]));
1447        // criteria1: "=1"
1448        let c1 = lit(LiteralValue::Text("=1".into()));
1449        // criteria_range2: 3x1 -> [1,1,1]
1450        let r2 = lit(LiteralValue::Array(vec![
1451            vec![LiteralValue::Int(1)],
1452            vec![LiteralValue::Int(1)],
1453            vec![LiteralValue::Int(1)],
1454        ]));
1455        // criteria2: "=1"
1456        let c2 = lit(LiteralValue::Text("=1".into()));
1457        let args = vec![
1458            ArgumentHandle::new(&r1, &ctx),
1459            ArgumentHandle::new(&c1, &ctx),
1460            ArgumentHandle::new(&r2, &ctx),
1461            ArgumentHandle::new(&c2, &ctx),
1462        ];
1463        let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1464        // Union rows = 3; row3 has r1=Empty (padded), which doesn't match =1; expect 2
1465        assert_eq!(
1466            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1467            LiteralValue::Number(2.0)
1468        );
1469    }
1470
1471    #[test]
1472    fn averageifs_mismatched_ranges_pad() {
1473        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1474        let ctx = interp(&wb);
1475        // avg_range: 2x1 -> [10,20]
1476        let avg = lit(LiteralValue::Array(vec![
1477            vec![LiteralValue::Int(10)],
1478            vec![LiteralValue::Int(20)],
1479        ]));
1480        // criteria_range: 3x1 -> [1,1,2]
1481        let r1 = lit(LiteralValue::Array(vec![
1482            vec![LiteralValue::Int(1)],
1483            vec![LiteralValue::Int(1)],
1484            vec![LiteralValue::Int(2)],
1485        ]));
1486        let c1 = lit(LiteralValue::Text("=1".into()));
1487        let args = vec![
1488            ArgumentHandle::new(&avg, &ctx),
1489            ArgumentHandle::new(&r1, &ctx),
1490            ArgumentHandle::new(&c1, &ctx),
1491        ];
1492        let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1493        // Only first two rows match; expect (10+20)/2 = 15
1494        assert_eq!(
1495            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1496            LiteralValue::Number(15.0)
1497        );
1498    }
1499
1500    #[test]
1501    #[ignore]
1502    fn countifs_window_parity() {
1503        let f = CountIfsFn;
1504        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1505        let ctx = interp(&wb);
1506        let city = lit(LiteralValue::Array(vec![vec![
1507            LiteralValue::Text("a".into()),
1508            LiteralValue::Text("b".into()),
1509            LiteralValue::Text("a".into()),
1510        ]]));
1511        let beds = lit(LiteralValue::Array(vec![vec![
1512            LiteralValue::Int(1),
1513            LiteralValue::Int(2),
1514            LiteralValue::Int(3),
1515        ]]));
1516        let c_city = lit(LiteralValue::Text("a".into()));
1517        let c_beds = lit(LiteralValue::Text(">1".into()));
1518        let args = vec![
1519            ArgumentHandle::new(&city, &ctx),
1520            ArgumentHandle::new(&c_city, &ctx),
1521            ArgumentHandle::new(&beds, &ctx),
1522            ArgumentHandle::new(&c_beds, &ctx),
1523        ];
1524        let fctx = ctx.function_context(None);
1525        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1526            &args,
1527            &fctx,
1528            crate::window_ctx::WindowSpec::default(),
1529        );
1530        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1531        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1532        assert_eq!(window_val, scalar);
1533    }
1534
1535    #[test]
1536    #[ignore]
1537    fn averageifs_window_parity() {
1538        let f = AverageIfsFn;
1539        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1540        let ctx = interp(&wb);
1541        let avg = lit(LiteralValue::Array(vec![vec![
1542            LiteralValue::Int(10),
1543            LiteralValue::Int(20),
1544            LiteralValue::Int(30),
1545        ]]));
1546        let crit_rng = lit(LiteralValue::Array(vec![vec![
1547            LiteralValue::Int(0),
1548            LiteralValue::Int(1),
1549            LiteralValue::Int(1),
1550        ]]));
1551        let crit = lit(LiteralValue::Text(">0".into()));
1552        let args = vec![
1553            ArgumentHandle::new(&avg, &ctx),
1554            ArgumentHandle::new(&crit_rng, &ctx),
1555            ArgumentHandle::new(&crit, &ctx),
1556        ];
1557        let fctx = ctx.function_context(None);
1558        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1559            &args,
1560            &fctx,
1561            crate::window_ctx::WindowSpec::default(),
1562        );
1563        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1564        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1565        assert_eq!(window_val, scalar);
1566    }
1567    #[test]
1568    #[ignore]
1569    fn counta_window_parity() {
1570        let f = CountAFn;
1571        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountAFn));
1572        let ctx = interp(&wb);
1573        let arr = lit(LiteralValue::Array(vec![vec![
1574            LiteralValue::Empty,
1575            LiteralValue::Int(1),
1576            LiteralValue::Text("".into()),
1577        ]]));
1578        let args = vec![ArgumentHandle::new(&arr, &ctx)];
1579        let fctx = ctx.function_context(None);
1580        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1581            &args,
1582            &fctx,
1583            crate::window_ctx::WindowSpec::default(),
1584        );
1585        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1586        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1587        assert_eq!(window_val, scalar);
1588    }
1589    #[test]
1590    #[ignore]
1591    fn countblank_window_parity() {
1592        let f = CountBlankFn;
1593        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountBlankFn));
1594        let ctx = interp(&wb);
1595        let arr = lit(LiteralValue::Array(vec![vec![
1596            LiteralValue::Empty,
1597            LiteralValue::Int(1),
1598            LiteralValue::Text("".into()),
1599        ]]));
1600        let args = vec![ArgumentHandle::new(&arr, &ctx)];
1601        let fctx = ctx.function_context(None);
1602        let mut wctx = crate::window_ctx::SimpleWindowCtx::new(
1603            &args,
1604            &fctx,
1605            crate::window_ctx::WindowSpec::default(),
1606        );
1607        let window_val = f.eval_window(&mut wctx).expect("window path").unwrap();
1608        let scalar = f.eval_scalar(&args, &fctx).unwrap();
1609        assert_eq!(window_val, scalar);
1610    }
1611
1612    // ───────── Criteria parsing edge cases ─────────
1613    #[test]
1614    fn criteria_numeric_string_vs_number() {
1615        // SUMIF over numeric cells with criteria expressed as text ">=2" and "=3"
1616        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1617        let ctx = interp(&wb);
1618        let range = lit(LiteralValue::Array(vec![vec![
1619            LiteralValue::Int(1),
1620            LiteralValue::Number(2.0),
1621            LiteralValue::Int(3),
1622        ]]));
1623        let ge2 = lit(LiteralValue::Text(">=2".into()));
1624        let eq3 = lit(LiteralValue::Text("=3".into()));
1625        let args_ge2 = vec![
1626            ArgumentHandle::new(&range, &ctx),
1627            ArgumentHandle::new(&ge2, &ctx),
1628        ];
1629        let args_eq3 = vec![
1630            ArgumentHandle::new(&range, &ctx),
1631            ArgumentHandle::new(&eq3, &ctx),
1632        ];
1633        let f = ctx.context.get_function("", "SUMIF").unwrap();
1634        assert_eq!(
1635            f.dispatch(&args_ge2, &ctx.function_context(None)).unwrap(),
1636            LiteralValue::Number(5.0)
1637        ); // 2+3
1638        assert_eq!(
1639            f.dispatch(&args_eq3, &ctx.function_context(None)).unwrap(),
1640            LiteralValue::Number(3.0)
1641        );
1642    }
1643
1644    #[test]
1645    fn criteria_wildcards_patterns() {
1646        // COUNTIF with wildcard patterns
1647        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1648        let ctx = interp(&wb);
1649        let data = lit(LiteralValue::Array(vec![vec![
1650            LiteralValue::Text("alpha".into()),
1651            LiteralValue::Text("alphabet".into()),
1652            LiteralValue::Text("alp".into()),
1653            LiteralValue::Text("al".into()),
1654            LiteralValue::Text("beta".into()),
1655        ]]));
1656        let pat_al_star = lit(LiteralValue::Text("al*".into())); // matches all starting with al
1657        let pat_q = lit(LiteralValue::Text("alp?".into())); // matches four-char starting alp?
1658        let pat_star_et = lit(LiteralValue::Text("*et".into())); // ends with et
1659        let f = ctx.context.get_function("", "COUNTIF").unwrap();
1660        let ctxf = ctx.function_context(None);
1661        // Current wildcard matcher is case-sensitive and non-greedy but supports * and ?; pattern 'al*' should match alpha, alphabet, alp, al (4)
1662        assert_eq!(
1663            f.dispatch(
1664                &[
1665                    ArgumentHandle::new(&data, &ctx),
1666                    ArgumentHandle::new(&pat_al_star, &ctx)
1667                ],
1668                &ctxf
1669            )
1670            .unwrap(),
1671            LiteralValue::Number(4.0)
1672        );
1673        // 'alp?' matches exactly four-char strings starting with 'alp'. We have 'alph' prefix inside 'alpha' but pattern must consume entire string, so only 'alp?' -> no exact 4-length match; expect 0.
1674        assert_eq!(
1675            f.dispatch(
1676                &[
1677                    ArgumentHandle::new(&data, &ctx),
1678                    ArgumentHandle::new(&pat_q, &ctx)
1679                ],
1680                &ctxf
1681            )
1682            .unwrap(),
1683            LiteralValue::Number(0.0)
1684        );
1685        // '*et' matches words ending with 'et' (alphabet)
1686        assert_eq!(
1687            f.dispatch(
1688                &[
1689                    ArgumentHandle::new(&data, &ctx),
1690                    ArgumentHandle::new(&pat_star_et, &ctx)
1691                ],
1692                &ctxf
1693            )
1694            .unwrap(),
1695            LiteralValue::Number(1.0)
1696        );
1697    }
1698
1699    #[test]
1700    fn criteria_boolean_text_and_numeric_equivalence() {
1701        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1702        let ctx = interp(&wb);
1703        let data = lit(LiteralValue::Array(vec![vec![
1704            LiteralValue::Boolean(true),
1705            LiteralValue::Boolean(false),
1706            LiteralValue::Text("TRUE".into()),
1707            LiteralValue::Int(1),
1708            LiteralValue::Int(0),
1709        ]]));
1710        // Criteria TRUE should match Boolean(true) only (NOT text TRUE unless equality logic coerces); we rely on current parse -> Eq(Boolean(true))
1711        let crit_true = lit(LiteralValue::Text("TRUE".into()));
1712        let args_true = vec![
1713            ArgumentHandle::new(&data, &ctx),
1714            ArgumentHandle::new(&crit_true, &ctx),
1715        ];
1716        let f = ctx.context.get_function("", "COUNTIF").unwrap();
1717        let res = f.dispatch(&args_true, &ctx.function_context(None)).unwrap();
1718        // Expect 1 match (the boolean true) because Text("TRUE") is parsed to boolean predicate Eq(Boolean(true))
1719        assert_eq!(res, LiteralValue::Number(1.0));
1720    }
1721
1722    #[test]
1723    fn criteria_empty_and_blank() {
1724        // COUNTIF to distinguish blank vs non-blank using criteria "=" and "<>" patterns
1725        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1726        let ctx = interp(&wb);
1727        let arr = lit(LiteralValue::Array(vec![vec![
1728            LiteralValue::Empty,
1729            LiteralValue::Text("".into()),
1730            LiteralValue::Text(" ".into()),
1731            LiteralValue::Int(0),
1732        ]]));
1733        let crit_blank = lit(LiteralValue::Text("=".into())); // equality with empty -> treated as Eq(Text("")) by parser? Actually '=' prefix branch with rhs '' -> Eq(Number?) fallback -> becomes Eq(Text(""))
1734        let crit_not_blank = lit(LiteralValue::Text("<>".into())); // Eq(Text("<>")) fallback due to parse path; document current semantics
1735        let f = ctx.context.get_function("", "COUNTIF").unwrap();
1736        let ctxf = ctx.function_context(None);
1737        let blank_result = f
1738            .dispatch(
1739                &[
1740                    ArgumentHandle::new(&arr, &ctx),
1741                    ArgumentHandle::new(&crit_blank, &ctx),
1742                ],
1743                &ctxf,
1744            )
1745            .unwrap();
1746        // Current parser: '=' recognized, rhs empty -> numeric parse fails, becomes Eq(Text("")) so matches Empty? criteria_match treats Eq(Text) vs Empty -> false, so only Text("") counts.
1747        // After equality adjustment, '=' with empty rhs matches both true blank and empty text => expect 2.
1748        assert_eq!(blank_result, LiteralValue::Number(2.0));
1749        let not_blank_result = f
1750            .dispatch(
1751                &[
1752                    ArgumentHandle::new(&arr, &ctx),
1753                    ArgumentHandle::new(&crit_not_blank, &ctx),
1754                ],
1755                &ctxf,
1756            )
1757            .unwrap();
1758        // Expect 0 with current simplistic parsing (since becomes Eq(Text("<>")) none match) -> acts as regression guard; adjust if semantics improved later.
1759        // '<>' with empty rhs -> Ne(Text("")) now excludes both blank and empty text; counts others (space, 0) => 2.
1760        assert_eq!(not_blank_result, LiteralValue::Number(2.0));
1761    }
1762
1763    #[test]
1764    fn criteria_non_numeric_relational_fallback() {
1765        // SUMIF with relational operator against non-numeric should degrade to equality on full string per parse_criteria implementation comment.
1766        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1767        let ctx = interp(&wb);
1768        let range = lit(LiteralValue::Array(vec![vec![
1769            LiteralValue::Text("apple".into()),
1770            LiteralValue::Text("banana".into()),
1771        ]]));
1772        let sum_range = lit(LiteralValue::Array(vec![vec![
1773            LiteralValue::Int(10),
1774            LiteralValue::Int(20),
1775        ]]));
1776        let crit = lit(LiteralValue::Text(">apple".into())); // will parse '>' then fail numeric parse -> equality on full expression '>apple'
1777        let args = vec![
1778            ArgumentHandle::new(&range, &ctx),
1779            ArgumentHandle::new(&crit, &ctx),
1780            ArgumentHandle::new(&sum_range, &ctx),
1781        ];
1782        let f = ctx.context.get_function("", "SUMIF").unwrap();
1783        // No element equals the literal string '>apple'
1784        assert_eq!(
1785            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1786            LiteralValue::Number(0.0)
1787        );
1788    }
1789
1790    #[test]
1791    fn criteria_scientific_notation() {
1792        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1793        let ctx = interp(&wb);
1794        let nums = lit(LiteralValue::Array(vec![vec![
1795            LiteralValue::Number(1000.0),
1796            LiteralValue::Number(1500.0),
1797            LiteralValue::Number(999.0),
1798        ]]));
1799        let crit = lit(LiteralValue::Text(">1e3".into())); // should parse as >1000
1800        let args = vec![
1801            ArgumentHandle::new(&nums, &ctx),
1802            ArgumentHandle::new(&crit, &ctx),
1803        ];
1804        let f = ctx.context.get_function("", "SUMIF").unwrap();
1805        // >1000 matches 1500 only (strict greater)
1806        assert_eq!(
1807            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1808            LiteralValue::Number(1500.0)
1809        );
1810    }
1811}
formualizer_eval/builtins/math/criteria_aggregates.rs

formualizer_eval/builtins/math/
criteria_aggregates.rs
