excel formula optimization help request

[mealan]

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

 

[StephenCrump]

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

	A	B	C	D
1	Criterion 1	Criterion 2	Criterion 3
2
3	a		c
4
5	Data			Range to sum
6	a	a	x	1
7	a	b	c	2
8	a	c	c	3
9	x	d	x	4
10	x			5
11	x	f	x	6
12
13	Result	5

<tbody>
</tbody>

Sheet1
​

 

[mealan]

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)}

​

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)"

 

[mealan]

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

 

[StephenCrump]

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

 

[mealan]

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!

 

[StephenCrump]

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!