3-d formulas in Excel+COUNTIF workaround

This video shows 3-D formulas in Excel with a COUNTIF, which does not work in its raw state. The 3-D formulas work with SUM, AVERAGE, MIN, and MAX but not with conditionals like IF and COUNTIF

Link to workbook:

[DataDashPro]

Love your channel. Learn a lot... Thank you.

[jazzista]

Very elegant solution Victor. At the beginning i thought you were going to use INDIRECT

[peterbartholomew]

Slightly different packaging! I defined a couple of named formulas to return the arrays


The formulas then become
= SUM(values)
= COUNT(FILTER(values, values>10))
= COUNTA(FILTER(regions, regions="South"))

[williamarthur]

I did vstack, I was thinking of using indirect at first, but then i used an if statement;
SUM(IF(VSTACK(XYZ:CDE!A1:A10)>5, 1, 0))