IF function, inclunde full range

Jarke

Board Regular
Joined
Aug 13, 2016
Messages
95
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
A
B
C
3
QSIZE
4
10​
Scriteria
5
5​
LS
6
10​
XLL
7
10​
XLXL
8
10​
L
9
10​
S
10
15​
XL
11
12
70​

<tbody>
</tbody>


A12=
SUMPRODUCT(SUMIF(B4:B10,C5:C7,A4:A10))
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top