COUNTIFS with range in criteria?

[BatTodor]

I'm trying to count items with ranges as criteria, not a separate value.
Using win this way, it's works:
=LET(
range, INDEX(CA3#, , 54),
uniqueRange, UNIQUE(range),
positions, COUNTIF(range, uniqueRange),
changes, COUNTIFS(range, uniqueRange, INDEX(CA3#, , 16), "Yes"),
percentage, changes/positions,
transport, IF(percentage >= 0.75, "Variant 1", IF(percentage >= 0.5, "Variant 1", "Variant 1")),
table, HSTACK(uniqueRange, changes, positions, transport),
table
)

When I use same approach but in more complex formula (data generate spill range in CA3# in formula above), it's not worked:
=LET(
...
data, HSTACK(dataTable, tempTableWithHeaders, filteredData1, readyTable),

range, INDEX(data, , 54),
uniqueRange, UNIQUE(range),
positions, COUNTIF(range, uniqueRange),
changes, COUNTIFS(range, uniqueRange, INDEX(data, , 16); "Yes"),
percentage, changes/positions,
transport, IF(percentage >= 0.75, "Variant 1", IF(percentage >= 0.5, "Variant 1", "Variant 1")),
table, HSTACK(uniqueRange, changes, positions, transport),
table
)

Receiving message "A value used in the formula is on the wrong data type" on counting - positions and changes.

Any idea?

 

[Fluff]

All the xxxIFS function need a range, but in the 2nd formula "data" is an array, so it cannot work.

 

[BatTodor]

All the xxxIFS function need a range, but in the 2nd formula "data" is an array, so it cannot work.

Failed to understand you, excuse me!
In separate formula I defined range with INDEX(CA3#, , 54), in more complex formula, range is defined as INDEX(data, , 54).
You mean that referencing spill range (CA3#) it's calculated as range (in separated first formula). But being part of more complex formula, same range (data) is calculated as array (in complex 2nd formula)?

Any suggestion how to count how many times unique values from column 54 appears in same array?
I prefer to make it in same complex formula preventing from mistakes.

 

[Fluff]

Yes, CA3# is a range, but the HSTACK function returns an array, not a range.

As you are only using 2 columns from the data variable, why not just refer to those ranges, rather than using HSTACK

 

[BatTodor]

Yes, CA3# is a range, but the HSTACK function returns an array, not a range.

As you are only using 2 columns from the data variable, why not just refer to those ranges, rather than using HSTACK

Before data array, produced with HSTACK(dataTable, tempTableWithHeaders, filteredData1, readyTable), I calculated many columns on dataTable, TempTableWithHeaders, filteredData1 and readyTable. Therefore, I'm trying to "merge" this additional calculations (with COUNTIF/S on positions and changes).

Seems that only possible way is to use MAP and make counting with LAMBDA?

Or use first formula as helper table and row-by-row with XLOOKUP to merge needed information outside spilled range data?

 

[Fluff]

As your formula is only looking at columns 54 & 16 of the Hstack, there is no need to use use Hstack to "merge" all 4 sections, just refer to the actual ranges needed for those two columns.

 

[BatTodor]

As your formula is only looking at columns 54 & 16 of the Hstack, there is no need to use use Hstack to "merge" all 4 sections, just refer to the actual ranges needed for those two columns.

I'm starting with range (table) with 15 columns, and transform it with "merging" information from 3 other tables and at finally data array include 55 columns
You mean:
1. To produce separate columns (without HSTACK) for column 54 and 16 (from data array).
2. To count it as separate columns
3. After that to "append" it to data

Hm, seems that would be not so easy, lots of calculations in my huge formula uses HSTACK and VSTACK (for headers in most cases) :-/

Is it possible to transform an array (from HSTACK) to range (and use it in COUNTIFS)?

 

[Fluff]

As these are not created inside the formula "dataTable, tempTableWithHeaders, filteredData1, readyTable", I'm assuming that they are named ranges or tables, is that correct.

 

[BatTodor]

As these are not created inside the formula "dataTable, tempTableWithHeaders, filteredData1, readyTable", I'm assuming that they are named ranges or tables, is that correct.

No, all of dataTable, dataTable, tempTableWithHeaders, filteredData1, readyTable are spilled arrays (using lots of HSTACK).
Probably would be possible (but hard) to calculate (without HSTACK) every from used in COUNTIFS columns...

Therefore, I wonder if there is a way to "convert" arrays (from HSTACK) to normal range (and use it in COUNTIFS).

 

[Fluff]

I wonder if there is a way to "convert" arrays (from HSTACK) to normal range (and use it in COUNTIFS).

The only way is to output the array to a cell as a spilled range & use that.

No, all of dataTable, dataTable, tempTableWithHeaders, filteredData1, readyTable are spilled arrays (using lots of HSTACK).

Do you mean they are all calculated within the formula you posted? As it it does not show that.