Converting array formula sum on sumproduct

[Konrado]

Welcome
I just wondering if it is possible to change array formula as below on normal formula sumproduct.

=SUM(($A$3:$A$11=$F$17)*IF(G17<>"All";$B$3:$B$11=G17;1)*($C$1:$Z$1=$H$17)*CHOOSE($K$17;$C$2:$Z$2=$I$17;$C$2:$Z$2<=$I$17;$C$2:$Z$2>$I$17)*($C$3:$Z$11)) (J19 - 5914 value as below)

Above formula refers to set of data. Using some criteria I can count value. Text start is in A1. Thanks in advance!

Start		Act	Act	Act	Act	Act	Act	Act	Act	Act	Act	Act	Act	Out	Out	Out	Out	Out	Out	Out	Out	Out	Out	Out	Out
Dep	CC	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
A	1200	292	993	716	260	995	997	77	726	960	242	496	840	86	316	794	143	425	985	201	335	329	1	345	135
A	1200	330	727	59	760	53	45	920	399	208	429	394	342	435	567	578	688	154	49	662	240	28	264	352	486
A	1000	746	668	937	924	489	79	538	990	75	707	321	614	609	593	207	380	196	398	513	58	233	71	980	796
A	1000	19	858	895	536	624	232	200	225	946	110	640	495	703	48	786	84	437	660	320	155	516	392	156	33
B	1200	651	534	956	170	597	406	578	908	745	821	363	828	415	653	876	547	800	560	227	123	75	972	792	999
B	1000	284	341	898	239	422	599	446	243	22	326	741	596	966	828	523	581	742	761	422	332	763	710	755	147
B	1000	217	584	381	346	2	229	99	615	359	161	245	924	944	358	3	122	511	840	735	512	632	605	494	286
C	1200	973	697	799	898	414	233	232	574	333	677	908	814	708	386	107	394	4	872	897	439	920	700	991	125
C	1000	213	320	370	512	364	43	823	684	472	772	746	516	413	353	149	640	306	476	271	271	138	16	308	173
									Monthly	1
									YTD	2
									YTG	3
					Dep	CC	Sort of #	Curr Mon	M/YTD/YTG
					A	1000	Out	4	YTG	3
								Total	5 914

<colgroup><col span="9"><col><col span="16"></colgroup><tbody>
</tbody>

 

[FormR]

You could try this, note: all this saves is CTRL+SHIFT+ENTER, it is still array processing in nature.

=SUMPRODUCT(C3:Z11*(C1:Z1=H17)*(A3:A11=F17)*(((C2:Z2=I17)*(K17=1))+((C2:Z2<=I17)*(K17=2))+((C2:Z2>I17)*(K17=3)))*((B3:B11=G17)+(G17="All")))

If you are looking for an improvement in performance, your array formula can probably be written in a better/more efficient way.

 

[Konrado]

Hi FormR

Thank you for posting, your answer is really helpful. Do you have any clue how to make my formula faster?

 

[FormR]

Do you have any clue how to make my formula faster?

Hi, I think your best bet would be to try using a helper column, for example:

In cell AA3 copied down:

=IF(AND(A3=$F$17;OR(B3=$G$17;$G$17="All"));SUMIFS(C3:Z3;$C$1:$Z$1;$H$17;$C$2:$Z$2;INDEX({"=";"<=";">"};$K$17)&$I$17);0)

And then in cell J19:

=SUM(AA:AA)

 

[Konrado]

Thank you again, great solution!