G
Guest
I have a budget worksheet in which I keep all my spending records, eg on
Sheet 1:
A B C D
Date Amount Category Month
1 1/1/5 20.00 Dining Jan
2 2/1/5 25.00 Telephone Jan
3 3/1/5 30.00 Petrol Jan
4 4/1/5 15.00 Dining Jan
I am using sumproduct to keep a running total on Sheet 2, of my spend
against each category, for example to return the total for "Dining":
=SUMPRODUCT((Sheet1!c2:c4=Sheet2!b2)*(Sheet1!d2:d4=Sheet2!a2)
*(Sheet1!b2:b4)
A B C D
Jan Feb Mar
1 Dining 35.00 0.00 0.00
2 Telephone 25.00 0.00 0.00
3 Petrol 30.00 0.00 0.00
The problem is that as soon as the ranges checked by SUMPRODUCT contain a
blank cell, it returns #N/A. I would obviously rather have SUMPRODUCT refer
to the whole column, e.g. Sheet1!c:c: so I can carry on entering new data in
Sheet1 without having to amend the ranges in the formula every time. Can
someone assist please?
Rgds
Dave
Sheet 1:
A B C D
Date Amount Category Month
1 1/1/5 20.00 Dining Jan
2 2/1/5 25.00 Telephone Jan
3 3/1/5 30.00 Petrol Jan
4 4/1/5 15.00 Dining Jan
I am using sumproduct to keep a running total on Sheet 2, of my spend
against each category, for example to return the total for "Dining":
=SUMPRODUCT((Sheet1!c2:c4=Sheet2!b2)*(Sheet1!d2:d4=Sheet2!a2)
*(Sheet1!b2:b4)
A B C D
Jan Feb Mar
1 Dining 35.00 0.00 0.00
2 Telephone 25.00 0.00 0.00
3 Petrol 30.00 0.00 0.00
The problem is that as soon as the ranges checked by SUMPRODUCT contain a
blank cell, it returns #N/A. I would obviously rather have SUMPRODUCT refer
to the whole column, e.g. Sheet1!c:c: so I can carry on entering new data in
Sheet1 without having to amend the ranges in the formula every time. Can
someone assist please?
Rgds
Dave