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

formualizer_eval/builtins/math/
criteria_aggregates.rs
