Optimize Formula

Leticia

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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!
 

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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
It's possible, but I would not recommend it.
 

Leticia

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

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