# Optimize Formula

#### Leticia

##### New Member
Hi!
How can I optimize the following formula - putting it in just one Countifs?

COUNTIFS('D'!\$P:\$P;"OTRO";'D'!\$G:\$G;"<="&\$F\$4)+COUNTIFS('D'!\$G:\$G;"<="&\$F\$4;'D'!\$Q:\$Q;">"&\$F\$4)

Thank you!

#### Leticia

##### New Member
Not sure what you mean.
A library to have access to the LET function

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Fluff

##### MrExcel MVP, Moderator
You will need install the latest updates.

Also I think that you can simplify your formulae like
Excel Formula:
``=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!C:C="MISSING")*(TEST!\$A:\$A<=\$F\$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")``

#### Leticia

##### New Member
You will need install the latest updates.

Also I think that you can simplify your formulae like
Excel Formula:
``=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!C:C="MISSING")*(TEST!\$A:\$A<=\$F\$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")``
Great thanks. Much better.
And still no possibility to mix it with another formula right?

#### Fluff

##### MrExcel MVP, Moderator
It's possible, but I would not recommend it.

#### Leticia

##### New Member

ADVERTISEMENT

It's possible, but I would not recommend it.
Could you give me a hint? I really need to put them at least in two formulas instead of three so that I get the top 5 in the two possible scenarios

#### Fluff

##### MrExcel MVP, Moderator
Why can't you just keep them as three separate formulae?
If you put them all into one formula then they will be recalculating when there is no need.
For instance the formula in post#12 looks at col C, but your other two don't, so with 3 formulae only one will recalc if you change col C rather than one massive formula recalculating.

#### Leticia

##### New Member

ADVERTISEMENT

Why can't you just keep them as three separate formulae?
If you put them all into one formula then they will be recalculating when there is no need.
For instance the formula in post#12 looks at col C, but your other two don't, so with 3 formulae only one will recalc if you change col C rather than one massive formula recalculating.
Because I have two scenarios, and I need the top 5 for each of them.

Scenario 1: SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4)+SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4)
Scenario 2: SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSSING";TEST!\$A:\$A;"<="&\$F\$4)+SUMIFS(TEST!\$F:\$F;TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1))

By having three formulas I cannot obtain my top 5

#### Fluff

##### MrExcel MVP, Moderator
I'm now totally confused, what have those two formulae got to do with either of these
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!C:C="MISSING")*(TEST!\$A:\$A<=\$F\$4))))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")``
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4))))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")``
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250"))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1);TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250"))))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1);TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250")))));-1);SEQUENCE(5;1;1);{1\2});"-")``

#### Leticia

##### New Member
I'm now totally confused, what have those two formulae got to do with either of these
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!C:C="MISSING")*(TEST!\$A:\$A<=\$F\$4))))));SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$C:\$C="MISSING")*(TEST!\$A:\$A<=\$F\$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")``
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4))))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")``
Excel Formula:
``=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250"))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1);TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250"))))));SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1);TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$B:\$B;UNIQUE(FILTER(TEST!\$B:\$B;((TEST!\$D:\$D>\$F\$4)*(TEST!\$A:\$A<=\$F\$4)*(TEST!\$D:\$D<EDATE(\$F\$4;1))*(TEST!\$E:\$E<>"R.")*(TEST!\$E:\$E<>"7250")))));-1);SEQUENCE(5;1;1);{1\2});"-")``

The first one corresponds to the top 5 values based on SUMIFS(TEST!\$F:\$F;TEST!\$C:\$C;"MISSING";TEST!\$A:\$A;"<="&\$F\$4) and sorted by B
The second one corresponds to the top 5 values based on SUMIFS(TEST!\$F:\$F;TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4) and sorted by B
The third one corresponds to the top 5 values based on SUMIFS(TEST!\$F:\$F;TEST!\$E:\$E;"<>R.";TEST!\$E:\$E;"<>7250";TEST!\$A:\$A;"<="&\$F\$4;TEST!\$D:\$D;">"&\$F\$4;TEST!\$D:\$D;"<"&EDATE(\$F\$4;1)) and sorted by B

However, they do not fit my two scenarios as I would need to sum 1+2 and 1+3

#### Fluff

##### MrExcel MVP, Moderator
Ok, I thought that you were trying to merge all 3 formulae into one, so that it would spill 5 rows & 6 columns, rather than trying to change the formulae.
Those 3 formula are doing completely different things. If they are not what you want, then you will need to rethink your approach.

Replies
3
Views
101
Replies
9
Views
255
Replies
6
Views
137
Replies
11
Views
190
Replies
4
Views
195

Threads
1,141,303
Messages
5,705,590
Members
421,400
Latest member
chakam

### 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

### 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