Debugging SUMPRODUCT
 

Hi all,

After some good advice on here I'm using the SUMPRODUCT function to
analyse personal expenditure data. It's scanning data on another
worksheet to see if each row of that worksheet falls within the date
range for the column and the catagory for the row. If it does the
value gets added in. I've got a fairly lengthy formula with 3D
references.

=SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J
$4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J
$4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F
$3202))

It isn't quite producing the figures I would expect. What I want to be
able to do is to see what cells on the other worksheet ("Money data")
are going into the sum. Is there a way to do this?

Cheers!

Martin

Debugging SUMPRODUCT
 

Why are you using 0+ throughout. I think I'd change it to this

=SUMPRODUCT(--('Money Data'!$B$1:$B$3202<DATE(YEAR(J$4),
MONTH(J$4)+1,1)),--('Money
Data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J$4)+1,1)),--('Money
Data'!$G$1:$G$3202=$A6),('Money Data'!$F$1:$F$3202))

--
HTH,
Barb Reinhardt




" wrote:

Hi all,

After some good advice on here I'm using the SUMPRODUCT function to
analyse personal expenditure data. It's scanning data on another
worksheet to see if each row of that worksheet falls within the date
range for the column and the catagory for the row. If it does the
value gets added in. I've got a fairly lengthy formula with 3D
references.

=SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J
$4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J
$4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F
$3202))

It isn't quite producing the figures I would expect. What I want to be
able to do is to see what cells on the other worksheet ("Money data")
are going into the sum. Is there a way to do this?

Cheers!

Martin

Debugging SUMPRODUCT
 

You can use conditional formatting to highlight the cells that are being
used to produce the result.

On the Money sheet enter these formulas in some cells, say, J1:J3

J1: =DATE(YEAR(Sheet_name!J$4), MONTH(Sheet_name!J$4)+1,1)
J2: =DATE(YEAR(Sheet_name!J$4)-1, MONTH(Sheet_name!J$4)+1,1)
J3: =Sheet_name!A6

Replace Sheet_name with the actual sheet name!

Now, select the range B1:G3202
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=AND($B1<$J$1,$B1=$J$2,$G1=$J$3)

Click the Format button
Click the Patterns tab
Select a nice color
OK out

Rows that are highlighted are being used to produce the result of your
formula.


--
Biff
Microsoft Excel MVP


wrote in message
...
Hi all,

After some good advice on here I'm using the SUMPRODUCT function to
analyse personal expenditure data. It's scanning data on another
worksheet to see if each row of that worksheet falls within the date
range for the column and the catagory for the row. If it does the
value gets added in. I've got a fairly lengthy formula with 3D
references.

=SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J
$4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J
$4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F
$3202))

It isn't quite producing the figures I would expect. What I want to be
able to do is to see what cells on the other worksheet ("Money data")
are going into the sum. Is there a way to do this?

Cheers!

Martin

Debugging SUMPRODUCT
 

Whilst I don't like 0+, it just looks ugly, it is just as valid as -- Barb.

--
__________________________________
HTH

Bob

"Barb Reinhardt" wrote in message
...
Why are you using 0+ throughout. I think I'd change it to this

=SUMPRODUCT(--('Money Data'!$B$1:$B$3202<DATE(YEAR(J$4),
MONTH(J$4)+1,1)),--('Money
Data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J$4)+1,1)),--('Money
Data'!$G$1:$G$3202=$A6),('Money Data'!$F$1:$F$3202))

--
HTH,
Barb Reinhardt




" wrote:

Hi all,

After some good advice on here I'm using the SUMPRODUCT function to
analyse personal expenditure data. It's scanning data on another
worksheet to see if each row of that worksheet falls within the date
range for the column and the catagory for the row. If it does the
value gets added in. I've got a fairly lengthy formula with 3D
references.

=SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J
$4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J
$4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F
$3202))

It isn't quite producing the figures I would expect. What I want to be
able to do is to see what cells on the other worksheet ("Money data")
are going into the sum. Is there a way to do this?

Cheers!

Martin