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(
660 super::super::utils::aggregate_result(total_sum),
661 )),
662 // Counts cannot overflow to non-finite; keep them branch-free.
663 AggregationType::Count => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
664 total_count as f64,
665 ))),
666 AggregationType::Average => {
667 if total_count == 0 {
668 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
669 ExcelError::new_div(),
670 )))
671 } else {
672 Ok(crate::traits::CalcValue::Scalar(
673 super::super::utils::aggregate_result(total_sum / total_count as f64),
674 ))
675 }
676 }
677 }
678}
679
680/* ─────────────────────────── AVERAGEIF() ──────────────────────────── */
681#[derive(Debug)]
682pub struct AverageIfFn;
683/// Returns the average of cells that satisfy a single criterion.
684///
685/// `AVERAGEIF` tests each cell in `range`, then averages matching values from `average_range`
686/// (or from `range` when `average_range` is omitted).
687///
688/// # Remarks
689/// - Criteria support comparison operators and wildcard text patterns.
690/// - Non-numeric values in the averaged cells are ignored.
691/// - If no cells match, `AVERAGEIF` returns `#DIV/0!`.
692///
693/// # Examples
694///
695/// ```yaml,sandbox
696/// title: "Average values greater than a threshold"
697/// grid:
698/// A1: 10
699/// A2: 25
700/// A3: 40
701/// formula: "=AVERAGEIF(A1:A3, \">20\")"
702/// expected: 32.5
703/// ```
704///
705/// ```yaml,sandbox
706/// title: "Average one range using criteria from another"
707/// grid:
708/// A1: "East"
709/// A2: "West"
710/// A3: "East"
711/// B1: 10
712/// B2: 40
713/// B3: 20
714/// formula: "=AVERAGEIF(A1:A3, \"East\", B1:B3)"
715/// expected: 15
716/// ```
717///
718/// ```yaml,sandbox
719/// title: "No matches returns divide-by-zero"
720/// formula: "=AVERAGEIF({1,2,3}, \">5\")"
721/// expected: "#DIV/0!"
722/// ```
723///
724/// ```yaml,docs
725/// related:
726/// - AVERAGE
727/// - AVERAGEIFS
728/// - SUMIF
729/// - COUNTIF
730/// faq:
731/// - q: "When does AVERAGEIF return #DIV/0!?"
732/// a: "It returns #DIV/0! when no matching cells contribute numeric values."
733/// - q: "If average_range is omitted, what gets averaged?"
734/// a: "The function averages matching numeric cells from the criteria range itself."
735/// ```
736///
737/// [formualizer-docgen:schema:start]
738/// Name: AVERAGEIF
739/// Type: AverageIfFn
740/// Min args: 2
741/// Max args: variadic
742/// Variadic: true
743/// Signature: AVERAGEIF(arg1...: any@scalar)
744/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
745/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
746/// [formualizer-docgen:schema:end]
747impl Function for AverageIfFn {
748 func_caps!(
749 PURE,
750 REDUCTION,
751 WINDOWED,
752 STREAM_OK,
753 PARALLEL_ARGS,
754 PARALLEL_CHUNKS
755 );
756 fn name(&self) -> &'static str {
757 "AVERAGEIF"
758 }
759 fn min_args(&self) -> usize {
760 2
761 }
762 fn variadic(&self) -> bool {
763 true
764 }
765 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
766 FunctionDependencyContract::criteria_aggregation(
767 arity,
768 FunctionArityRule::OneOf(&[2, 3]),
769 CriteriaValueRange::Optional {
770 provided_index: 2,
771 fallback_criteria_range_index: 0,
772 },
773 0,
774 )
775 }
776 fn arg_schema(&self) -> &'static [ArgSchema] {
777 &ARG_ANY_ONE[..]
778 }
779 fn eval<'a, 'b, 'c>(
780 &self,
781 args: &'c [ArgumentHandle<'a, 'b>],
782 ctx: &dyn FunctionContext<'b>,
783 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
784 eval_if_family(args, ctx, AggregationType::Average, false)
785 }
786}
787
788/* ─────────────────────────── SUMIF() ──────────────────────────── */
789#[derive(Debug)]
790pub struct SumIfFn;
791/// Adds values that satisfy a single criterion.
792///
793/// `SUMIF` evaluates each cell in `range` against `criteria`, then sums corresponding values.
794///
795/// # Remarks
796/// - If `sum_range` is omitted, matching cells from `range` are summed.
797/// - Criteria support operators like `">10"` and wildcard text patterns.
798/// - Cells that do not coerce to numbers in the sum target contribute `0`.
799///
800/// # Examples
801///
802/// ```yaml,sandbox
803/// title: "Sum values above a threshold"
804/// grid:
805/// A1: 5
806/// A2: 15
807/// A3: 25
808/// formula: "=SUMIF(A1:A3, \">10\")"
809/// expected: 40
810/// ```
811///
812/// ```yaml,sandbox
813/// title: "Use separate sum range"
814/// grid:
815/// A1: "East"
816/// A2: "West"
817/// A3: "East"
818/// B1: 10
819/// B2: 40
820/// B3: 20
821/// formula: "=SUMIF(A1:A3, \"East\", B1:B3)"
822/// expected: 30
823/// ```
824///
825/// ```yaml,sandbox
826/// title: "Wildcard criteria"
827/// formula: "=SUMIF({\"apple\",\"pear\",\"apricot\"}, \"ap*\", {2,3,5})"
828/// expected: 7
829/// ```
830///
831/// ```yaml,docs
832/// related:
833/// - SUM
834/// - SUMIFS
835/// - COUNTIF
836/// - AVERAGEIF
837/// faq:
838/// - q: "What happens when matching cells are non-numeric in SUMIF?"
839/// a: "They contribute 0 to the sum target after coercion logic."
840/// - q: "Can SUMIF use wildcard criteria like * and ??"
841/// a: "Yes. Text criteria support wildcard matching semantics."
842/// ```
843///
844/// [formualizer-docgen:schema:start]
845/// Name: SUMIF
846/// Type: SumIfFn
847/// Min args: 2
848/// Max args: variadic
849/// Variadic: true
850/// Signature: SUMIF(arg1...: any@scalar)
851/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
852/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
853/// [formualizer-docgen:schema:end]
854impl Function for SumIfFn {
855 func_caps!(
856 PURE,
857 REDUCTION,
858 WINDOWED,
859 STREAM_OK,
860 PARALLEL_ARGS,
861 PARALLEL_CHUNKS
862 );
863 fn name(&self) -> &'static str {
864 "SUMIF"
865 }
866 fn min_args(&self) -> usize {
867 2
868 }
869 fn variadic(&self) -> bool {
870 true
871 }
872 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
873 FunctionDependencyContract::criteria_aggregation(
874 arity,
875 FunctionArityRule::OneOf(&[2, 3]),
876 CriteriaValueRange::Optional {
877 provided_index: 2,
878 fallback_criteria_range_index: 0,
879 },
880 0,
881 )
882 }
883 fn arg_schema(&self) -> &'static [ArgSchema] {
884 &ARG_ANY_ONE[..]
885 }
886 fn eval<'a, 'b, 'c>(
887 &self,
888 args: &'c [ArgumentHandle<'a, 'b>],
889 ctx: &dyn FunctionContext<'b>,
890 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
891 eval_if_family(args, ctx, AggregationType::Sum, false)
892 }
893}
894
895/* ─────────────────────────── COUNTIF() ──────────────────────────── */
896#[derive(Debug)]
897pub struct CountIfFn;
898/// Counts cells in a range that satisfy a single criterion.
899///
900/// `COUNTIF` evaluates each candidate cell against one criteria expression.
901///
902/// # Remarks
903/// - Criteria support numeric comparisons and wildcard text matching.
904/// - Matching is case-insensitive for text criteria.
905/// - Non-matching or blank cells are not counted.
906///
907/// # Examples
908///
909/// ```yaml,sandbox
910/// title: "Count numbers greater than 10"
911/// grid:
912/// A1: 5
913/// A2: 15
914/// A3: 22
915/// formula: "=COUNTIF(A1:A3, \">10\")"
916/// expected: 2
917/// ```
918///
919/// ```yaml,sandbox
920/// title: "Count text with wildcard"
921/// formula: "=COUNTIF({\"alpha\",\"beta\",\"alphabet\"}, \"al*\")"
922/// expected: 2
923/// ```
924///
925/// ```yaml,sandbox
926/// title: "Exact-match criterion"
927/// formula: "=COUNTIF({1,2,2,3}, \"=2\")"
928/// expected: 2
929/// ```
930///
931/// ```yaml,docs
932/// related:
933/// - COUNTIFS
934/// - COUNTA
935/// - COUNTBLANK
936/// - SUMIF
937/// faq:
938/// - q: "Is COUNTIF text matching case-sensitive?"
939/// a: "No. Text criteria matching is case-insensitive."
940/// - q: "Can COUNTIF evaluate wildcard criteria?"
941/// a: "Yes. Criteria expressions support wildcard patterns for text."
942/// ```
943///
944/// [formualizer-docgen:schema:start]
945/// Name: COUNTIF
946/// Type: CountIfFn
947/// Min args: 2
948/// Max args: 1
949/// Variadic: false
950/// Signature: COUNTIF(arg1: any@scalar)
951/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
952/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
953/// [formualizer-docgen:schema:end]
954impl Function for CountIfFn {
955 func_caps!(
956 PURE,
957 REDUCTION,
958 WINDOWED,
959 STREAM_OK,
960 PARALLEL_ARGS,
961 PARALLEL_CHUNKS
962 );
963 fn name(&self) -> &'static str {
964 "COUNTIF"
965 }
966 fn min_args(&self) -> usize {
967 2
968 }
969 fn variadic(&self) -> bool {
970 false
971 }
972 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
973 FunctionDependencyContract::criteria_aggregation(
974 arity,
975 FunctionArityRule::Exactly(2),
976 CriteriaValueRange::None,
977 0,
978 )
979 }
980 fn arg_schema(&self) -> &'static [ArgSchema] {
981 &ARG_ANY_ONE[..]
982 }
983 fn eval<'a, 'b, 'c>(
984 &self,
985 args: &'c [ArgumentHandle<'a, 'b>],
986 ctx: &dyn FunctionContext<'b>,
987 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
988 eval_if_family(args, ctx, AggregationType::Count, false)
989 }
990}
991
992/* ─────────────────────────── SUMIFS() ──────────────────────────── */
993#[derive(Debug)]
994pub struct SumIfsFn; // SUMIFS(sum_range, criteria_range1, criteria1, ...)
995/// Adds values that satisfy multiple criteria.
996///
997/// `SUMIFS` applies all criteria pairs with logical AND and sums the matching cells.
998///
999/// # Remarks
1000/// - The first argument is always the sum target range.
1001/// - Criteria are supplied in `(criteria_range, criteria)` pairs.
1002/// - Criteria ranges are broadcast/padded according to engine matching rules.
1003///
1004/// # Examples
1005///
1006/// ```yaml,sandbox
1007/// title: "Sum with two conditions"
1008/// grid:
1009/// A1: "East"
1010/// A2: "East"
1011/// A3: "West"
1012/// B1: 2024
1013/// B2: 2025
1014/// B3: 2025
1015/// C1: 10
1016/// C2: 20
1017/// C3: 30
1018/// formula: "=SUMIFS(C1:C3, A1:A3, \"East\", B1:B3, \">=2025\")"
1019/// expected: 20
1020/// ```
1021///
1022/// ```yaml,sandbox
1023/// title: "Numeric criteria on single range"
1024/// formula: "=SUMIFS({5,10,20,30}, {1,2,3,4}, \">=2\", {1,2,3,4}, \"<=3\")"
1025/// expected: 30
1026/// ```
1027///
1028/// ```yaml,sandbox
1029/// title: "No matching rows yields zero"
1030/// formula: "=SUMIFS({10,20}, {\"A\",\"B\"}, \"C\")"
1031/// expected: 0
1032/// ```
1033///
1034/// ```yaml,docs
1035/// related:
1036/// - SUMIF
1037/// - COUNTIFS
1038/// - AVERAGEIFS
1039/// - SUMPRODUCT
1040/// faq:
1041/// - q: "How are multiple SUMIFS criteria combined?"
1042/// a: "All criteria pairs are applied with logical AND; every condition must match."
1043/// - q: "What if criteria range sizes differ?"
1044/// a: "Ranges are broadcast/padded under engine rules instead of strict Excel-size rejection."
1045/// ```
1046///
1047/// [formualizer-docgen:schema:start]
1048/// Name: SUMIFS
1049/// Type: SumIfsFn
1050/// Min args: 3
1051/// Max args: variadic
1052/// Variadic: true
1053/// Signature: SUMIFS(arg1...: any@scalar)
1054/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1055/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1056/// [formualizer-docgen:schema:end]
1057impl Function for SumIfsFn {
1058 func_caps!(
1059 PURE,
1060 REDUCTION,
1061 WINDOWED,
1062 STREAM_OK,
1063 PARALLEL_ARGS,
1064 PARALLEL_CHUNKS
1065 );
1066 fn name(&self) -> &'static str {
1067 "SUMIFS"
1068 }
1069 fn min_args(&self) -> usize {
1070 3
1071 }
1072 fn variadic(&self) -> bool {
1073 true
1074 }
1075 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1076 FunctionDependencyContract::criteria_aggregation(
1077 arity,
1078 FunctionArityRule::OddAtLeast(3),
1079 CriteriaValueRange::Fixed(0),
1080 1,
1081 )
1082 }
1083 fn arg_schema(&self) -> &'static [ArgSchema] {
1084 &ARG_ANY_ONE[..]
1085 }
1086 fn eval<'a, 'b, 'c>(
1087 &self,
1088 args: &'c [ArgumentHandle<'a, 'b>],
1089 ctx: &dyn FunctionContext<'b>,
1090 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1091 eval_if_family(args, ctx, AggregationType::Sum, true)
1092 }
1093}
1094
1095/* ─────────────────────────── COUNTIFS() ──────────────────────────── */
1096#[derive(Debug)]
1097pub struct CountIfsFn; // COUNTIFS(criteria_range1, criteria1, ...)
1098/// Counts cells that satisfy all supplied criteria pairs.
1099///
1100/// `COUNTIFS` applies each `(criteria_range, criteria)` pair and counts rows where all tests pass.
1101///
1102/// # Remarks
1103/// - Requires one or more criteria pairs.
1104/// - Criteria support operators and wildcard matching.
1105/// - A row contributes to the result only when every criterion evaluates true.
1106///
1107/// # Examples
1108///
1109/// ```yaml,sandbox
1110/// title: "Count rows matching two filters"
1111/// grid:
1112/// A1: "East"
1113/// A2: "East"
1114/// A3: "West"
1115/// B1: 12
1116/// B2: 8
1117/// B3: 15
1118/// formula: "=COUNTIFS(A1:A3, \"East\", B1:B3, \">=10\")"
1119/// expected: 1
1120/// ```
1121///
1122/// ```yaml,sandbox
1123/// title: "Wildcard text matching"
1124/// formula: "=COUNTIFS({\"apple\",\"pear\",\"apricot\"}, \"ap*\")"
1125/// expected: 2
1126/// ```
1127///
1128/// ```yaml,sandbox
1129/// title: "No rows meeting all criteria"
1130/// formula: "=COUNTIFS({1,2,3}, \">5\", {\"a\",\"b\",\"c\"}, \"a\")"
1131/// expected: 0
1132/// ```
1133///
1134/// ```yaml,docs
1135/// related:
1136/// - COUNTIF
1137/// - SUMIFS
1138/// - AVERAGEIFS
1139/// - FILTER
1140/// faq:
1141/// - q: "Why can COUNTIFS return 0 even when one criterion matches rows?"
1142/// a: "Each row must satisfy every criterion pair; partial matches are excluded."
1143/// - q: "Does COUNTIFS require at least one criteria pair?"
1144/// a: "Yes. It expects arguments in (range, criteria) pairs."
1145/// ```
1146///
1147/// [formualizer-docgen:schema:start]
1148/// Name: COUNTIFS
1149/// Type: CountIfsFn
1150/// Min args: 2
1151/// Max args: variadic
1152/// Variadic: true
1153/// Signature: COUNTIFS(arg1...: any@scalar)
1154/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1155/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1156/// [formualizer-docgen:schema:end]
1157impl Function for CountIfsFn {
1158 func_caps!(
1159 PURE,
1160 REDUCTION,
1161 WINDOWED,
1162 STREAM_OK,
1163 PARALLEL_ARGS,
1164 PARALLEL_CHUNKS
1165 );
1166 fn name(&self) -> &'static str {
1167 "COUNTIFS"
1168 }
1169 fn min_args(&self) -> usize {
1170 2
1171 }
1172 fn variadic(&self) -> bool {
1173 true
1174 }
1175 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1176 FunctionDependencyContract::criteria_aggregation(
1177 arity,
1178 FunctionArityRule::EvenAtLeast(2),
1179 CriteriaValueRange::None,
1180 0,
1181 )
1182 }
1183 fn arg_schema(&self) -> &'static [ArgSchema] {
1184 &ARG_ANY_ONE[..]
1185 }
1186 fn eval<'a, 'b, 'c>(
1187 &self,
1188 args: &'c [ArgumentHandle<'a, 'b>],
1189 ctx: &dyn FunctionContext<'b>,
1190 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1191 eval_if_family(args, ctx, AggregationType::Count, true)
1192 }
1193}
1194
1195/* ─────────────────────────── AVERAGEIFS() (moved) ──────────────────────────── */
1196#[derive(Debug)]
1197pub struct AverageIfsFn;
1198/// Returns the average of cells that satisfy multiple criteria.
1199///
1200/// `AVERAGEIFS` filters by all criteria pairs, then averages matching numeric values.
1201///
1202/// # Remarks
1203/// - The first argument is the average target range.
1204/// - Criteria are supplied in `(criteria_range, criteria)` pairs.
1205/// - If no numeric cells match, the function returns `#DIV/0!`.
1206///
1207/// # Examples
1208///
1209/// ```yaml,sandbox
1210/// title: "Average with two criteria"
1211/// grid:
1212/// A1: "East"
1213/// A2: "East"
1214/// A3: "West"
1215/// B1: 2025
1216/// B2: 2024
1217/// B3: 2025
1218/// C1: 10
1219/// C2: 40
1220/// C3: 30
1221/// formula: "=AVERAGEIFS(C1:C3, A1:A3, \"East\", B1:B3, \">=2025\")"
1222/// expected: 10
1223/// ```
1224///
1225/// ```yaml,sandbox
1226/// title: "Average over inline arrays"
1227/// formula: "=AVERAGEIFS({10,20,30}, {1,2,3}, \">=2\")"
1228/// expected: 25
1229/// ```
1230///
1231/// ```yaml,sandbox
1232/// title: "No matches returns divide-by-zero"
1233/// formula: "=AVERAGEIFS({10,20}, {\"A\",\"B\"}, \"C\")"
1234/// expected: "#DIV/0!"
1235/// ```
1236///
1237/// ```yaml,docs
1238/// related:
1239/// - AVERAGEIF
1240/// - AVERAGE
1241/// - SUMIFS
1242/// - COUNTIFS
1243/// faq:
1244/// - q: "When does AVERAGEIFS return #DIV/0!?"
1245/// a: "It returns #DIV/0! when no matching numeric cells are available to average."
1246/// - q: "Do non-numeric matched cells count in the average?"
1247/// a: "No. Only numeric target cells contribute to sum and count."
1248/// ```
1249///
1250/// [formualizer-docgen:schema:start]
1251/// Name: AVERAGEIFS
1252/// Type: AverageIfsFn
1253/// Min args: 3
1254/// Max args: variadic
1255/// Variadic: true
1256/// Signature: AVERAGEIFS(arg1...: any@scalar)
1257/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1258/// Caps: PURE, REDUCTION, WINDOWED, STREAM_OK, PARALLEL_ARGS, PARALLEL_CHUNKS
1259/// [formualizer-docgen:schema:end]
1260impl Function for AverageIfsFn {
1261 func_caps!(
1262 PURE,
1263 REDUCTION,
1264 WINDOWED,
1265 STREAM_OK,
1266 PARALLEL_ARGS,
1267 PARALLEL_CHUNKS
1268 );
1269 fn name(&self) -> &'static str {
1270 "AVERAGEIFS"
1271 }
1272 fn min_args(&self) -> usize {
1273 3
1274 }
1275 fn variadic(&self) -> bool {
1276 true
1277 }
1278 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1279 FunctionDependencyContract::criteria_aggregation(
1280 arity,
1281 FunctionArityRule::OddAtLeast(3),
1282 CriteriaValueRange::Fixed(0),
1283 1,
1284 )
1285 }
1286 fn arg_schema(&self) -> &'static [ArgSchema] {
1287 &ARG_ANY_ONE[..]
1288 }
1289 fn eval<'a, 'b, 'c>(
1290 &self,
1291 args: &'c [ArgumentHandle<'a, 'b>],
1292 ctx: &dyn FunctionContext<'b>,
1293 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1294 eval_if_family(args, ctx, AggregationType::Average, true)
1295 }
1296}
1297
1298/* ─────────────────────────── COUNTA() ──────────────────────────── */
1299#[derive(Debug)]
1300pub struct CountAFn; // counts non-empty (including empty text "")
1301/// Counts non-empty cells and scalar arguments.
1302///
1303/// `COUNTA` counts any value except true empty cells.
1304///
1305/// # Remarks
1306/// - Numbers, text, booleans, and errors all count.
1307/// - Empty string values (`""`) are counted as non-empty.
1308/// - Truly empty cells are the only values excluded.
1309///
1310/// # Examples
1311///
1312/// ```yaml,sandbox
1313/// title: "Count mixed populated values"
1314/// formula: "=COUNTA(1, \"x\", TRUE, \"\")"
1315/// expected: 4
1316/// ```
1317///
1318/// ```yaml,sandbox
1319/// title: "Range count excludes only true blanks"
1320/// grid:
1321/// A1: 10
1322/// A2: ""
1323/// formula: "=COUNTA(A1:A3)"
1324/// expected: 2
1325/// ```
1326///
1327/// ```yaml,sandbox
1328/// title: "Errors are counted"
1329/// formula: "=COUNTA(1/0, 5)"
1330/// expected: 2
1331/// ```
1332///
1333/// ```yaml,docs
1334/// related:
1335/// - COUNT
1336/// - COUNTBLANK
1337/// - COUNTIF
1338/// faq:
1339/// - q: "Does COUNTA count empty-string results like \"\"?"
1340/// a: "Yes. Empty text is counted as non-empty by COUNTA."
1341/// - q: "Are error values counted?"
1342/// a: "Yes. Errors are considered populated values and increase the count."
1343/// ```
1344///
1345/// [formualizer-docgen:schema:start]
1346/// Name: COUNTA
1347/// Type: CountAFn
1348/// Min args: 1
1349/// Max args: variadic
1350/// Variadic: true
1351/// Signature: COUNTA(arg1...: any@scalar)
1352/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1353/// Caps: PURE, REDUCTION
1354/// [formualizer-docgen:schema:end]
1355impl Function for CountAFn {
1356 func_caps!(PURE, REDUCTION);
1357 fn name(&self) -> &'static str {
1358 "COUNTA"
1359 }
1360 fn min_args(&self) -> usize {
1361 1
1362 }
1363 fn variadic(&self) -> bool {
1364 true
1365 }
1366 fn dependency_contract(&self, arity: usize) -> Option<FunctionDependencyContract> {
1367 FunctionDependencyContract::static_reduction(arity, self.min_args())
1368 }
1369 fn arg_schema(&self) -> &'static [ArgSchema] {
1370 &ARG_ANY_ONE[..]
1371 }
1372 fn eval<'a, 'b, 'c>(
1373 &self,
1374 args: &'c [ArgumentHandle<'a, 'b>],
1375 _ctx: &dyn FunctionContext<'b>,
1376 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1377 let mut cnt = 0i64;
1378 for a in args {
1379 if let Ok(view) = a.range_view() {
1380 for res in view.type_tags_slices() {
1381 let (_, _, tag_cols) = res?;
1382 for col in tag_cols {
1383 for i in 0..col.len() {
1384 if col.value(i) != crate::arrow_store::TypeTag::Empty as u8 {
1385 cnt += 1;
1386 }
1387 }
1388 }
1389 }
1390 } else {
1391 let v = a.value()?.into_literal();
1392 if !matches!(v, LiteralValue::Empty) {
1393 cnt += 1;
1394 }
1395 }
1396 }
1397 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
1398 cnt as f64,
1399 )))
1400 }
1401}
1402
1403/* ─────────────────────────── COUNTBLANK() ──────────────────────────── */
1404#[derive(Debug)]
1405pub struct CountBlankFn; // counts truly empty cells and empty text
1406/// Counts blank cells, including empty-string text results.
1407///
1408/// `COUNTBLANK` treats both true empty cells and `""` text values as blank.
1409///
1410/// # Remarks
1411/// - Empty-string text values are counted.
1412/// - Numbers, booleans, and non-empty text are not counted.
1413/// - Supports scalar arguments and ranges.
1414///
1415/// # Examples
1416///
1417/// ```yaml,sandbox
1418/// title: "Count blanks in a range"
1419/// grid:
1420/// A1: 10
1421/// A2: ""
1422/// formula: "=COUNTBLANK(A1:A3)"
1423/// expected: 2
1424/// ```
1425///
1426/// ```yaml,sandbox
1427/// title: "Scalar empty-string counts as blank"
1428/// formula: "=COUNTBLANK(\"\", 5)"
1429/// expected: 1
1430/// ```
1431///
1432/// ```yaml,sandbox
1433/// title: "Non-empty values are excluded"
1434/// formula: "=COUNTBLANK(1, \"x\", TRUE)"
1435/// expected: 0
1436/// ```
1437///
1438/// ```yaml,docs
1439/// related:
1440/// - COUNTA
1441/// - COUNT
1442/// - COUNTIF
1443/// faq:
1444/// - q: "Does COUNTBLANK include cells that contain \"\"?"
1445/// a: "Yes. Empty-string text values are treated as blank for COUNTBLANK."
1446/// - q: "Are numeric zeros considered blank?"
1447/// a: "No. Zero is a numeric value, so it is not counted as blank."
1448/// ```
1449///
1450/// [formualizer-docgen:schema:start]
1451/// Name: COUNTBLANK
1452/// Type: CountBlankFn
1453/// Min args: 1
1454/// Max args: variadic
1455/// Variadic: true
1456/// Signature: COUNTBLANK(arg1...: any@scalar)
1457/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1458/// Caps: PURE, REDUCTION
1459/// [formualizer-docgen:schema:end]
1460impl Function for CountBlankFn {
1461 func_caps!(PURE, REDUCTION);
1462 fn name(&self) -> &'static str {
1463 "COUNTBLANK"
1464 }
1465 fn min_args(&self) -> usize {
1466 1
1467 }
1468 fn variadic(&self) -> bool {
1469 true
1470 }
1471 fn arg_schema(&self) -> &'static [ArgSchema] {
1472 &ARG_ANY_ONE[..]
1473 }
1474 fn eval<'a, 'b, 'c>(
1475 &self,
1476 args: &'c [ArgumentHandle<'a, 'b>],
1477 _ctx: &dyn FunctionContext<'b>,
1478 ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1479 let mut cnt = 0i64;
1480 for a in args {
1481 if let Ok(view) = a.range_view() {
1482 let mut tag_it = view.type_tags_slices();
1483 let mut text_it = view.text_slices();
1484
1485 while let (Some(tag_res), Some(text_res)) = (tag_it.next(), text_it.next()) {
1486 let (_, _, tag_cols) = tag_res?;
1487 let (_, _, text_cols) = text_res?;
1488
1489 for (tc, xc) in tag_cols.into_iter().zip(text_cols.into_iter()) {
1490 let text_arr = xc
1491 .as_any()
1492 .downcast_ref::<arrow_array::StringArray>()
1493 .unwrap();
1494 for i in 0..tc.len() {
1495 let is_blank = tc.value(i) == crate::arrow_store::TypeTag::Empty as u8
1496 || (tc.value(i) == crate::arrow_store::TypeTag::Text as u8
1497 && !text_arr.is_null(i)
1498 && text_arr.value(i).is_empty());
1499 if is_blank {
1500 cnt += 1;
1501 }
1502 }
1503 }
1504 }
1505 } else {
1506 let v = a.value()?.into_literal();
1507 match v {
1508 LiteralValue::Empty => cnt += 1,
1509 LiteralValue::Text(s) if s.is_empty() => cnt += 1,
1510 _ => {}
1511 }
1512 }
1513 }
1514 Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
1515 cnt as f64,
1516 )))
1517 }
1518}
1519
1520pub fn register_builtins() {
1521 use std::sync::Arc;
1522 crate::function_registry::register_function(Arc::new(SumIfFn));
1523 crate::function_registry::register_function(Arc::new(CountIfFn));
1524 crate::function_registry::register_function(Arc::new(AverageIfFn));
1525 crate::function_registry::register_function(Arc::new(SumIfsFn));
1526 crate::function_registry::register_function(Arc::new(CountIfsFn));
1527 crate::function_registry::register_function(Arc::new(AverageIfsFn));
1528 crate::function_registry::register_function(Arc::new(CountAFn));
1529 crate::function_registry::register_function(Arc::new(CountBlankFn));
1530}
1531
1532#[cfg(test)]
1533mod tests {
1534 use super::*;
1535 use crate::test_workbook::TestWorkbook;
1536 use crate::traits::ArgumentHandle;
1537 use formualizer_common::LiteralValue;
1538 use formualizer_parse::parser::{ASTNode, ASTNodeType};
1539 fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1540 wb.interpreter()
1541 }
1542 fn lit(v: LiteralValue) -> ASTNode {
1543 ASTNode::new(ASTNodeType::Literal(v), None)
1544 }
1545
1546 #[test]
1547 fn sumif_basic() {
1548 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1549 let ctx = interp(&wb);
1550 let range = lit(LiteralValue::Array(vec![vec![
1551 LiteralValue::Int(1),
1552 LiteralValue::Int(2),
1553 LiteralValue::Int(3),
1554 ]]));
1555 let crit = lit(LiteralValue::Text(">1".into()));
1556 let args = vec![
1557 ArgumentHandle::new(&range, &ctx),
1558 ArgumentHandle::new(&crit, &ctx),
1559 ];
1560 let f = ctx.context.get_function("", "SUMIF").unwrap();
1561 assert_eq!(
1562 f.dispatch(&args, &ctx.function_context(None))
1563 .unwrap()
1564 .into_literal(),
1565 LiteralValue::Number(5.0)
1566 );
1567 }
1568
1569 #[test]
1570 fn sumif_with_sum_range() {
1571 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1572 let ctx = interp(&wb);
1573 let range = lit(LiteralValue::Array(vec![vec![
1574 LiteralValue::Int(1),
1575 LiteralValue::Int(0),
1576 LiteralValue::Int(1),
1577 ]]));
1578 let sum_range = lit(LiteralValue::Array(vec![vec![
1579 LiteralValue::Int(10),
1580 LiteralValue::Int(20),
1581 LiteralValue::Int(30),
1582 ]]));
1583 let crit = lit(LiteralValue::Text("=1".into()));
1584 let args = vec![
1585 ArgumentHandle::new(&range, &ctx),
1586 ArgumentHandle::new(&crit, &ctx),
1587 ArgumentHandle::new(&sum_range, &ctx),
1588 ];
1589 let f = ctx.context.get_function("", "SUMIF").unwrap();
1590 assert_eq!(
1591 f.dispatch(&args, &ctx.function_context(None))
1592 .unwrap()
1593 .into_literal(),
1594 LiteralValue::Number(40.0)
1595 );
1596 }
1597
1598 #[test]
1599 fn sumif_numeric_zero_matches_blank_in_text_column() {
1600 // Regression test: if the criteria range is text-typed (no numeric fast-path column),
1601 // numeric criteria should still match blanks (Excel semantics: blank coerces to 0).
1602 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1603 let ctx = interp(&wb);
1604
1605 // Criteria range is a 1x2 row with (blank, "x") so the column is non-numeric.
1606 let range = lit(LiteralValue::Array(vec![vec![
1607 LiteralValue::Empty,
1608 LiteralValue::Text("x".into()),
1609 ]]));
1610 let sum_range = lit(LiteralValue::Array(vec![vec![
1611 LiteralValue::Int(5),
1612 LiteralValue::Int(7),
1613 ]]));
1614 let crit = lit(LiteralValue::Int(0));
1615
1616 let args = vec![
1617 ArgumentHandle::new(&range, &ctx),
1618 ArgumentHandle::new(&crit, &ctx),
1619 ArgumentHandle::new(&sum_range, &ctx),
1620 ];
1621 let f = ctx.context.get_function("", "SUMIF").unwrap();
1622 assert_eq!(
1623 f.dispatch(&args, &ctx.function_context(None))
1624 .unwrap()
1625 .into_literal(),
1626 LiteralValue::Number(5.0)
1627 );
1628 }
1629
1630 #[test]
1631 fn sumif_mismatched_ranges_now_pad_with_empty() {
1632 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1633 let ctx = interp(&wb);
1634 // sum_range: 2x2
1635 let sum = lit(LiteralValue::Array(vec![
1636 vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1637 vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1638 ]));
1639 // criteria range: 3x2 (extra row should be ignored due to iterating sum_range dims)
1640 let crit_range = lit(LiteralValue::Array(vec![
1641 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1642 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1643 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1644 ]));
1645 let crit = lit(LiteralValue::Text("=1".into()));
1646 let args = vec![
1647 ArgumentHandle::new(&crit_range, &ctx),
1648 ArgumentHandle::new(&crit, &ctx),
1649 ArgumentHandle::new(&sum, &ctx),
1650 ];
1651 let f = ctx.context.get_function("", "SUMIF").unwrap();
1652 assert_eq!(
1653 f.dispatch(&args, &ctx.function_context(None))
1654 .unwrap()
1655 .into_literal(),
1656 LiteralValue::Number(10.0)
1657 );
1658 }
1659
1660 #[test]
1661 fn countif_text_wildcard() {
1662 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfFn));
1663 let ctx = interp(&wb);
1664 let rng = lit(LiteralValue::Array(vec![vec![
1665 LiteralValue::Text("alpha".into()),
1666 LiteralValue::Text("beta".into()),
1667 LiteralValue::Text("alphabet".into()),
1668 ]]));
1669 let crit = lit(LiteralValue::Text("al*".into()));
1670 let args = vec![
1671 ArgumentHandle::new(&rng, &ctx),
1672 ArgumentHandle::new(&crit, &ctx),
1673 ];
1674 let f = ctx.context.get_function("", "COUNTIF").unwrap();
1675 assert_eq!(
1676 f.dispatch(&args, &ctx.function_context(None))
1677 .unwrap()
1678 .into_literal(),
1679 LiteralValue::Number(2.0)
1680 );
1681 }
1682
1683 #[test]
1684 fn sumifs_multiple_criteria() {
1685 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1686 let ctx = interp(&wb);
1687 let sum = lit(LiteralValue::Array(vec![vec![
1688 LiteralValue::Int(10),
1689 LiteralValue::Int(20),
1690 LiteralValue::Int(30),
1691 LiteralValue::Int(40),
1692 ]]));
1693 let city = lit(LiteralValue::Array(vec![vec![
1694 LiteralValue::Text("Bellevue".into()),
1695 LiteralValue::Text("Issaquah".into()),
1696 LiteralValue::Text("Bellevue".into()),
1697 LiteralValue::Text("Issaquah".into()),
1698 ]]));
1699 let beds = lit(LiteralValue::Array(vec![vec![
1700 LiteralValue::Int(2),
1701 LiteralValue::Int(3),
1702 LiteralValue::Int(4),
1703 LiteralValue::Int(5),
1704 ]]));
1705 let c_city = lit(LiteralValue::Text("Bellevue".into()));
1706 let c_beds = lit(LiteralValue::Text(">=4".into()));
1707 let args = vec![
1708 ArgumentHandle::new(&sum, &ctx),
1709 ArgumentHandle::new(&city, &ctx),
1710 ArgumentHandle::new(&c_city, &ctx),
1711 ArgumentHandle::new(&beds, &ctx),
1712 ArgumentHandle::new(&c_beds, &ctx),
1713 ];
1714 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1715 assert_eq!(
1716 f.dispatch(&args, &ctx.function_context(None))
1717 .unwrap()
1718 .into_literal(),
1719 LiteralValue::Number(30.0)
1720 );
1721 }
1722
1723 #[test]
1724 fn countifs_basic() {
1725 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1726 let ctx = interp(&wb);
1727 let city = lit(LiteralValue::Array(vec![vec![
1728 LiteralValue::Text("a".into()),
1729 LiteralValue::Text("b".into()),
1730 LiteralValue::Text("a".into()),
1731 ]]));
1732 let beds = lit(LiteralValue::Array(vec![vec![
1733 LiteralValue::Int(1),
1734 LiteralValue::Int(2),
1735 LiteralValue::Int(3),
1736 ]]));
1737 let c_city = lit(LiteralValue::Text("a".into()));
1738 let c_beds = lit(LiteralValue::Text(">1".into()));
1739 let args = vec![
1740 ArgumentHandle::new(&city, &ctx),
1741 ArgumentHandle::new(&c_city, &ctx),
1742 ArgumentHandle::new(&beds, &ctx),
1743 ArgumentHandle::new(&c_beds, &ctx),
1744 ];
1745 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1746 assert_eq!(
1747 f.dispatch(&args, &ctx.function_context(None))
1748 .unwrap()
1749 .into_literal(),
1750 LiteralValue::Number(1.0)
1751 );
1752 }
1753
1754 #[test]
1755 fn averageifs_div0() {
1756 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1757 let ctx = interp(&wb);
1758 let avg = lit(LiteralValue::Array(vec![vec![
1759 LiteralValue::Int(1),
1760 LiteralValue::Int(2),
1761 ]]));
1762 let crit_rng = lit(LiteralValue::Array(vec![vec![
1763 LiteralValue::Int(0),
1764 LiteralValue::Int(0),
1765 ]]));
1766 let crit = lit(LiteralValue::Text(">0".into()));
1767 let args = vec![
1768 ArgumentHandle::new(&avg, &ctx),
1769 ArgumentHandle::new(&crit_rng, &ctx),
1770 ArgumentHandle::new(&crit, &ctx),
1771 ];
1772 let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1773 match f
1774 .dispatch(&args, &ctx.function_context(None))
1775 .unwrap()
1776 .into_literal()
1777 {
1778 LiteralValue::Error(e) => assert_eq!(e, "#DIV/0!"),
1779 _ => panic!("expected div0"),
1780 }
1781 }
1782
1783 #[test]
1784 fn counta_and_countblank() {
1785 let wb = TestWorkbook::new()
1786 .with_function(std::sync::Arc::new(CountAFn))
1787 .with_function(std::sync::Arc::new(CountBlankFn));
1788 let ctx = interp(&wb);
1789 let arr = lit(LiteralValue::Array(vec![vec![
1790 LiteralValue::Empty,
1791 LiteralValue::Text("".into()),
1792 LiteralValue::Int(5),
1793 ]]));
1794 let args = vec![ArgumentHandle::new(&arr, &ctx)];
1795 let counta = ctx.context.get_function("", "COUNTA").unwrap();
1796 let countblank = ctx.context.get_function("", "COUNTBLANK").unwrap();
1797 assert_eq!(
1798 counta
1799 .dispatch(&args, &ctx.function_context(None))
1800 .unwrap()
1801 .into_literal(),
1802 LiteralValue::Number(2.0)
1803 );
1804 assert_eq!(
1805 countblank
1806 .dispatch(&args, &ctx.function_context(None))
1807 .unwrap()
1808 .into_literal(),
1809 LiteralValue::Number(2.0)
1810 );
1811 }
1812
1813 // ───────── Parity tests (window vs scalar) ─────────
1814 #[test]
1815 fn sumifs_broadcasts_1x1_criteria_over_range() {
1816 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1817 let ctx = interp(&wb);
1818 // sum_range: column vector [10, 20]
1819 let sum = lit(LiteralValue::Array(vec![
1820 vec![LiteralValue::Int(10)],
1821 vec![LiteralValue::Int(20)],
1822 ]));
1823 // criteria_range: column vector ["A", "B"]
1824 let tags = lit(LiteralValue::Array(vec![
1825 vec![LiteralValue::Text("A".into())],
1826 vec![LiteralValue::Text("B".into())],
1827 ]));
1828 // criteria: 1x1 array acting as scalar "A"
1829 let c_tag = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1830 "A".into(),
1831 )]]));
1832 let args = vec![
1833 ArgumentHandle::new(&sum, &ctx),
1834 ArgumentHandle::new(&tags, &ctx),
1835 ArgumentHandle::new(&c_tag, &ctx),
1836 ];
1837 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1838 assert_eq!(
1839 f.dispatch(&args, &ctx.function_context(None))
1840 .unwrap()
1841 .into_literal(),
1842 LiteralValue::Number(10.0)
1843 );
1844 }
1845
1846 #[test]
1847 fn countifs_broadcasts_1x1_criteria_over_row() {
1848 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1849 let ctx = interp(&wb);
1850 // criteria_range: row [1,2,3,4]
1851 let nums = lit(LiteralValue::Array(vec![vec![
1852 LiteralValue::Int(1),
1853 LiteralValue::Int(2),
1854 LiteralValue::Int(3),
1855 LiteralValue::Int(4),
1856 ]]));
1857 // criteria: 1x1 array ">=3"
1858 let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1859 ">=3".into(),
1860 )]]));
1861 let args = vec![
1862 ArgumentHandle::new(&nums, &ctx),
1863 ArgumentHandle::new(&crit, &ctx),
1864 ];
1865 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1866 assert_eq!(
1867 f.dispatch(&args, &ctx.function_context(None))
1868 .unwrap()
1869 .into_literal(),
1870 LiteralValue::Number(2.0)
1871 );
1872 }
1873
1874 #[test]
1875 fn sumifs_empty_ranges_with_1x1_criteria_produce_zero() {
1876 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1877 let ctx = interp(&wb);
1878 // Empty ranges (0x0) simulate unused whole-column resolved empty
1879 let empty = lit(LiteralValue::Array(Vec::new()));
1880 // 1x1 criteria (array)
1881 let crit = lit(LiteralValue::Array(vec![vec![LiteralValue::Text(
1882 "X".into(),
1883 )]]));
1884 let args = vec![
1885 ArgumentHandle::new(&empty, &ctx),
1886 ArgumentHandle::new(&empty, &ctx),
1887 ArgumentHandle::new(&crit, &ctx),
1888 ];
1889 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1890 assert_eq!(
1891 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1892 LiteralValue::Number(0.0)
1893 );
1894 }
1895
1896 #[test]
1897 fn sumifs_mismatched_ranges_now_pad_with_empty() {
1898 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfsFn));
1899 let ctx = interp(&wb);
1900 // sum_range: 2x2
1901 let sum = lit(LiteralValue::Array(vec![
1902 vec![LiteralValue::Int(1), LiteralValue::Int(2)],
1903 vec![LiteralValue::Int(3), LiteralValue::Int(4)],
1904 ]));
1905 // criteria_range: 3x2 (different rows - extra row will match against padded empty values)
1906 let crit_range = lit(LiteralValue::Array(vec![
1907 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1908 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1909 vec![LiteralValue::Int(1), LiteralValue::Int(1)],
1910 ]));
1911 // scalar criterion
1912 let crit = lit(LiteralValue::Text("=1".into()));
1913 let args = vec![
1914 ArgumentHandle::new(&sum, &ctx),
1915 ArgumentHandle::new(&crit_range, &ctx),
1916 ArgumentHandle::new(&crit, &ctx),
1917 ];
1918 let f = ctx.context.get_function("", "SUMIFS").unwrap();
1919 // With padding, sum_range gets padded with empties for row 3
1920 // Rows 1-2 match criteria (all 1s), row 3 has empties which don't match =1
1921 // So we sum: 1 + 2 + 3 + 4 = 10
1922 assert_eq!(
1923 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1924 LiteralValue::Number(10.0)
1925 );
1926 }
1927
1928 #[test]
1929 fn countifs_mismatched_ranges_pad_and_broadcast() {
1930 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CountIfsFn));
1931 let ctx = interp(&wb);
1932 // criteria_range1: 2x1 -> [1,1]
1933 let r1 = lit(LiteralValue::Array(vec![
1934 vec![LiteralValue::Int(1)],
1935 vec![LiteralValue::Int(1)],
1936 ]));
1937 // criteria1: "=1"
1938 let c1 = lit(LiteralValue::Text("=1".into()));
1939 // criteria_range2: 3x1 -> [1,1,1]
1940 let r2 = lit(LiteralValue::Array(vec![
1941 vec![LiteralValue::Int(1)],
1942 vec![LiteralValue::Int(1)],
1943 vec![LiteralValue::Int(1)],
1944 ]));
1945 // criteria2: "=1"
1946 let c2 = lit(LiteralValue::Text("=1".into()));
1947 let args = vec![
1948 ArgumentHandle::new(&r1, &ctx),
1949 ArgumentHandle::new(&c1, &ctx),
1950 ArgumentHandle::new(&r2, &ctx),
1951 ArgumentHandle::new(&c2, &ctx),
1952 ];
1953 let f = ctx.context.get_function("", "COUNTIFS").unwrap();
1954 // Union rows = 3; row3 has r1=Empty (padded), which doesn't match =1; expect 2
1955 assert_eq!(
1956 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1957 LiteralValue::Number(2.0)
1958 );
1959 }
1960
1961 #[test]
1962 fn averageifs_mismatched_ranges_pad() {
1963 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(AverageIfsFn));
1964 let ctx = interp(&wb);
1965 // avg_range: 2x1 -> [10,20]
1966 let avg = lit(LiteralValue::Array(vec![
1967 vec![LiteralValue::Int(10)],
1968 vec![LiteralValue::Int(20)],
1969 ]));
1970 // criteria_range: 3x1 -> [1,1,2]
1971 let r1 = lit(LiteralValue::Array(vec![
1972 vec![LiteralValue::Int(1)],
1973 vec![LiteralValue::Int(1)],
1974 vec![LiteralValue::Int(2)],
1975 ]));
1976 let c1 = lit(LiteralValue::Text("=1".into()));
1977 let args = vec![
1978 ArgumentHandle::new(&avg, &ctx),
1979 ArgumentHandle::new(&r1, &ctx),
1980 ArgumentHandle::new(&c1, &ctx),
1981 ];
1982 let f = ctx.context.get_function("", "AVERAGEIFS").unwrap();
1983 // Only first two rows match; expect (10+20)/2 = 15
1984 assert_eq!(
1985 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
1986 LiteralValue::Number(15.0)
1987 );
1988 }
1989
1990 #[test]
1991 fn criteria_scientific_notation() {
1992 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(SumIfFn));
1993 let ctx = interp(&wb);
1994 let nums = lit(LiteralValue::Array(vec![vec![
1995 LiteralValue::Number(1000.0),
1996 LiteralValue::Number(1500.0),
1997 LiteralValue::Number(999.0),
1998 ]]));
1999 let crit = lit(LiteralValue::Text(">1e3".into())); // should parse as >1000
2000 let args = vec![
2001 ArgumentHandle::new(&nums, &ctx),
2002 ArgumentHandle::new(&crit, &ctx),
2003 ];
2004 let f = ctx.context.get_function("", "SUMIF").unwrap();
2005 // >1000 matches 1500 only (strict greater)
2006 assert_eq!(
2007 f.dispatch(&args, &ctx.function_context(None)).unwrap(),
2008 LiteralValue::Number(1500.0)
2009 );
2010 }
2011}