CODEWITHSUNDEEP
excelexcel-formula
quinekxi
quinekxi

Reputation: 889

How to dynamically use the formula based on filter 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 value of SumSQ using SUM function like I did in the image.

Expected value is: 30323295

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

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

Note that the SumSQ is kind of a helper column.

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

So basically this is how it should look like without 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 this must be dynamic based on filter on column A.

Example with filter in PC_Number:

enter image description here

It means that it should only compute what is only visible based on filter.

I have tried using SUBTOTAL() function added with OFFSET(), MIN(), and ROW() but it wont work as what I wanted to.

This is currently what I 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 output using the formula I have:

enter image description here

Which have a big difference on the total value.

Not sure I explained it well but that's the gist of it.

Upvotes: 3

Views: 4062

Answers (2)

A.S.H
A.S.H

Reputation: 29352

Normal formula, not 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 by column A, suppose you have to filter by a value that you enter in A1:

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

EDIT 2: to make the filtering optional, the next formula applies the filtering unless A1 is blank, in which case it accounts for on all the 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: to take into account only visible (non-filtered out) rows:

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

Idea is that the subtotal(2) on an individual and numeric cell returns 1 (0) if the cell is visible (hidden).

Upvotes: 3

Kyle
Kyle

Reputation: 2545

You need to use an array formula, and I don't believe you can use SUMSQ(). The below prodcues the desired result. Please not this is to be entered with Shift + Ctrl + Enter.

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

Upvotes: 3

Related Questions