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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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

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

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
76
Replies
9
Views
203
Replies
6
Views
120
Replies
11
Views
143
Replies
4
Views
180

1,136,764
Messages
5,677,612
Members
419,707
Latest member
Anna vib

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