formualizer_eval/builtins/math/criteria_aggregates.rs
1use super::super::utils::{ARG_ANY_ONE, coerce_num, criteria_match};
2use crate::args::ArgSchema;
3use crate::compute_prelude::{boolean, cmp, filter_array};
4use crate::function::Function;
5use crate::function_contract::{CriteriaValueRange, FunctionArityRule, FunctionDependencyContract};
6use crate::traits::{ArgumentHandle, FunctionContext};
7use arrow::compute::kernels::aggregate::sum_array;
8use arrow_array::types::Float64Type;
9use arrow_array::{Array as _, BooleanArray, Float64Array};
10use formualizer_common::{ExcelError, LiteralValue};
11use formualizer_macros::func_caps;
12
13#[cfg(test)]
14pub(crate) mod test_hooks {
15 use std::cell::Cell;
16
17 thread_local! {
18 static CACHED_MASK_SLICE_FAST: Cell<usize> = const { Cell::new(0) };
19 static CACHED_MASK_PAD_PARTIAL: Cell<usize> = const { Cell::new(0) };
20 static CACHED_MASK_PAD_ALL_FILL: Cell<usize> = const { Cell::new(0) };
21 }
22
23 pub fn reset_cached_mask_counters() {
24 CACHED_MASK_SLICE_FAST.with(|c| c.set(0));
25 CACHED_MASK_PAD_PARTIAL.with(|c| c.set(0));
26 CACHED_MASK_PAD_ALL_FILL.with(|c| c.set(0));
27 }
28
29 pub fn cached_mask_counters() -> (usize, usize, usize) {
30 let a = CACHED_MASK_SLICE_FAST.with(|c| c.get());
31 let b = CACHED_MASK_PAD_PARTIAL.with(|c| c.get());
32 let d = CACHED_MASK_PAD_ALL_FILL.with(|c| c.get());
33 (a, b, d)
34 }
35
36 pub(crate) fn inc_slice_fast() {
37 CACHED_MASK_SLICE_FAST.with(|c| c.set(c.get() + 1));
38 }
39 pub(crate) fn inc_pad_partial() {
40 CACHED_MASK_PAD_PARTIAL.with(|c| c.set(c.get() + 1));
41 }
42 pub(crate) fn inc_pad_all_fill() {
43 CACHED_MASK_PAD_ALL_FILL.with(|c| c.set(c.get() + 1));
44 }
45}
46
47/*
48Criteria-driven aggregation functions:
49 - SUMIF(range, criteria, [sum_range])
50 - SUMIFS(sum_range, criteria_range1, criteria1, ...)
51 - COUNTIF(range, criteria)
52 - COUNTIFS(criteria_range1, criteria1, ...)
53 - AVERAGEIFS(avg_range, criteria_range1, criteria1, ...) (moved here from aggregate.rs)
54 - COUNTA(value1, value2, ...)
55 - COUNTBLANK(range_or_values...)
56
57Design notes:
58 * Validation of shape parity for multi-criteria aggregations (#VALUE! on mismatch).
59 * Criteria parsing reused via crate::args::parse_criteria and criteria_match helper in utils.
60 * Streaming optimization deferred (TODO(perf)).
61*/
62
63#[derive(Debug, Clone, Copy, PartialEq, Eq)]
64enum AggregationType {
65 Sum,
66 Count,
67 Average,
68}
69
70fn eval_if_family<'a, 'b>(
71 args: &[ArgumentHandle<'a, 'b>],
72 ctx: &dyn FunctionContext<'b>,
73 agg_type: AggregationType,
74 multi: bool,
75) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
76 let mut sum_view: Option<crate::engine::range_view::RangeView<'_>> = None;
77 let mut sum_scalar: Option<LiteralValue> = None;
78 let mut crit_specs = Vec::new();
79
80 if !multi {
81 // Single criterion: IF(range, criteria, [target_range])
82 if args.len() < 2 || args.len() > 3 {
83 return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
84 ExcelError::new_value().with_message(format!(
85 "Function expects 2 or 3 arguments, got {}",
86 args.len()
87 )),
88 )));
89 }
90 let pred = crate::args::parse_criteria(&args[1].value()?.into_literal())?;
91 let crit_rv = args[0].range_view().ok();
92 let crit_val = if crit_rv.is_none() {
93 Some(args[0].value()?.into_literal())
94 } else {
95 None
96 };
97 crit_specs.push((crit_rv, pred, crit_val));
98
99 if agg_type != AggregationType::Count {
100 if args.len() == 3 {
101 if let Ok(v) = args[2].range_view() {
102 let crit_dims = crit_specs[0].0.as_ref().map(|v| v.dims()).unwrap_or((1, 1));
103 sum_view = Some(v.expand_to(crit_dims.0, crit_dims.1));
104 } else {
105 sum_scalar = Some(args[2].value()?.into_literal());
106 }
107 } else {
108 // Default target is criteria range
109 if let Ok(v) = args[0].range_view() {
110 sum_view = Some(v);
111 } else {
112 sum_scalar = Some(args[0].value()?.into_literal());
113 }
114 }
115 }
116 } else {
117 // Multi criteria: IFS(target_range, crit_range1, crit1, ...) or COUNTIFS(crit_range1, crit1, ...)
118 if agg_type == AggregationType::Count {
119 if args.len() < 2 || !args.len().is_multiple_of(2) {
120 return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
121 ExcelError::new_value().with_message(format!(
122 "COUNTIFS expects N pairs (criteria_range, criteria); got {} args",
123 args.len()
124 )),
125 )));
126 }
127 for i in (0..args.len()).step_by(2) {
128 let mut rv = args[i].range_view().ok();
129 let mut val: Option<LiteralValue> = None;
130
131 // Broadcast semantics: treat 1x1 criteria ranges as scalar criteria.
132 if let Some(ref view) = rv {
133 let (r, c) = view.dims();
134 if r == 1 && c == 1 {
135 val = Some(view.as_1x1().unwrap_or(LiteralValue::Empty));
136 rv = None;
137 }
138 }
139
140 if val.is_none() && rv.is_none() {
141 val = Some(args[i].value()?.into_literal());
142 }
143
144 let pred = crate::args::parse_criteria(&args[i + 1].value()?.into_literal())?;
145 crit_specs.push((rv, pred, val));
146 }
147 } else {
148 if args.len() < 3 || !(args.len() - 1).is_multiple_of(2) {
149 return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
150 ExcelError::new_value().with_message(format!(
151 "Function expects 1 target_range followed by N pairs (criteria_range, criteria); got {} args",
152 args.len()
153 )),
154 )));
155 }
156 if let Ok(v) = args[0].range_view() {
157 sum_view = Some(v);
158 } else {
159 sum_scalar = Some(args[0].value()?.into_literal());
160 }
161 for i in (1..args.len()).step_by(2) {
162 let mut rv = args[i].range_view().ok();
163 let mut val: Option<LiteralValue> = None;
164
165 // Broadcast semantics: treat 1x1 criteria ranges as scalar criteria.
166 if let Some(ref view) = rv {
167 let (r, c) = view.dims();
168 if r == 1 && c == 1 {
169 val = Some(view.as_1x1().unwrap_or(LiteralValue::Empty));
170 rv = None;
171 }
172 }
173
174 if val.is_none() && rv.is_none() {
175 val = Some(args[i].value()?.into_literal());
176 }
177
178 let pred = crate::args::parse_criteria(&args[i + 1].value()?.into_literal())?;
179 crit_specs.push((rv, pred, val));
180 }
181 }
182 }
183
184 // Determine union dimensions
185 let mut dims = (1usize, 1usize);
186 if let Some(ref sv) = sum_view {
187 dims = sv.dims();
188 }
189 for (rv, _, _) in &crit_specs {
190 if let Some(v) = rv {
191 let vd = v.dims();
192 dims.0 = dims.0.max(vd.0);
193 dims.1 = dims.1.max(vd.1);
194 }
195 }
196
197 // Excel SUMIF rules: if target_range is given, it expands from its top-left to match criteria range dims
198 // SUMIFS rules: all ranges must have same dims.
199 // Our implementation will use dims as the iteration space and broadcast/pad.
200
201 let mut total_sum = 0.0f64;
202 let mut total_count = 0i64;
203
204 // Use a driver view for chunked iteration. Prefer sum_view, else first criteria range.
205 let driver = sum_view
206 .as_ref()
207 .or_else(|| crit_specs.iter().find_map(|(rv, _, _)| rv.as_ref()));
208
209 if let Some(drv) = driver {
210 // We can't easily iterate over union dims if they are larger than driver.
211 // But for most cases they are same.
212 // If driver is smaller, we'll miss some rows.
213 // Actually, if it's SUMIF, we want to iterate over criteria range dims.
214 let driver = if !multi && crit_specs[0].0.is_some() {
215 crit_specs[0].0.as_ref().unwrap()
216 } else {
217 drv
218 };
219
220 for res in driver.iter_row_chunks() {
221 let cs = res?;
222 let row_start = cs.row_start;
223 let row_len = cs.row_len;
224 if row_len == 0 {
225 continue;
226 }
227
228 // Get slices for all criteria and sum range
229 let mut crit_num_slices = Vec::with_capacity(crit_specs.len());
230 let mut crit_text_slices = Vec::with_capacity(crit_specs.len());
231 for (rv, _, _) in &crit_specs {
232 if let Some(v) = rv {
233 crit_num_slices.push(Some(v.slice_numbers(row_start, row_len)));
234 crit_text_slices.push(Some(v.slice_lowered_text(row_start, row_len)));
235 } else {
236 crit_num_slices.push(None);
237 crit_text_slices.push(None);
238 }
239 }
240
241 let sum_slices = sum_view
242 .as_ref()
243 .map(|v| v.slice_numbers(row_start, row_len));
244
245 for c in 0..dims.1 {
246 let mut mask_opt: Option<BooleanArray> = None;
247 let mut impossible = false;
248
249 for (j, (_, pred, scalar_val)) in crit_specs.iter().enumerate() {
250 if crit_specs[j].0.is_none() {
251 if let Some(sv) = scalar_val {
252 if !criteria_match(pred, sv) {
253 impossible = true;
254 break;
255 }
256 continue;
257 }
258 if !criteria_match(pred, &LiteralValue::Empty) {
259 impossible = true;
260 break;
261 }
262 continue;
263 }
264
265 // Try cache
266 let cur_cached = if let Some(ref view) = crit_specs[j].0 {
267 ctx.get_criteria_mask(view, c, pred).map(|m| {
268 let fill = criteria_match(pred, &LiteralValue::Empty);
269 let m_len = m.len();
270
271 // The cached mask may be shorter than the current driver's chunk
272 // (e.g., whole-column references trimmed to different used-regions).
273 // Treat out-of-bounds rows as Empty cells.
274 if row_start + row_len <= m_len {
275 #[cfg(test)]
276 test_hooks::inc_slice_fast();
277 let sl = m.slice(row_start, row_len);
278 return sl
279 .as_any()
280 .downcast_ref::<arrow_array::BooleanArray>()
281 .expect("cached criteria mask slice downcast")
282 .clone();
283 }
284
285 let mut bb =
286 arrow_array::builder::BooleanBuilder::with_capacity(row_len);
287 if row_start < m_len {
288 #[cfg(test)]
289 test_hooks::inc_pad_partial();
290 let take_len = row_len.min(m_len - row_start);
291 let sl = m.slice(row_start, take_len);
292 let ba = sl
293 .as_any()
294 .downcast_ref::<arrow_array::BooleanArray>()
295 .expect("cached criteria mask slice downcast");
296 bb.append_array(ba);
297 bb.append_n(row_len - take_len, fill);
298 } else {
299 #[cfg(test)]
300 test_hooks::inc_pad_all_fill();
301 bb.append_n(row_len, fill);
302 }
303
304 bb.finish()
305 })
306 } else {
307 None
308 };
309
310 if let Some(cm) = cur_cached {
311 mask_opt = Some(match mask_opt {
312 None => cm,
313 Some(prev) => boolean::and_kleene(&prev, &cm).unwrap(),
314 });
315 continue;
316 }
317
318 // Compute mask for this chunk
319 let num_col = crit_num_slices[j]
320 .as_ref()
321 .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
322 let text_col = crit_text_slices[j]
323 .as_ref()
324 .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
325
326 let m = match (pred, num_col, text_col) {
327 (crate::args::CriteriaPredicate::Gt(n), Some(nc), _) => {
328 cmp::gt(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
329 }
330 (crate::args::CriteriaPredicate::Ge(n), Some(nc), _) => {
331 cmp::gt_eq(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
332 }
333 (crate::args::CriteriaPredicate::Lt(n), Some(nc), _) => {
334 cmp::lt(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
335 }
336 (crate::args::CriteriaPredicate::Le(n), Some(nc), _) => {
337 cmp::lt_eq(nc.as_ref(), &Float64Array::new_scalar(*n)).unwrap()
338 }
339 (crate::args::CriteriaPredicate::Eq(v), nc, tc) => {
340 match v {
341 LiteralValue::Number(x) => {
342 let nx = *x;
343 if let Some(nc) = nc {
344 let m0 =
345 cmp::eq(nc.as_ref(), &Float64Array::new_scalar(nx))
346 .unwrap();
347 if m0.null_count() == 0 {
348 m0
349 } else {
350 // Fill nulls using per-cell matching so blanks can still match numeric
351 // criteria (e.g. blank == 0 in Excel criteria semantics).
352 let view = crit_specs[j].0.as_ref().unwrap();
353 let mut bb =
354 arrow_array::builder::BooleanBuilder::with_capacity(
355 row_len,
356 );
357 for i in 0..row_len {
358 if m0.is_valid(i) {
359 bb.append_value(m0.value(i));
360 } else {
361 bb.append_value(criteria_match(
362 pred,
363 &view.get_cell(row_start + i, c),
364 ));
365 }
366 }
367 bb.finish()
368 }
369 } else {
370 // If the criteria range has no numeric fast-path column (e.g. text column
371 // or mixed types), fall back to per-cell matching so numeric criteria can
372 // still match blanks / numeric text values (Excel semantics).
373 let mut bb =
374 arrow_array::builder::BooleanBuilder::with_capacity(
375 row_len,
376 );
377 let view = crit_specs[j].0.as_ref().unwrap();
378 for i in 0..row_len {
379 bb.append_value(criteria_match(
380 pred,
381 &view.get_cell(row_start + i, c),
382 ));
383 }
384 bb.finish()
385 }
386 }
387 LiteralValue::Int(x) => {
388 let nx = *x as f64;
389 if let Some(nc) = nc {
390 let m0 =
391 cmp::eq(nc.as_ref(), &Float64Array::new_scalar(nx))
392 .unwrap();
393 if m0.null_count() == 0 {
394 m0
395 } else {
396 let view = crit_specs[j].0.as_ref().unwrap();
397 let mut bb =
398 arrow_array::builder::BooleanBuilder::with_capacity(
399 row_len,
400 );
401 for i in 0..row_len {
402 if m0.is_valid(i) {
403 bb.append_value(m0.value(i));
404 } else {
405 bb.append_value(criteria_match(
406 pred,
407 &view.get_cell(row_start + i, c),
408 ));
409 }
410 }
411 bb.finish()
412 }
413 } else {
414 let mut bb =
415 arrow_array::builder::BooleanBuilder::with_capacity(
416 row_len,
417 );
418 let view = crit_specs[j].0.as_ref().unwrap();
419 for i in 0..row_len {
420 bb.append_value(criteria_match(
421 pred,
422 &view.get_cell(row_start + i, c),
423 ));
424 }
425 bb.finish()
426 }
427 }
428 _ => {
429 // Use fallback for text and other types to ensure Excel parity (e.g. blank matching)
430 let mut bb =
431 arrow_array::builder::BooleanBuilder::with_capacity(
432 row_len,
433 );
434 let view = crit_specs[j].0.as_ref().unwrap();
435 for i in 0..row_len {
436 bb.append_value(criteria_match(
437 pred,
438 &view.get_cell(row_start + i, c),
439 ));
440 }
441 bb.finish()
442 }
443 }
444 }
445 (crate::args::CriteriaPredicate::Ne(v), nc, tc) => match v {
446 LiteralValue::Number(x) => {
447 let nx = *x;
448 if let Some(nc) = nc {
449 let m0 = cmp::neq(nc.as_ref(), &Float64Array::new_scalar(nx))
450 .unwrap();
451 if m0.null_count() == 0 {
452 m0
453 } else {
454 let view = crit_specs[j].0.as_ref().unwrap();
455 let mut bb =
456 arrow_array::builder::BooleanBuilder::with_capacity(
457 row_len,
458 );
459 for i in 0..row_len {
460 if m0.is_valid(i) {
461 bb.append_value(m0.value(i));
462 } else {
463 bb.append_value(criteria_match(
464 pred,
465 &view.get_cell(row_start + i, c),
466 ));
467 }
468 }
469 bb.finish()
470 }
471 } else {
472 let mut bb =
473 arrow_array::builder::BooleanBuilder::with_capacity(
474 row_len,
475 );
476 let view = crit_specs[j].0.as_ref().unwrap();
477 for i in 0..row_len {
478 bb.append_value(criteria_match(
479 pred,
480 &view.get_cell(row_start + i, c),
481 ));
482 }
483 bb.finish()
484 }
485 }
486 LiteralValue::Int(x) => {
487 let nx = *x as f64;
488 if let Some(nc) = nc {
489 let m0 = cmp::neq(nc.as_ref(), &Float64Array::new_scalar(nx))
490 .unwrap();
491 if m0.null_count() == 0 {
492 m0
493 } else {
494 let view = crit_specs[j].0.as_ref().unwrap();
495 let mut bb =
496 arrow_array::builder::BooleanBuilder::with_capacity(
497 row_len,
498 );
499 for i in 0..row_len {
500 if m0.is_valid(i) {
501 bb.append_value(m0.value(i));
502 } else {
503 bb.append_value(criteria_match(
504 pred,
505 &view.get_cell(row_start + i, c),
506 ));
507 }
508 }
509 bb.finish()
510 }
511 } else {
512 let mut bb =
513 arrow_array::builder::BooleanBuilder::with_capacity(
514 row_len,
515 );
516 let view = crit_specs[j].0.as_ref().unwrap();
517 for i in 0..row_len {
518 bb.append_value(criteria_match(
519 pred,
520 &view.get_cell(row_start + i, c),
521 ));
522 }
523 bb.finish()
524 }
525 }
526 _ => {
527 let mut bb =
528 arrow_array::builder::BooleanBuilder::with_capacity(row_len);
529 let view = crit_specs[j].0.as_ref().unwrap();
530 for i in 0..row_len {
531 bb.append_value(criteria_match(
532 pred,
533 &view.get_cell(row_start + i, c),
534 ));
535 }
536 bb.finish()
537 }
538 },
539 (crate::args::CriteriaPredicate::TextLike { .. }, _, _) => {
540 let mut bb =
541 arrow_array::builder::BooleanBuilder::with_capacity(row_len);
542 let view = crit_specs[j].0.as_ref().unwrap();
543 for i in 0..row_len {
544 bb.append_value(criteria_match(
545 pred,
546 &view.get_cell(row_start + i, c),
547 ));
548 }
549 bb.finish()
550 }
551 _ => {
552 // Fallback for any other case
553 let mut bb =
554 arrow_array::builder::BooleanBuilder::with_capacity(row_len);
555 if let Some(ref view) = crit_specs[j].0 {
556 for i in 0..row_len {
557 bb.append_value(criteria_match(
558 pred,
559 &view.get_cell(row_start + i, c),
560 ));
561 }
562 } else {
563 let val = scalar_val.as_ref().unwrap_or(&LiteralValue::Empty);
564 let matches = criteria_match(pred, val);
565 for _ in 0..row_len {
566 bb.append_value(matches);
567 }
568 }
569 bb.finish()
570 }
571 };
572
573 mask_opt = Some(match mask_opt {
574 None => m,
575 Some(prev) => boolean::and_kleene(&prev, &m).unwrap(),
576 });
577 }
578
579 if impossible {
580 continue;
581 }
582
583 match mask_opt {
584 Some(mask) => {
585 if agg_type == AggregationType::Count {
586 total_count += (0..mask.len())
587 .filter(|&i| mask.is_valid(i) && mask.value(i))
588 .count() as i64;
589 } else {
590 let target_col = sum_slices
591 .as_ref()
592 .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
593 if let Some(tc) = target_col {
594 let filtered = filter_array(tc.as_ref(), &mask).unwrap();
595 let f64_arr =
596 filtered.as_any().downcast_ref::<Float64Array>().unwrap();
597 if let Some(s) = sum_array::<Float64Type, _>(f64_arr) {
598 total_sum += s;
599 }
600 total_count += f64_arr.len() as i64 - f64_arr.null_count() as i64;
601 } else if let Some(ref s) = sum_scalar
602 && let Ok(n) = coerce_num(s)
603 {
604 let count = (0..mask.len())
605 .filter(|&i| mask.is_valid(i) && mask.value(i))
606 .count() as i64;
607 total_sum += n * count as f64;
608 total_count += count;
609 }
610 }
611 }
612 None => {
613 // No masks: everything matches
614 if agg_type == AggregationType::Count {
615 total_count += row_len as i64;
616 } else {
617 let target_col = sum_slices
618 .as_ref()
619 .and_then(|cols| cols.get(c).and_then(|a| a.as_ref()));
620 if let Some(tc) = target_col {
621 if let Some(s) = sum_array::<Float64Type, _>(tc.as_ref()) {
622 total_sum += s;
623 }
624 total_count += tc.len() as i64 - tc.null_count() as i64;
625 } else if let Some(ref s) = sum_scalar
626 && let Ok(n) = coerce_num(s)
627 {
628 total_sum += n * row_len as f64;
629 total_count += row_len as i64;
630 }
631 }
632 }
633 }
634 }
635 }
636 } else {
637 // Scalar driver fallback
638 let mut all_match = true;
639 for (_, pred, scalar_val) in &crit_specs {
640 let val = scalar_val.as_ref().unwrap_or(&LiteralValue::Empty);
641 if !criteria_match(pred, val) {
642 all_match = false;
643 break;
644 }
645 }
646 if all_match {
647 if agg_type == AggregationType::Count {
648 total_count = (dims.0 * dims.1) as i64;
649 } else if let Some(ref s) = sum_scalar
650 && let Ok(n) = coerce_num(s)
651 {
652 total_sum = n * (dims.0 * dims.1) as f64;
653 total_count = (dims.0 * dims.1) as i64;
654 }
655 }
656 }
657
658 match agg_type {
659 AggregationType::Sum => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
660 total_sum,
661 ))),
662 AggregationType::Count => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
663 total_count as f64,
664 ))),
665 AggregationType::Average => {
666 if total_count == 0 {
667 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
668 ExcelError::new_div(),
669 )))
670 } else {
671 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
672 total_sum / total_count as f64,
673 )))
674 }
675 }
676 }
677}
678
679/* ─────────────────────────── AVERAGEIF() ──────────────────────────── */
680#[derive(Debug)]
681pub struct AverageIfFn;
682/// Returns the average of cells that satisfy a single criterion.
683///
684/// `AVERAGEIF` tests each cell in `range`, then averages matching values from `average_range`
685/// (or from `range` when `average_range` is omitted).
686///
687/// # Remarks
688/// - Criteria support comparison operators and wildcard text patterns.
689/// - Non-numeric values in the averaged cells are ignored.
690/// - If no cells match, `AVERAGEIF` returns `#DIV/0!`.
691///
692/// # Examples
693///
694/// ```yaml,sandbox
695/// title: "Average values greater than a threshold"
696/// grid:
697/// A1: 10
698/// A2: 25
699/// A3: 40
700/// formula: "=AVERAGEIF(A1:A3, \">20\")"
701/// expected: 32.5
702/// ```
703///
704/// ```yaml,sandbox
705/// title: "Average one range using criteria from another"
706/// grid:
707/// A1: "East"
708/// A2: "West"
709/// A3: "East"
710/// B1: 10
711/// B2: 40
712/// B3: 20
713/// formula: "=AVERAGEIF(A1:A3, \"East\", B1:B3)"
714/// expected: 15
715/// ```
716///
717/// ```yaml,sandbox
718/// title: "No matches returns divide-by-zero"
719/// formula: "=AVERAGEIF({1,2,3}, \">5\")"
720/// expected: "#DIV/0!"
721/// ```
722///
723/// ```yaml,docs
724/// related:
725/// - AVERAGE
726/// - AVERAGEIFS
727/// - SUMIF
728/// - COUNTIF
729/// faq:
730/// - q: "When does AVERAGEIF return #DIV/0!?"
731/// a: "It returns #DIV/0! when no matching cells contribute numeric values."
732/// - q: "If average_range is omitted, what gets averaged?"
733/// a: "The function averages matching numeric cells from the criteria range itself."
734/// ```
735///
736/// [formualizer-docgen:schema:start]
737/// Name: AVERAGEIF
738/// Type: AverageIfFn
739/// Min args: 2
740/// Max args: variadic
741/// Variadic: true
742/// Signature: AVERAGEIF(arg1...: any@scalar)
743/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
744/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
745/// [formualizer-docgen:schema:end]
746impl Function for AverageIfFn {
747 func_caps!(
748 PURE,
749 REDUCTION,
750 WINDOWED,
751 STREAM_OK,
752 PARALLEL_ARGS,
753 PARALLEL_CHUNKS
754 );
755 fn name(&self) -> &'static str {
756 "AVERAGEIF"
757 }
758 fn min_args(&self) -> usize {
759 2
760 }
761 fn variadic(&self) -> bool {
762 true
763 }
764 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
765 FunctionDependencyContract::criteria_aggregation(
766 arity,
767 FunctionArityRule::OneOf(&[2, 3]),
768 CriteriaValueRange::Optional {
769 provided_index: 2,
770 fallback_criteria_range_index: 0,
771 },
772 0,
773 )
774 }
775 fn arg_schema(&self) -> &'static [ArgSchema] {
776 &ARG_ANY_ONE[..]
777 }
778 fn eval<'a, 'b, 'c>(
779 &self,
780 args: &'c [ArgumentHandle<'a, 'b>],
781 ctx: &dyn FunctionContext<'b>,
782 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
783 eval_if_family(args, ctx, AggregationType::Average, false)
784 }
785}
786
787/* ─────────────────────────── SUMIF() ──────────────────────────── */
788#[derive(Debug)]
789pub struct SumIfFn;
790/// Adds values that satisfy a single criterion.
791///
792/// `SUMIF` evaluates each cell in `range` against `criteria`, then sums corresponding values.
793///
794/// # Remarks
795/// - If `sum_range` is omitted, matching cells from `range` are summed.
796/// - Criteria support operators like `">10"` and wildcard text patterns.
797/// - Cells that do not coerce to numbers in the sum target contribute `0`.
798///
799/// # Examples
800///
801/// ```yaml,sandbox
802/// title: "Sum values above a threshold"
803/// grid:
804/// A1: 5
805/// A2: 15
806/// A3: 25
807/// formula: "=SUMIF(A1:A3, \">10\")"
808/// expected: 40
809/// ```
810///
811/// ```yaml,sandbox
812/// title: "Use separate sum range"
813/// grid:
814/// A1: "East"
815/// A2: "West"
816/// A3: "East"
817/// B1: 10
818/// B2: 40
819/// B3: 20
820/// formula: "=SUMIF(A1:A3, \"East\", B1:B3)"
821/// expected: 30
822/// ```
823///
824/// ```yaml,sandbox
825/// title: "Wildcard criteria"
826/// formula: "=SUMIF({\"apple\",\"pear\",\"apricot\"}, \"ap*\", {2,3,5})"
827/// expected: 7
828/// ```
829///
830/// ```yaml,docs
831/// related:
832/// - SUM
833/// - SUMIFS
834/// - COUNTIF
835/// - AVERAGEIF
836/// faq:
837/// - q: "What happens when matching cells are non-numeric in SUMIF?"
838/// a: "They contribute 0 to the sum target after coercion logic."
839/// - q: "Can SUMIF use wildcard criteria like * and ??"
840/// a: "Yes. Text criteria support wildcard matching semantics."
841/// ```
842///
843/// [formualizer-docgen:schema:start]
844/// Name: SUMIF
845/// Type: SumIfFn
846/// Min args: 2
847/// Max args: variadic
848/// Variadic: true
849/// Signature: SUMIF(arg1...: any@scalar)
850/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
851/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
852/// [formualizer-docgen:schema:end]
853impl Function for SumIfFn {
854 func_caps!(
855 PURE,
856 REDUCTION,
857 WINDOWED,
858 STREAM_OK,
859 PARALLEL_ARGS,
860 PARALLEL_CHUNKS
861 );
862 fn name(&self) -> &'static str {
863 "SUMIF"
864 }
865 fn min_args(&self) -> usize {
866 2
867 }
868 fn variadic(&self) -> bool {
869 true
870 }
871 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
872 FunctionDependencyContract::criteria_aggregation(
873 arity,
874 FunctionArityRule::OneOf(&[2, 3]),
875 CriteriaValueRange::Optional {
876 provided_index: 2,
877 fallback_criteria_range_index: 0,
878 },
879 0,
880 )
881 }
882 fn arg_schema(&self) -> &'static [ArgSchema] {
883 &ARG_ANY_ONE[..]
884 }
885 fn eval<'a, 'b, 'c>(
886 &self,
887 args: &'c [ArgumentHandle<'a, 'b>],
888 ctx: &dyn FunctionContext<'b>,
889 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
890 eval_if_family(args, ctx, AggregationType::Sum, false)
891 }
892}
893
894/* ─────────────────────────── COUNTIF() ──────────────────────────── */
895#[derive(Debug)]
896pub struct CountIfFn;
897/// Counts cells in a range that satisfy a single criterion.
898///
899/// `COUNTIF` evaluates each candidate cell against one criteria expression.
900///
901/// # Remarks
902/// - Criteria support numeric comparisons and wildcard text matching.
903/// - Matching is case-insensitive for text criteria.
904/// - Non-matching or blank cells are not counted.
905///
906/// # Examples
907///
908/// ```yaml,sandbox
909/// title: "Count numbers greater than 10"
910/// grid:
911/// A1: 5
912/// A2: 15
913/// A3: 22
914/// formula: "=COUNTIF(A1:A3, \">10\")"
915/// expected: 2
916/// ```
917///
918/// ```yaml,sandbox
919/// title: "Count text with wildcard"
920/// formula: "=COUNTIF({\"alpha\",\"beta\",\"alphabet\"}, \"al*\")"
921/// expected: 2
922/// ```
923///
924/// ```yaml,sandbox
925/// title: "Exact-match criterion"
926/// formula: "=COUNTIF({1,2,2,3}, \"=2\")"
927/// expected: 2
928/// ```
929///
930/// ```yaml,docs
931/// related:
932/// - COUNTIFS
933/// - COUNTA
934/// - COUNTBLANK
935/// - SUMIF
936/// faq:
937/// - q: "Is COUNTIF text matching case-sensitive?"
938/// a: "No. Text criteria matching is case-insensitive."
939/// - q: "Can COUNTIF evaluate wildcard criteria?"
940/// a: "Yes. Criteria expressions support wildcard patterns for text."
941/// ```
942///
943/// [formualizer-docgen:schema:start]
944/// Name: COUNTIF
945/// Type: CountIfFn
946/// Min args: 2
947/// Max args: 1
948/// Variadic: false
949/// Signature: COUNTIF(arg1: any@scalar)
950/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
951/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
952/// [formualizer-docgen:schema:end]
953impl Function for CountIfFn {
954 func_caps!(
955 PURE,
956 REDUCTION,
957 WINDOWED,
958 STREAM_OK,
959 PARALLEL_ARGS,
960 PARALLEL_CHUNKS
961 );
962 fn name(&self) -> &'static str {
963 "COUNTIF"
964 }
965 fn min_args(&self) -> usize {
966 2
967 }
968 fn variadic(&self) -> bool {
969 false
970 }
971 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
972 FunctionDependencyContract::criteria_aggregation(
973 arity,
974 FunctionArityRule::Exactly(2),
975 CriteriaValueRange::None,
976 0,
977 )
978 }
979 fn arg_schema(&self) -> &'static [ArgSchema] {
980 &ARG_ANY_ONE[..]
981 }
982 fn eval<'a, 'b, 'c>(
983 &self,
984 args: &'c [ArgumentHandle<'a, 'b>],
985 ctx: &dyn FunctionContext<'b>,
986 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
987 eval_if_family(args, ctx, AggregationType::Count, false)
988 }
989}
990
991/* ─────────────────────────── SUMIFS() ──────────────────────────── */
992#[derive(Debug)]
993pub struct SumIfsFn; // SUMIFS(sum_range, criteria_range1, criteria1, ...)
994/// Adds values that satisfy multiple criteria.
995///
996/// `SUMIFS` applies all criteria pairs with logical AND and sums the matching cells.
997///
998/// # Remarks
999/// - The first argument is always the sum target range.
1000/// - Criteria are supplied in `(criteria_range, criteria)` pairs.
1001/// - Criteria ranges are broadcast/padded according to engine matching rules.
1002///
1003/// # Examples
1004///
1005/// ```yaml,sandbox
1006/// title: "Sum with two conditions"
1007/// grid:
1008/// A1: "East"
1009/// A2: "East"
1010/// A3: "West"
1011/// B1: 2024
1012/// B2: 2025
1013/// B3: 2025
1014/// C1: 10
1015/// C2: 20
1016/// C3: 30
1017/// formula: "=SUMIFS(C1:C3, A1:A3, \"East\", B1:B3, \">=2025\")"
1018/// expected: 20
1019/// ```
1020///
1021/// ```yaml,sandbox
1022/// title: "Numeric criteria on single range"
1023/// formula: "=SUMIFS({5,10,20,30}, {1,2,3,4}, \">=2\", {1,2,3,4}, \"<=3\")"
1024/// expected: 30
1025/// ```
1026///
1027/// ```yaml,sandbox
1028/// title: "No matching rows yields zero"
1029/// formula: "=SUMIFS({10,20}, {\"A\",\"B\"}, \"C\")"
1030/// expected: 0
1031/// ```
1032///
1033/// ```yaml,docs
1034/// related:
1035/// - SUMIF
1036/// - COUNTIFS
1037/// - AVERAGEIFS
1038/// - SUMPRODUCT
1039/// faq:
1040/// - q: "How are multiple SUMIFS criteria combined?"
1041/// a: "All criteria pairs are applied with logical AND; every condition must match."
1042/// - q: "What if criteria range sizes differ?"
1043/// a: "Ranges are broadcast/padded under engine rules instead of strict Excel-size rejection."
1044/// ```
1045///
1046/// [formualizer-docgen:schema:start]
1047/// Name: SUMIFS
1048/// Type: SumIfsFn
1049/// Min args: 3
1050/// Max args: variadic
1051/// Variadic: true
1052/// Signature: SUMIFS(arg1...: any@scalar)
1053/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1054/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1055/// [formualizer-docgen:schema:end]
1056impl Function for SumIfsFn {
1057 func_caps!(
1058 PURE,
1059 REDUCTION,
1060 WINDOWED,
1061 STREAM_OK,
1062 PARALLEL_ARGS,
1063 PARALLEL_CHUNKS
1064 );
1065 fn name(&self) -> &'static str {
1066 "SUMIFS"
1067 }
1068 fn min_args(&self) -> usize {
1069 3
1070 }
1071 fn variadic(&self) -> bool {
1072 true
1073 }
1074 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1075 FunctionDependencyContract::criteria_aggregation(
1076 arity,
1077 FunctionArityRule::OddAtLeast(3),
1078 CriteriaValueRange::Fixed(0),
1079 1,
1080 )
1081 }
1082 fn arg_schema(&self) -> &'static [ArgSchema] {
1083 &ARG_ANY_ONE[..]
1084 }
1085 fn eval<'a, 'b, 'c>(
1086 &self,
1087 args: &'c [ArgumentHandle<'a, 'b>],
1088 ctx: &dyn FunctionContext<'b>,
1089 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1090 eval_if_family(args, ctx, AggregationType::Sum, true)
1091 }
1092}
1093
1094/* ─────────────────────────── COUNTIFS() ──────────────────────────── */
1095#[derive(Debug)]
1096pub struct CountIfsFn; // COUNTIFS(criteria_range1, criteria1, ...)
1097/// Counts cells that satisfy all supplied criteria pairs.
1098///
1099/// `COUNTIFS` applies each `(criteria_range, criteria)` pair and counts rows where all tests pass.
1100///
1101/// # Remarks
1102/// - Requires one or more criteria pairs.
1103/// - Criteria support operators and wildcard matching.
1104/// - A row contributes to the result only when every criterion evaluates true.
1105///
1106/// # Examples
1107///
1108/// ```yaml,sandbox
1109/// title: "Count rows matching two filters"
1110/// grid:
1111/// A1: "East"
1112/// A2: "East"
1113/// A3: "West"
1114/// B1: 12
1115/// B2: 8
1116/// B3: 15
1117/// formula: "=COUNTIFS(A1:A3, \"East\", B1:B3, \">=10\")"
1118/// expected: 1
1119/// ```
1120///
1121/// ```yaml,sandbox
1122/// title: "Wildcard text matching"
1123/// formula: "=COUNTIFS({\"apple\",\"pear\",\"apricot\"}, \"ap*\")"
1124/// expected: 2
1125/// ```
1126///
1127/// ```yaml,sandbox
1128/// title: "No rows meeting all criteria"
1129/// formula: "=COUNTIFS({1,2,3}, \">5\", {\"a\",\"b\",\"c\"}, \"a\")"
1130/// expected: 0
1131/// ```
1132///
1133/// ```yaml,docs
1134/// related:
1135/// - COUNTIF
1136/// - SUMIFS
1137/// - AVERAGEIFS
1138/// - FILTER
1139/// faq:
1140/// - q: "Why can COUNTIFS return 0 even when one criterion matches rows?"
1141/// a: "Each row must satisfy every criterion pair; partial matches are excluded."
1142/// - q: "Does COUNTIFS require at least one criteria pair?"
1143/// a: "Yes. It expects arguments in (range, criteria) pairs."
1144/// ```
1145///
1146/// [formualizer-docgen:schema:start]
1147/// Name: COUNTIFS
1148/// Type: CountIfsFn
1149/// Min args: 2
1150/// Max args: variadic
1151/// Variadic: true
1152/// Signature: COUNTIFS(arg1...: any@scalar)
1153/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1154/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1155/// [formualizer-docgen:schema:end]
1156impl Function for CountIfsFn {
1157 func_caps!(
1158 PURE,
1159 REDUCTION,
1160 WINDOWED,
1161 STREAM_OK,
1162 PARALLEL_ARGS,
1163 PARALLEL_CHUNKS
1164 );
1165 fn name(&self) -> &'static str {
1166 "COUNTIFS"
1167 }
1168 fn min_args(&self) -> usize {
1169 2
1170 }
1171 fn variadic(&self) -> bool {
1172 true
1173 }
1174 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1175 FunctionDependencyContract::criteria_aggregation(
1176 arity,
1177 FunctionArityRule::EvenAtLeast(2),
1178 CriteriaValueRange::None,
1179 0,
1180 )
1181 }
1182 fn arg_schema(&self) -> &'static [ArgSchema] {
1183 &ARG_ANY_ONE[..]
1184 }
1185 fn eval<'a, 'b, 'c>(
1186 &self,
1187 args: &'c [ArgumentHandle<'a, 'b>],
1188 ctx: &dyn FunctionContext<'b>,
1189 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1190 eval_if_family(args, ctx, AggregationType::Count, true)
1191 }
1192}
1193
1194/* ─────────────────────────── AVERAGEIFS() (moved) ──────────────────────────── */
1195#[derive(Debug)]
1196pub struct AverageIfsFn;
1197/// Returns the average of cells that satisfy multiple criteria.
1198///
1199/// `AVERAGEIFS` filters by all criteria pairs, then averages matching numeric values.
1200///
1201/// # Remarks
1202/// - The first argument is the average target range.
1203/// - Criteria are supplied in `(criteria_range, criteria)` pairs.
1204/// - If no numeric cells match, the function returns `#DIV/0!`.
1205///
1206/// # Examples
1207///
1208/// ```yaml,sandbox
1209/// title: "Average with two criteria"
1210/// grid:
1211/// A1: "East"
1212/// A2: "East"
1213/// A3: "West"
1214/// B1: 2025
1215/// B2: 2024
1216/// B3: 2025
1217/// C1: 10
1218/// C2: 40
1219/// C3: 30
1220/// formula: "=AVERAGEIFS(C1:C3, A1:A3, \"East\", B1:B3, \">=2025\")"
1221/// expected: 10
1222/// ```
1223///
1224/// ```yaml,sandbox
1225/// title: "Average over inline arrays"
1226/// formula: "=AVERAGEIFS({10,20,30}, {1,2,3}, \">=2\")"
1227/// expected: 25
1228/// ```
1229///
1230/// ```yaml,sandbox
1231/// title: "No matches returns divide-by-zero"
1232/// formula: "=AVERAGEIFS({10,20}, {\"A\",\"B\"}, \"C\")"
1233/// expected: "#DIV/0!"
1234/// ```
1235///
1236/// ```yaml,docs
1237/// related:
1238/// - AVERAGEIF
1239/// - AVERAGE
1240/// - SUMIFS
1241/// - COUNTIFS
1242/// faq:
1243/// - q: "When does AVERAGEIFS return #DIV/0!?"
1244/// a: "It returns #DIV/0! when no matching numeric cells are available to average."
1245/// - q: "Do non-numeric matched cells count in the average?"
1246/// a: "No. Only numeric target cells contribute to sum and count."
1247/// ```
1248///
1249/// [formualizer-docgen:schema:start]
1250/// Name: AVERAGEIFS
1251/// Type: AverageIfsFn
1252/// Min args: 3
1253/// Max args: variadic
1254/// Variadic: true
1255/// Signature: AVERAGEIFS(arg1...: any@scalar)
1256/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1257/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1258/// [formualizer-docgen:schema:end]
1259impl Function for AverageIfsFn {
1260 func_caps!(
1261 PURE,
1262 REDUCTION,
1263 WINDOWED,
1264 STREAM_OK,
1265 PARALLEL_ARGS,
1266 PARALLEL_CHUNKS
1267 );
1268 fn name(&self) -> &'static str {
1269 "AVERAGEIFS"
1270 }
1271 fn min_args(&self) -> usize {
1272 3
1273 }
1274 fn variadic(&self) -> bool {
1275 true
1276 }
1277 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1278 FunctionDependencyContract::criteria_aggregation(
1279 arity,
1280 FunctionArityRule::OddAtLeast(3),
1281 CriteriaValueRange::Fixed(0),
1282 1,
1283 )
1284 }
1285 fn arg_schema(&self) -> &'static [ArgSchema] {
1286 &ARG_ANY_ONE[..]
1287 }
1288 fn eval<'a, 'b, 'c>(
1289 &self,
1290 args: &'c [ArgumentHandle<'a, 'b>],
1291 ctx: &dyn FunctionContext<'b>,
1292 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1293 eval_if_family(args, ctx, AggregationType::Average, true)
1294 }
1295}
1296
1297/* ─────────────────────────── COUNTA() ──────────────────────────── */
1298#[derive(Debug)]
1299pub struct CountAFn; // counts non-empty (including empty text "")
1300/// Counts non-empty cells and scalar arguments.
1301///
1302/// `COUNTA` counts any value except true empty cells.
1303///
1304/// # Remarks
1305/// - Numbers, text, booleans, and errors all count.
1306/// - Empty string values (`""`) are counted as non-empty.
1307/// - Truly empty cells are the only values excluded.
1308///
1309/// # Examples
1310///
1311/// ```yaml,sandbox
1312/// title: "Count mixed populated values"
1313/// formula: "=COUNTA(1, \"x\", TRUE, \"\")"
1314/// expected: 4
1315/// ```
1316///
1317/// ```yaml,sandbox
1318/// title: "Range count excludes only true blanks"
1319/// grid:
1320/// A1: 10
1321/// A2: ""
1322/// formula: "=COUNTA(A1:A3)"
1323/// expected: 2
1324/// ```
1325///
1326/// ```yaml,sandbox
1327/// title: "Errors are counted"
1328/// formula: "=COUNTA(1/0, 5)"
1329/// expected: 2
1330/// ```
1331///
1332/// ```yaml,docs
1333/// related:
1334/// - COUNT
1335/// - COUNTBLANK
1336/// - COUNTIF
1337/// faq:
1338/// - q: "Does COUNTA count empty-string results like \"\"?"
1339/// a: "Yes. Empty text is counted as non-empty by COUNTA."
1340/// - q: "Are error values counted?"
1341/// a: "Yes. Errors are considered populated values and increase the count."
1342/// ```
1343///
1344/// [formualizer-docgen:schema:start]
1345/// Name: COUNTA
1346/// Type: CountAFn
1347/// Min args: 1
1348/// Max args: variadic
1349/// Variadic: true
1350/// Signature: COUNTA(arg1...: any@scalar)
1351/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1352/// Caps: PURE, REDUCTION
1353/// [formualizer-docgen:schema:end]
1354impl Function for CountAFn {
1355 func_caps!(PURE, REDUCTION);
1356 fn name(&self) -> &'static str {
1357 "COUNTA"
1358 }
1359 fn min_args(&self) -> usize {
1360 1
1361 }
1362 fn variadic(&self) -> bool {
1363 true
1364 }
1365 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1366 FunctionDependencyContract::static_reduction(arity, self.min_args())
1367 }
1368 fn arg_schema(&self) -> &'static [ArgSchema] {
1369 &ARG_ANY_ONE[..]
1370 }
1371 fn eval<'a, 'b, 'c>(
1372 &self,
1373 args: &'c [ArgumentHandle<'a, 'b>],
1374 _ctx: &dyn FunctionContext<'b>,
1375 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1376 let mut cnt = 0i64;
1377 for a in args {
1378 if let Ok(view) = a.range_view() {
1379 for res in view.type_tags_slices() {
1380 let (_, _, tag_cols) = res?;
1381 for col in tag_cols {
1382 for i in 0..col.len() {
1383 if col.value(i) != crate::arrow_store::TypeTag::Empty as u8 {
1384 cnt += 1;
1385 }
1386 }
1387 }
1388 }
1389 } else {
1390 let v = a.value()?.into_literal();
1391 if !matches!(v, LiteralValue::Empty) {
1392 cnt += 1;
1393 }
1394 }
1395 }
1396 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
1397 cnt as f64,
1398 )))
1399 }
1400}
1401
1402/* ─────────────────────────── COUNTBLANK() ──────────────────────────── */
1403#[derive(Debug)]
1404pub struct CountBlankFn; // counts truly empty cells and empty text
1405/// Counts blank cells, including empty-string text results.
1406///
1407/// `COUNTBLANK` treats both true empty cells and `""` text values as blank.
1408///
1409/// # Remarks
1410/// - Empty-string text values are counted.
1411/// - Numbers, booleans, and non-empty text are not counted.
1412/// - Supports scalar arguments and ranges.
1413///
1414/// # Examples
1415///
1416/// ```yaml,sandbox
1417/// title: "Count blanks in a range"
1418/// grid:
1419/// A1: 10
1420/// A2: ""
1421/// formula: "=COUNTBLANK(A1:A3)"
1422/// expected: 2
1423/// ```
1424///
1425/// ```yaml,sandbox
1426/// title: "Scalar empty-string counts as blank"
1427/// formula: "=COUNTBLANK(\"\", 5)"
1428/// expected: 1
1429/// ```
1430///
1431/// ```yaml,sandbox
1432/// title: "Non-empty values are excluded"
1433/// formula: "=COUNTBLANK(1, \"x\", TRUE)"
1434/// expected: 0
1435/// ```
1436///
1437/// ```yaml,docs
1438/// related:
1439/// - COUNTA
1440/// - COUNT
1441/// - COUNTIF
1442/// faq:
1443/// - q: "Does COUNTBLANK include cells that contain \"\"?"
1444/// a: "Yes. Empty-string text values are treated as blank for COUNTBLANK."
1445/// - q: "Are numeric zeros considered blank?"
1446/// a: "No. Zero is a numeric value, so it is not counted as blank."
1447/// ```
1448///
1449/// [formualizer-docgen:schema:start]
1450/// Name: COUNTBLANK
1451/// Type: CountBlankFn
1452/// Min args: 1
1453/// Max args: variadic
1454/// Variadic: true
1455/// Signature: COUNTBLANK(arg1...: any@scalar)
1456/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1457/// Caps: PURE, REDUCTION
1458/// [formualizer-docgen:schema:end]
1459impl Function for CountBlankFn {
1460 func_caps!(PURE, REDUCTION);
1461 fn name(&self) -> &'static str {
1462 "COUNTBLANK"
1463 }
1464 fn min_args(&self) -> usize {
1465 1
1466 }
1467 fn variadic(&self) -> bool {
1468 true
1469 }
1470 fn arg_schema(&self) -> &'static [ArgSchema] {
1471 &ARG_ANY_ONE[..]
1472 }
1473 fn eval<'a, 'b, 'c>(
1474 &self,
1475 args: &'c [ArgumentHandle<'a, 'b>],
1476 _ctx: &dyn FunctionContext<'b>,
1477 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1478 let mut cnt = 0i64;
1479 for a in args {
1480 if let Ok(view) = a.range_view() {
1481 let mut tag_it = view.type_tags_slices();
1482 let mut text_it = view.text_slices();
1483
1484 while let (Some(tag_res), Some(text_res)) = (tag_it.next(), text_it.next()) {
1485 let (_, _, tag_cols) = tag_res?;
1486 let (_, _, text_cols) = text_res?;
1487
1488 for (tc, xc) in tag_cols.into_iter().zip(text_cols.into_iter()) {
1489 let text_arr = xc
1490 .as_any()
1491 .downcast_ref::<arrow_array::StringArray>()
1492 .unwrap();
1493 for i in 0..tc.len() {
1494 let is_blank = tc.value(i) == crate::arrow_store::TypeTag::Empty as u8
1495 || (tc.value(i) == crate::arrow_store::TypeTag::Text as u8
1496 && !text_arr.is_null(i)
1497 && text_arr.value(i).is_empty());
1498 if is_blank {
1499 cnt += 1;
1500 }
1501 }
1502 }
1503 }
1504 } else {
1505 let v = a.value()?.into_literal();
1506 match v {
1507 LiteralValue::Empty => cnt += 1,
1508 LiteralValue::Text(s) if s.is_empty() => cnt += 1,
1509 _ => {}
1510 }
1511 }
1512 }
1513 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
1514 cnt as f64,
1515 )))
1516 }
1517}
1518
1519pub fn register_builtins() {
1520 use std::sync::Arc;
1521 crate::function_registry::register_function(Arc::new(SumIfFn));
1522 crate::function_registry::register_function(Arc::new(CountIfFn));
1523 crate::function_registry::register_function(Arc::new(AverageIfFn));
1524 crate::function_registry::register_function(Arc::new(SumIfsFn));
1525 crate::function_registry::register_function(Arc::new(CountIfsFn));
1526 crate::function_registry::register_function(Arc::new(AverageIfsFn));
1527 crate::function_registry::register_function(Arc::new(CountAFn));
1528 crate::function_registry::register_function(Arc::new(CountBlankFn));
1529}
1530
1531#[cfg(test)]
1532mod tests {
1533 use super::*;
1534 use crate::test_workbook::TestWorkbook;
1535 use crate::traits::ArgumentHandle;
1536 use formualizer_common::LiteralValue;
1537 use formualizer_parse::parser::{ASTNode, ASTNodeType};
1538 fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1539 wb.interpreter()
1540 }
1541 fn lit(v: LiteralValue) -> ASTNode {
1542 ASTNode::new(ASTNodeType::Literal(v), None)
1543 }
1544
1545 #[test]
1546 fn sumif_basic() {
1547 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1548 let ctx = interp(&wb);
1549 let range = lit(LiteralValue::Array(vec![vec![
1550 LiteralValue::Int(1),
1551 LiteralValue::Int(2),
1552 LiteralValue::Int(3),
1553 ]]));
1554 let crit = lit(LiteralValue::Text(">1".into()));
1555 let args = vec![
1556 ArgumentHandle::new(&range, &ctx),
1557 ArgumentHandle::new(&crit, &ctx),
1558 ];
1559 let f = ctx.context.get_function("", "SUMIF").unwrap();
1560 assert_eq!(
1561 f.dispatch(&args, &ctx.function_context(None))
1562 .unwrap()
1563 .into_literal(),
1564 LiteralValue::Number(5.0)
1565 );
1566 }
1567
1568 #[test]
1569 fn sumif_with_sum_range() {
1570 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1571 let ctx = interp(&wb);
1572 let range = lit(LiteralValue::Array(vec![vec![
1573 LiteralValue::Int(1),
1574 LiteralValue::Int(0),
1575 LiteralValue::Int(1),
1576 ]]));
1577 let sum_range = lit(LiteralValue::Array(vec![vec![
1578 LiteralValue::Int(10),
1579 LiteralValue::Int(20),
1580 LiteralValue::Int(30),
1581 ]]));
1582 let crit = lit(LiteralValue::Text("=1".into()));
1583 let args = vec![
1584 ArgumentHandle::new(&range, &ctx),
1585 ArgumentHandle::new(&crit, &ctx),
1586 ArgumentHandle::new(&sum_range, &ctx),
1587 ];
1588 let f = ctx.context.get_function("", "SUMIF").unwrap();
1589 assert_eq!(
1590 f.dispatch(&args, &ctx.function_context(None))
1591 .unwrap()
1592 .into_literal(),
1593 LiteralValue::Number(40.0)
1594 );
1595 }
1596
1597 #[test]
1598 fn sumif_numeric_zero_matches_blank_in_text_column() {
1599 // Regression test: if the criteria range is text-typed (no numeric fast-path column),
1600 // numeric criteria should still match blanks (Excel semantics: blank coerces to 0).
1601 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1602 let ctx = interp(&wb);
1603
1604 // Criteria range is a 1x2 row with (blank, "x") so the column is non-numeric.
1605 let range = lit(LiteralValue::Array(vec![vec![
1606 LiteralValue::Empty,
1607 LiteralValue::Text("x".into()),
1608 ]]));
1609 let sum_range = lit(LiteralValue::Array(vec![vec![
1610 LiteralValue::Int(5),
1611 LiteralValue::Int(7),
1612 ]]));
1613 let crit = lit(LiteralValue::Int(0));
1614
1615 let args = vec![
1616 ArgumentHandle::new(&range, &ctx),
1617 ArgumentHandle::new(&crit, &ctx),
1618 ArgumentHandle::new(&sum_range, &ctx),
1619 ];
1620 let f = ctx.context.get_function("", "SUMIF").unwrap();
1621 assert_eq!(
1622 f.dispatch(&args, &ctx.function_context(None))
1623 .unwrap()
1624 .into_literal(),
1625 LiteralValue::Number(5.0)
1626 );
1627 }
1628
1629 #[test]
1630 fn sumif_mismatched_ranges_now_pad_with_empty() {
1631 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1632 let ctx = interp(&wb);
1633 // sum_range: 2x2
1634 let sum = lit(LiteralValue::Array(vec![
1635 vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1636 vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1637 ]));
1638 // criteria range: 3x2 (extra row should be ignored due to iterating sum_range dims)
1639 let crit_range = lit(LiteralValue::Array(vec![
1640 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1641 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1642 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1643 ]));
1644 let crit = lit(LiteralValue::Text("=1".into()));
1645 let args = vec![
1646 ArgumentHandle::new(&crit_range, &ctx),
1647 ArgumentHandle::new(&crit, &ctx),
1648 ArgumentHandle::new(&sum, &ctx),
1649 ];
1650 let f = ctx.context.get_function("", "SUMIF").unwrap();
1651 assert_eq!(
1652 f.dispatch(&args, &ctx.function_context(None))
1653 .unwrap()
1654 .into_literal(),
1655 LiteralValue::Number(10.0)
1656 );
1657 }
1658
1659 #[test]
1660 fn countif_text_wildcard() {
1661 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1662 let ctx = interp(&wb);
1663 let rng = lit(LiteralValue::Array(vec![vec![
1664 LiteralValue::Text("alpha".into()),
1665 LiteralValue::Text("beta".into()),
1666 LiteralValue::Text("alphabet".into()),
1667 ]]));
1668 let crit = lit(LiteralValue::Text("al*".into()));
1669 let args = vec![
1670 ArgumentHandle::new(&rng, &ctx),
1671 ArgumentHandle::new(&crit, &ctx),
1672 ];
1673 let f = ctx.context.get_function("", "COUNTIF").unwrap();
1674 assert_eq!(
1675 f.dispatch(&args, &ctx.function_context(None))
1676 .unwrap()
1677 .into_literal(),
1678 LiteralValue::Number(2.0)
1679 );
1680 }
1681
1682 #[test]
1683 fn sumifs_multiple_criteria() {
1684 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1685 let ctx = interp(&wb);
1686 let sum = lit(LiteralValue::Array(vec![vec![
1687 LiteralValue::Int(10),
1688 LiteralValue::Int(20),
1689 LiteralValue::Int(30),
1690 LiteralValue::Int(40),
1691 ]]));
1692 let city = lit(LiteralValue::Array(vec![vec![
1693 LiteralValue::Text("Bellevue".into()),
1694 LiteralValue::Text("Issaquah".into()),
1695 LiteralValue::Text("Bellevue".into()),
1696 LiteralValue::Text("Issaquah".into()),
1697 ]]));
1698 let beds = lit(LiteralValue::Array(vec![vec![
1699 LiteralValue::Int(2),
1700 LiteralValue::Int(3),
1701 LiteralValue::Int(4),
1702 LiteralValue::Int(5),
1703 ]]));
1704 let c_city = lit(LiteralValue::Text("Bellevue".into()));
1705 let c_beds = lit(LiteralValue::Text(">=4".into()));
1706 let args = vec![
1707 ArgumentHandle::new(&sum, &ctx),
1708 ArgumentHandle::new(&city, &ctx),
1709 ArgumentHandle::new(&c_city, &ctx),
1710 ArgumentHandle::new(&beds, &ctx),
1711 ArgumentHandle::new(&c_beds, &ctx),
1712 ];
1713 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1714 assert_eq!(
1715 f.dispatch(&args, &ctx.function_context(None))
1716 .unwrap()
1717 .into_literal(),
1718 LiteralValue::Number(30.0)
1719 );
1720 }
1721
1722 #[test]
1723 fn countifs_basic() {
1724 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1725 let ctx = interp(&wb);
1726 let city = lit(LiteralValue::Array(vec![vec![
1727 LiteralValue::Text("a".into()),
1728 LiteralValue::Text("b".into()),
1729 LiteralValue::Text("a".into()),
1730 ]]));
1731 let beds = lit(LiteralValue::Array(vec![vec![
1732 LiteralValue::Int(1),
1733 LiteralValue::Int(2),
1734 LiteralValue::Int(3),
1735 ]]));
1736 let c_city = lit(LiteralValue::Text("a".into()));
1737 let c_beds = lit(LiteralValue::Text(">1".into()));
1738 let args = vec![
1739 ArgumentHandle::new(&city, &ctx),
1740 ArgumentHandle::new(&c_city, &ctx),
1741 ArgumentHandle::new(&beds, &ctx),
1742 ArgumentHandle::new(&c_beds, &ctx),
1743 ];
1744 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1745 assert_eq!(
1746 f.dispatch(&args, &ctx.function_context(None))
1747 .unwrap()
1748 .into_literal(),
1749 LiteralValue::Number(1.0)
1750 );
1751 }
1752
1753 #[test]
1754 fn averageifs_div0() {
1755 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1756 let ctx = interp(&wb);
1757 let avg = lit(LiteralValue::Array(vec![vec![
1758 LiteralValue::Int(1),
1759 LiteralValue::Int(2),
1760 ]]));
1761 let crit_rng = lit(LiteralValue::Array(vec![vec![
1762 LiteralValue::Int(0),
1763 LiteralValue::Int(0),
1764 ]]));
1765 let crit = lit(LiteralValue::Text(">0".into()));
1766 let args = vec![
1767 ArgumentHandle::new(&avg, &ctx),
1768 ArgumentHandle::new(&crit_rng, &ctx),
1769 ArgumentHandle::new(&crit, &ctx),
1770 ];
1771 let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1772 match f
1773 .dispatch(&args, &ctx.function_context(None))
1774 .unwrap()
1775 .into_literal()
1776 {
1777 LiteralValue::Error(e) => assert_eq!(e, "#DIV/0!"),
1778 _ => panic!("expected div0"),
1779 }
1780 }
1781
1782 #[test]
1783 fn counta_and_countblank() {
1784 let wb = TestWorkbook::new()
1785 .with_function(std::sync::Arc::new(CountAFn))
1786 .with_function(std::sync::Arc::new(CountBlankFn));
1787 let ctx = interp(&wb);
1788 let arr = lit(LiteralValue::Array(vec![vec![
1789 LiteralValue::Empty,
1790 LiteralValue::Text("".into()),
1791 LiteralValue::Int(5),
1792 ]]));
1793 let args = vec![ArgumentHandle::new(&arr, &ctx)];
1794 let counta = ctx.context.get_function("", "COUNTA").unwrap();
1795 let countblank = ctx.context.get_function("", "COUNTBLANK").unwrap();
1796 assert_eq!(
1797 counta
1798 .dispatch(&args, &ctx.function_context(None))
1799 .unwrap()
1800 .into_literal(),
1801 LiteralValue::Number(2.0)
1802 );
1803 assert_eq!(
1804 countblank
1805 .dispatch(&args, &ctx.function_context(None))
1806 .unwrap()
1807 .into_literal(),
1808 LiteralValue::Number(2.0)
1809 );
1810 }
1811
1812 // ───────── Parity tests (window vs scalar) ─────────
1813 #[test]
1814 fn sumifs_broadcasts_1x1_criteria_over_range() {
1815 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1816 let ctx = interp(&wb);
1817 // sum_range: column vector [10, 20]
1818 let sum = lit(LiteralValue::Array(vec![
1819 vec![LiteralValue::Int(10)],
1820 vec![LiteralValue::Int(20)],
1821 ]));
1822 // criteria_range: column vector ["A", "B"]
1823 let tags = lit(LiteralValue::Array(vec![
1824 vec![LiteralValue::Text("A".into())],
1825 vec![LiteralValue::Text("B".into())],
1826 ]));
1827 // criteria: 1x1 array acting as scalar "A"
1828 let c_tag = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1829 "A".into(),
1830 )]]));
1831 let args = vec![
1832 ArgumentHandle::new(&sum, &ctx),
1833 ArgumentHandle::new(&tags, &ctx),
1834 ArgumentHandle::new(&c_tag, &ctx),
1835 ];
1836 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1837 assert_eq!(
1838 f.dispatch(&args, &ctx.function_context(None))
1839 .unwrap()
1840 .into_literal(),
1841 LiteralValue::Number(10.0)
1842 );
1843 }
1844
1845 #[test]
1846 fn countifs_broadcasts_1x1_criteria_over_row() {
1847 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1848 let ctx = interp(&wb);
1849 // criteria_range: row [1,2,3,4]
1850 let nums = lit(LiteralValue::Array(vec![vec![
1851 LiteralValue::Int(1),
1852 LiteralValue::Int(2),
1853 LiteralValue::Int(3),
1854 LiteralValue::Int(4),
1855 ]]));
1856 // criteria: 1x1 array ">=3"
1857 let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1858 ">=3".into(),
1859 )]]));
1860 let args = vec![
1861 ArgumentHandle::new(&nums, &ctx),
1862 ArgumentHandle::new(&crit, &ctx),
1863 ];
1864 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1865 assert_eq!(
1866 f.dispatch(&args, &ctx.function_context(None))
1867 .unwrap()
1868 .into_literal(),
1869 LiteralValue::Number(2.0)
1870 );
1871 }
1872
1873 #[test]
1874 fn sumifs_empty_ranges_with_1x1_criteria_produce_zero() {
1875 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1876 let ctx = interp(&wb);
1877 // Empty ranges (0x0) simulate unused whole-column resolved empty
1878 let empty = lit(LiteralValue::Array(Vec::new()));
1879 // 1x1 criteria (array)
1880 let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1881 "X".into(),
1882 )]]));
1883 let args = vec![
1884 ArgumentHandle::new(&empty, &ctx),
1885 ArgumentHandle::new(&empty, &ctx),
1886 ArgumentHandle::new(&crit, &ctx),
1887 ];
1888 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1889 assert_eq!(
1890 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1891 LiteralValue::Number(0.0)
1892 );
1893 }
1894
1895 #[test]
1896 fn sumifs_mismatched_ranges_now_pad_with_empty() {
1897 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1898 let ctx = interp(&wb);
1899 // sum_range: 2x2
1900 let sum = lit(LiteralValue::Array(vec![
1901 vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1902 vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1903 ]));
1904 // criteria_range: 3x2 (different rows - extra row will match against padded empty values)
1905 let crit_range = lit(LiteralValue::Array(vec![
1906 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1907 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1908 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1909 ]));
1910 // scalar criterion
1911 let crit = lit(LiteralValue::Text("=1".into()));
1912 let args = vec![
1913 ArgumentHandle::new(&sum, &ctx),
1914 ArgumentHandle::new(&crit_range, &ctx),
1915 ArgumentHandle::new(&crit, &ctx),
1916 ];
1917 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1918 // With padding, sum_range gets padded with empties for row 3
1919 // Rows 1-2 match criteria (all 1s), row 3 has empties which don't match =1
1920 // So we sum: 1 + 2 + 3 + 4 = 10
1921 assert_eq!(
1922 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1923 LiteralValue::Number(10.0)
1924 );
1925 }
1926
1927 #[test]
1928 fn countifs_mismatched_ranges_pad_and_broadcast() {
1929 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1930 let ctx = interp(&wb);
1931 // criteria_range1: 2x1 -> [1,1]
1932 let r1 = lit(LiteralValue::Array(vec![
1933 vec![LiteralValue::Int(1)],
1934 vec![LiteralValue::Int(1)],
1935 ]));
1936 // criteria1: "=1"
1937 let c1 = lit(LiteralValue::Text("=1".into()));
1938 // criteria_range2: 3x1 -> [1,1,1]
1939 let r2 = lit(LiteralValue::Array(vec![
1940 vec![LiteralValue::Int(1)],
1941 vec![LiteralValue::Int(1)],
1942 vec![LiteralValue::Int(1)],
1943 ]));
1944 // criteria2: "=1"
1945 let c2 = lit(LiteralValue::Text("=1".into()));
1946 let args = vec![
1947 ArgumentHandle::new(&r1, &ctx),
1948 ArgumentHandle::new(&c1, &ctx),
1949 ArgumentHandle::new(&r2, &ctx),
1950 ArgumentHandle::new(&c2, &ctx),
1951 ];
1952 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1953 // Union rows = 3; row3 has r1=Empty (padded), which doesn't match =1; expect 2
1954 assert_eq!(
1955 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1956 LiteralValue::Number(2.0)
1957 );
1958 }
1959
1960 #[test]
1961 fn averageifs_mismatched_ranges_pad() {
1962 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1963 let ctx = interp(&wb);
1964 // avg_range: 2x1 -> [10,20]
1965 let avg = lit(LiteralValue::Array(vec![
1966 vec![LiteralValue::Int(10)],
1967 vec![LiteralValue::Int(20)],
1968 ]));
1969 // criteria_range: 3x1 -> [1,1,2]
1970 let r1 = lit(LiteralValue::Array(vec![
1971 vec![LiteralValue::Int(1)],
1972 vec![LiteralValue::Int(1)],
1973 vec![LiteralValue::Int(2)],
1974 ]));
1975 let c1 = lit(LiteralValue::Text("=1".into()));
1976 let args = vec![
1977 ArgumentHandle::new(&avg, &ctx),
1978 ArgumentHandle::new(&r1, &ctx),
1979 ArgumentHandle::new(&c1, &ctx),
1980 ];
1981 let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1982 // Only first two rows match; expect (10+20)/2 = 15
1983 assert_eq!(
1984 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1985 LiteralValue::Number(15.0)
1986 );
1987 }
1988
1989 #[test]
1990 fn criteria_scientific_notation() {
1991 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1992 let ctx = interp(&wb);
1993 let nums = lit(LiteralValue::Array(vec![vec![
1994 LiteralValue::Number(1000.0),
1995 LiteralValue::Number(1500.0),
1996 LiteralValue::Number(999.0),
1997 ]]));
1998 let crit = lit(LiteralValue::Text(">1e3".into())); // should parse as >1000
1999 let args = vec![
2000 ArgumentHandle::new(&nums, &ctx),
2001 ArgumentHandle::new(&crit, &ctx),
2002 ];
2003 let f = ctx.context.get_function("", "SUMIF").unwrap();
2004 // >1000 matches 1500 only (strict greater)
2005 assert_eq!(
2006 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
2007 LiteralValue::Number(1500.0)
2008 );
2009 }
2010}