IF function, inclunde full range

[Jarke]

Hi,

I have some formulas in which I would like to filter my data, as the normal "filter" function don't remove data in the ranges, the formulas won't change.

So for example, in A3:A2000 I have quantity and in B3:B2000 I have sizes: S, L and XL

Let's say I have a formula to sum the quantity for each, depending in a criteria cell that I define. But how would I sum all of the sizes in one formula?

In the criteria cell I could type S or L or XL, but what should I do to include all?


Thanks in advance

 

[Fluff]

If you want to use filters take a look at the SUBTOTAL function.
https://www.ablebits.com/office-addins-blog/2016/08/16/excel-subtotal-function/

 

[Jarke]

Thank you that looks interesting!

However, I have many sheets and would also want to try to avoid having to go in every one of them to filter them. It would be very neat to have a master cell that filters all data. My solution was correct except I don't know how I would include all "sizes", I'm thinking of adding something more to the formula, perhaps, OR or something, but not very good at those functions.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Q[/TD]
[TD]SIZE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

10​

[/TD]
[TD]S[/TD]
[TD]criteria[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]L[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

10​

[/TD]
[TD]XL[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

10​

[/TD]
[TD]XL[/TD]
[TD]XL[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

10​

[/TD]
[TD]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

15​

[/TD]
[TD]XL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

70​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


A12=SUMPRODUCT(SUMIF(B4:B10,C5:C7,A4:A10))

 

[Fluff]

If you use
=SUBTOTAL(109,A2:A20000)
It will sum all visible cells. So if you don't filter col B It will sum the entire column (ie all sizes), but if you filter col B on S it will only sum those sizes.

 

[Jarke]

Thanks guys!

Perhaps I used a bad example in the quest for my solution tho. This is one of my formulas: =IFERROR(TRIMMEAN(IF(M$3:M$2000>$T20,IF(M$3:M$2000<$U20,IF(X39>$X$7,IF(ISNUMBER(M$3:M$2000),H$3:H$2000)))),$X$6),"")

I would like to add a criteria where it filters out my data based on the text in D3:D2000 - only take the data where the data in D matches the criteria for D. My initial try was that I added: ,IF(D3:D2000=AN11,IF.....

Where AN11 is the criteria text for the column D. This works fine, but what should I do to also be able to chose alla data and not just one criteria (the one typed in AN11).