DSUM an alternative for SUMPRODUCT

[mucah!t]

Hello all

I'using a lot of Sumproduct formulas on my sheet, which slows down the file considerably. I did some searching on the internet and DSUM seems to be a good alternative for Sumproduct. However, its hard to find a good example.
Perhaps one of you know how it can be applied.

This is the formula I'm using at this moment

=SUMPRODUCT(--('R-overzicht'!$L$5:$L$2000=$C4);--(MONTH('R-overzicht'!$M$5:$M$2000)=MONTH(D$2))*(YEAR('R-overzicht'!$M$5:$M$2000)=YEAR(D$2));'R-overzicht'!$J$5:$J$2000)

 

[mikerickson]

DSUM, etc. are a good substitute for SUMPRODUCT, but....

The criteria range has to be a range, it won't accept explicit arrays.

The common useage of putting a SUMPRODUCT formula on a row and dragging down to return the value for the next row will not work with a DSUM formula. Dragging down will either loose the header row of the Criteria Range or expand the Criteria Range, creating an OR.

To return values for a single, user defined scenario, DSUM is fine. Or Perhaps a bit better. (The criteria range is a pretty self explainaitory user interface.)

As a formula as a part of a table of data, SUMPRODUCT is much better.

 

[mucah!t]

I hoped that Dsum would solve all my problems...
Can any of you think of an alternative?

This is the File I'm working on, not all data is shown though

 

[mikerickson]

Its not clear what your problem is. In the OP you say that you are using SUMPRODUCT, but you don't mention what problems this causes.

It looks like that formula is doing something similar to a SUMIFS.

 

[mucah!t]

Like I said, it slows down the file considerably because of the calculations.
In the example file only a couple of them are shown but the file I'm working on has a lot of them [sumproduct-formulas]

 

[Aladin Akyurek]

I hoped that Dsum would solve all my problems...
Can any of you think of an alternative?

This is the File I'm working on, not all data is shown though

Try to adapt from:

http://www.mrexcel.com/forum/showthread.php?t=58539&highlight=table+method

 

[Aladin Akyurek]

I hoped that Dsum would solve all my problems...
Can any of you think of an alternative?

This is the File I'm working on, not all data is shown though

By the way, you invoke an unnecessarily high-cost formula...

D4, just enter, copy across and down:

=SUMPRODUCT(--('1'!$D$5:$D$1081=$C4),--('1'!$E$5:$E$1081-DAY('1'!$E$5:$E$1081)+1=D$2),'1'!$B$5:$B$1081)

Recall that comma must be semi-colon on your system.

 

[AlphaFrog]

If you're willing to use a helper column on sheet 1, you could replace your SUMPRODUCT formulas with SIMIF formulas. SUMIF should be faster.

Excel Workbook
	A	B	C	D	E	F
4	DATUM	BEDRAG	OMSCHRIJVING	POST	MND	Helper
5	23/07/2010	12.00	GFS	test1	Jul	072010test1
6	23/07/2010	234.00	KUYRJ	test1	Jul	072010test1
7	23/07/2010	5.00	REW	test2	Jul	072010test2
8	23/07/2010	76.00	YTREUYT	test3	Jul	072010test3
9	23/07/2010	54.00	EUT	test1	Jul	072010test1
10	23/07/2010	44.00	REYT	test2	Jul	072010test2
11	23/07/2010	56.00	RWY	test2	Jul	072010test2
12	23/07/2010	65.00	T4WYUUT	test3	Jul	072010test3
1

Excel 2002

Cell Formulas
Range	Formula
F5	=TEXT(A5,"mmyyyy")&D5
F6	=TEXT(A6,"mmyyyy")&D6
F7	=TEXT(A7,"mmyyyy")&D7
F8	=TEXT(A8,"mmyyyy")&D8
F9	=TEXT(A9,"mmyyyy")&D9
F10	=TEXT(A10,"mmyyyy")&D10
F11	=TEXT(A11,"mmyyyy")&D11
F12	=TEXT(A12,"mmyyyy")&D12

Excel Workbook
	A	C	D	E	F	G	H	I	J
1
2		Inkomsten	Jan	Feb	Mar	Apr	May	Jun	Jul
3
4		test1	-	-	-	-	-	-	300.00
5		test2	-	-	-	-	-	-	105.00
6		test3	-	-	-	-	-	-	141.00
2

Excel 2002

Cell Formulas
Range	Formula
D4	=SUMIF('1'!$F:$F,TEXT(D$2,"mmyyyy")&$C4,'1'!$B:$B)
D5	=SUMIF('1'!$F:$F,TEXT(D$2,"mmyyyy")&$C5,'1'!$B:$B)
D6	=SUMIF('1'!$F:$F,TEXT(D$2,"mmyyyy")&$C6,'1'!$B:$B)
E4	=SUMIF('1'!$F:$F,TEXT(E$2,"mmyyyy")&$C4,'1'!$B:$B)
E5	=SUMIF('1'!$F:$F,TEXT(E$2,"mmyyyy")&$C5,'1'!$B:$B)
E6	=SUMIF('1'!$F:$F,TEXT(E$2,"mmyyyy")&$C6,'1'!$B:$B)
F4	=SUMIF('1'!$F:$F,TEXT(F$2,"mmyyyy")&$C4,'1'!$B:$B)
F5	=SUMIF('1'!$F:$F,TEXT(F$2,"mmyyyy")&$C5,'1'!$B:$B)
F6	=SUMIF('1'!$F:$F,TEXT(F$2,"mmyyyy")&$C6,'1'!$B:$B)
G4	=SUMIF('1'!$F:$F,TEXT(G$2,"mmyyyy")&$C4,'1'!$B:$B)
G5	=SUMIF('1'!$F:$F,TEXT(G$2,"mmyyyy")&$C5,'1'!$B:$B)
G6	=SUMIF('1'!$F:$F,TEXT(G$2,"mmyyyy")&$C6,'1'!$B:$B)
H4	=SUMIF('1'!$F:$F,TEXT(H$2,"mmyyyy")&$C4,'1'!$B:$B)
H5	=SUMIF('1'!$F:$F,TEXT(H$2,"mmyyyy")&$C5,'1'!$B:$B)
H6	=SUMIF('1'!$F:$F,TEXT(H$2,"mmyyyy")&$C6,'1'!$B:$B)
I4	=SUMIF('1'!$F:$F,TEXT(I$2,"mmyyyy")&$C4,'1'!$B:$B)
I5	=SUMIF('1'!$F:$F,TEXT(I$2,"mmyyyy")&$C5,'1'!$B:$B)
I6	=SUMIF('1'!$F:$F,TEXT(I$2,"mmyyyy")&$C6,'1'!$B:$B)
J4	=SUMIF('1'!$F:$F,TEXT(J$2,"mmyyyy")&$C4,'1'!$B:$B)
J5	=SUMIF('1'!$F:$F,TEXT(J$2,"mmyyyy")&$C5,'1'!$B:$B)
J6	=SUMIF('1'!$F:$F,TEXT(J$2,"mmyyyy")&$C6,'1'!$B:$B)

 

[AlphaFrog]

You could also do this with a Pivot Table. You would still need to make helper columns on sheet 1

Excel Workbook
	A	B	C	D	E	F	G	H
4	DATUM	BEDRAG	OMSCHRIJVING	POST	MND	Month	Year	Mo & Yr
5	23/07/2010	12.00	GFS	test1	Jul	Jul	2010	Jul 2010
6	23/07/2010	234.00	KUYRJ	test1	Jul	Jul	2010	Jul 2010
7	23/07/2010	5.00	REW	test2	Jul	Jul	2010	Jul 2010
8	23/07/2010	76.00	YTREUYT	test3	Jul	Jul	2010	Jul 2010
9	23/07/2010	54.00	EUT	test1	Jul	Jul	2010	Jul 2010
10	23/07/2010	44.00	REYT	test2	Jul	Jul	2010	Jul 2010
11	23/07/2010	56.00	RWY	test2	Jul	Jul	2010	Jul 2010
12	23/07/2010	65.00	T4WYUUT	test3	Jul	Jul	2010	Jul 2010
1

Excel 2002

Cell Formulas
Range	Formula
F5	=TEXT(A5,"mmm")
G5	=YEAR(A5)
H5	=TEXT(A5,"mmm yyyy")

Then set up your pivot table so that...
Page Area
Month
Year

Row area
Post

Column area
Month & Year

Data area
Sum of BEDRAG

 

[mucah!t]

Thanks AlphaFrog, your solution did speed up the calculations.