excel formula optimization help request

M

mealan

New Member
Joined
Oct 14, 2014
Messages
4
Hello all, windows 7sp1 64 bit (8gb ram, i7 dual core proc) with excel 64 bit (64 bit is needed for larger calculations).

I need some help with the below formula and how to make it smaller or faster using loops. I am good with powershell, just not excel :)

Thank you for your time and help with this!

=IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISBLANK($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUM('Cash Model'!BG$122:BG263),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISBLANK($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIF('Cash Model'!$F$122:$F263,$D$6,'Cash Model'!BG$122:BG263),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIF('Cash Model'!$E$122:$E263,$D$5,'Cash Model'!BG$122:BG263),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISTEXT($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIF('Cash Model'!$D$122:$D263,$D$4,'Cash Model'!BG$122:BG263),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISTEXT($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISBLANK($D$2),ISBLANK($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISBLANK($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIF('Cash Model'!$C$122:$C263,$D$3,'Cash Model'!BG$122:BG263),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISBLANK($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISTEXT($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISTEXT($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISBLANK($D$2),ISTEXT($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISTEXT($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISTEXT($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISBLANK($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISTEXT($D$3),ISBLANK($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISTEXT($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISTEXT($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$D$122:$D263,$D$4),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISTEXT($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISBLANK($D$4),ISNUMBER($D$5),ISBLANK($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$E$122:$E263,$D$5),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISBLANK($D$4),ISBLANK($D$5),ISTEXT($D$6)),SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$F$122:$F263,$D$6),
IF(AND(ISTEXT($D$2),ISBLANK($D$3),ISBLANK($D$4),ISBLANK($D$5),ISBLANK($D$6)),SUMIF('Cash Model'!$B$122:$B263,$D$2,'Cash Model'!BG$122:BG263),
SUMIFS('Cash Model'!BG$122:BG263,'Cash Model'!$B$122:$B263,$D$2,'Cash Model'!$C$122:$C263,$D$3,'Cash Model'!$D$122:$D263,$D$4,'Cash Model'!$E$122:$E263,$D$5,'Cash Model'!$F$122:$F263,$D$6))))))))))))))))))))))))))))))))
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Welcome to MrExcel!

Based on a quick look at your formula, it looks like you're trying to do something like this?

Array-entered formula in B13: {=SUM(IF(ISBLANK(A3),TRUE,A6:A11=A3)*IF(ISBLANK(B3),TRUE,B6:B11=B3)*IF(ISBLANK(C3),TRUE,C6:C11=C3)*D6:D11)}

Excel 2010
ABCD
Criterion 1Criterion 2Criterion 3
ac
DataRange to sum
aax
abc
acc
xdx
x
xfx
Result

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1
​
 
Last edited:
Upvote 0
StephenCrump said:
Welcome to MrExcel!

Based on a quick look at your formula, it looks like you're trying to do something like this?

Array-entered formula in B13: {=SUM(IF(ISBLANK(A3),TRUE,A6:A11=A3)*IF(ISBLANK(B3),TRUE,B6:B11=B3)*IF(ISBLANK(C3),TRUE,C6:C11=C3)*D6:D11)}

​
Click to expand...

close to that I think yes, however when I enter that formula for the spreadsheet you provided I get a #value error.

is there something special with the { before the = and at the end? when I enter those it just reads it as text and not a formula

Thank you for your help!

ex for b13 I tried "=SUM(IF(ISBLANK(A3),TRUE,A6:A11=A3)*IF(ISBLANK(B3),TRUE,B6:B11=B3)*IF(ISBLANK(C3),TRUE,C6:C11=C3)*D6:D11)"
 
Upvote 0
sorry, didn't google research very well before. control shift enter got me far, and seems to be good! could you by chance break it down the calculations for me? thank you so much for everything, this has been a huge help

need to look up more into array base formulas :)
 
Upvote 0
It looks like you are trying to sum this range: 'Cash Model'!BG$122:BG263, where you have filtered on five criteria specified in D2:D6. However, if a filter is blank, all values are summed.

In my example, I've simplified to three criteria:

Column A = "a"
Column B: no filter
Column C = "c"

The array-entered formula: =SUM(IF(ISBLANK(A3),TRUE,A6:A11=A3)*IF(ISBLANK(B3),TRUE,B6:B11=B3)*IF(ISBLANK(C3),TRUE,C6:C11=C3)*D6:D11)

calculates as:

=SUM((A6:A11="a") * TRUE * (C6:C11="c"))

=SUM({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}*TRUE*{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6})

=SUM({0;1;1;0;0;0}*{1;2;3;4;5;6})

=SUM({0;2;3;0;0;0})

= 5
 
Upvote 0
Holy cow, you're a genius!!

this will replace around 500+ cells for now and looks like it'll speed it up a lot too, using the array for a compare then only adding the numbers are the end is a very efficient way of doing this. Thank you very much for everything! Is there a way to donate to a user or to the forum for server cost etc? This literally will save a lot of work, thank you very much!
 
Upvote 0
Thanks for the feedback.

MrExcel does provide consulting services, but all suggestions/advice on this forum are given freely by anyone who wishes to contribute.

If it's good advice and will save you time, that's great for you, and I'm pleased too!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
132
davidcox9
D
Nlhicks
  • Solved
Need Conditional Formatting Formula to Change the Highlight to Red if today is more than 5 years past sticker date and yellow if it is 4 years past
2
Replies
10
Views
218
etaf
etaf
O
  • Solved
Put amounts for adjacent items based on matching column between two sheets
Replies
3
Views
60
dreid1011
dreid1011
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
Z
  • Solved
Many tabs to 1
Replies
7
Views
275
Michael M
Michael M

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top