# IF function, inclunde full range

#### Jarke

##### Board Regular
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?

### 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
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
 A​ B​ C​ 3​ Q SIZE 4​ 10​ S criteria 5​ 5​ L S 6​ 10​ XL L 7​ 10​ XL XL 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
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
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).

Replies
2
Views
48
Replies
3
Views
70
Replies
6
Views
84
Replies
4
Views
82
Replies
3
Views
228

### Forum statistics

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.

### Which adblocker are you using?

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

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