Clever Geek Handbook

How to dynamically use filter based formula in excel?

I'm having a hard time figuring out how to get the total value without using the SUM function.

Example:

enter image description here

I can get the total SumSQ value using the SUM function like in the image.

Expected Value: 30323295

SumSQ formula: SumSQ = (SD ^ 2 * count * (count - 1) + (SumX ^ 2)) / count

In excel: SumSQ = (SUMSQ (D3) * B3 * (B3-1) + (SUMSQ (C3))) / B3

Note that SumSQ is a kind of helper column.

I want to avoid using a helper column, in this case SumSQ. But I still want to get the total value 30323295.

So basically it should look like without the filter:

enter image description here

Formula for Total SumSQ: SumSQ = (total_SD ^ 2 * total_count * (total_count - 1) + (total_SumX ^ 2)) / total_count

And note that this should be dynamic based on the filter in column A.

Example with filter in PC_Number:

enter image description here

This means that it should only calculate what is visible only based on the filter.

I tried using the SUBTOTAL () function added with OFFSET (), MIN () and ROW (), but it won't work the way I wanted.

I currently have:

--start

=SUMPRODUCT(((SUBTOTAL(9,OFFSET(U5:U256,ROW(U5:U256)-MIN(ROW(U5:U256)),,1))^2)*SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1))*(SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1))-1)+((SUBTOTAL(9,OFFSET(Q5:Q256,ROW(Q5:Q256)-MIN(ROW(Q5:Q256)),,1))*SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1)))^2))/SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1)))

--end

This is the result using formula I:

enter image description here

Which have a big difference in total cost.

Not sure if I explained it well, but the gist of it.

+3


source to share


2 answers


A normal formula, not an array formula, just using SumProduct

:

=SUMPRODUCT((D3:D9*D3:D9*B3:B9*(B3:B9-1)+(C3:C9*C3:C9))/B3:B9)

OR

=SUMPRODUCT((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9)

EDIT 1: filtering

To add some filtering to column A

, let's say you need to filter the value you enter into A1

:



=SUMPRODUCT((A3:A9 = $A$1)*((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9))

EDIT 2: To make filtering optional, the following formula applies filtering unless A1

empty, in which case it counts for all rows:

=SUMPRODUCT((A3:A9=IF(ISBLANK(A1),A3:A9,A1))*((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9))

EDIT 3: Consider only visible (not filtered) rows:

=SUMPRODUCT(SUBTOTAL(2, OFFSET(B1, ROW(B3:B9)-1,0))*((D3:D9^2*B3:B9*(B3:B9-1)+C3:C9^2)/B3:B9))

The idea is that subtotal(2)

in single and numeric cells it returns 1 (0) if the cell is visible (hidden).

+3


source


You need to use an array formula and I don't think you can use SUMSQ()

. Below you will get the desired result. Please do not enter with Shift + Ctrl + Enter

.



{=SUM(((D3:D9)^2 * (B3:B9)*((B3:B9)-1)+((C3:C9)^2))/B3:B9)}
+3


source






More articles:


All Articles