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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Jarke

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

Well-known Member
Joined
Aug 22, 2015
Messages
767
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))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

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
Top