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