# 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### 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
101
Replies
9
Views
255
Replies
6
Views
137
Replies
11
Views
190
Replies
4
Views
195

1,141,300
Messages
5,705,577
Members
421,399
Latest member
hjweiss00

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