Скачать или смотреть EUS 5.0 - Excel User Series 5.0 - V#15 - IF - Sum,Count & Average using Dynamic Spilled Array Unique

#Unique, #SUMIF, #COUNTIF, #averageifs

The functions SUMIF, COUNTIF, and AVERAGEIF don't directly support dynamic spilled arrays in the way you might be thinking. These functions work by comparing a single criteria range against a single sum/count/average range. While you can use spilled ranges as input to these functions, the output will not be a spilled array reflecting unique values. They'll calculate a single result based on the entire spilled range.

To achieve unique value calculations within spilled arrays, you'll need to combine UNIQUE, FILTER, and the relevant aggregate function.

Here's how you'd approach it, assuming your data is in a spilled array:

1. Unique values:

Use UNIQUE(your_spilled_array) to get a list of unique values.
2. Conditional Aggregation:

Use FILTER to subset the original array based on your condition, then apply SUM, COUNT, or AVERAGE. This requires nesting the functions.
Example: Let's say your data is in A1#, which spills dynamically. Column A contains names, column B contains values.

SUMIF equivalent for unique names: SUM(FILTER(B1#,A1#=UNIQUE(A1#))) This sums the values in B where the name in A matches each unique name. The result will be a spilled array of sums for each unique name.

COUNTIF equivalent for unique names: COUNT(FILTER(B1#,A1#=UNIQUE(A1#))) This counts the values in B where the name in A matches each unique name. The result will be a spilled array of counts for each unique name.

AVERAGEIF equivalent for unique names: AVERAGE(FILTER(B1#,A1#=UNIQUE(A1#))) This averages the values in B where the name in A matches each unique name. The result will be a spilled array of averages for each unique name.

This approach allows for dynamic updates as your data changes, but it relies on the combination of UNIQUE, FILTER, and standard aggregate functions, not a direct extension of the -IF functions. Do you need a more specific example with sample data? Knowing the structure of your data will help me provide a more tailored solution.