With help from this forum I've used SumProduct many times but I've been going round in circles for the past couple of days with a formula which won't stop counting results instead of summing them, I'm sure I'm missing something obvious so any help would be greatly appreciated.
I'm using Excel 2003 and Windows XP Professional.
I have a report which can be hundreds of lines long which shows product deliveries by product code and method of despatch (MOD). As this is a stock item deliveries are shown as negatives and credits as positive numbers. I want to sum the credits (i.e. positive numbers) by product code and MOD.
Here's my sample data:
<tbody>
</tbody>
The report will be of variable length so I'll be using INDIRECT references in the final formula but for this post I've used fixed cell addresses.
So my typical formula is =SUMPRODUCT(($D$2:$D$10=$H15)*($F$2:$F$10=I$6)*($E$2:$E$10>0)). This is duplicated across a table with Product Codes down the left side and MOD's along the top and the formula replicated accordingly.
If I remove the >0 in the last part of the formula it correctly sums each product by MOD so for example BPPCHP by Rail would be -319 (the sum of rows 6, 8 & 9). But when I add the >0 to sum the positive values, instead of giving the sum i.e. 1300 in this example it returns 1, the count.
I've tried using * instead of commas without success and using INDIRECT references returns the count as well. I thought it might be to do with the original report being loaded from a .csv file but I typed the above example with the same result.
Can anybody can see where I'm going wrong?
Thanks for looking.
I'm using Excel 2003 and Windows XP Professional.
I have a report which can be hundreds of lines long which shows product deliveries by product code and method of despatch (MOD). As this is a stock item deliveries are shown as negatives and credits as positive numbers. I want to sum the credits (i.e. positive numbers) by product code and MOD.
Here's my sample data:
| D | E | F | |
| 1 | Products | Tonnes | MOD |
| 2 | BPRHHP | 20 | Road |
| 3 | BPPCHP | 40 | Road |
| 4 | BPPCHP | -35 | |
| 5 | BPBAGHP | -700 | Rail |
| 6 | BPPCHP | -319 | Rail |
| 7 | BPPCHP | 26 | Collect |
| 8 | BPPCHP | 1300 | Rail |
| 9 | BPPCHP | -1300 | Rail |
| 10 | BPRHHP | -1800 | Rail |
| 11 |
<tbody>
</tbody>
The report will be of variable length so I'll be using INDIRECT references in the final formula but for this post I've used fixed cell addresses.
So my typical formula is =SUMPRODUCT(($D$2:$D$10=$H15)*($F$2:$F$10=I$6)*($E$2:$E$10>0)). This is duplicated across a table with Product Codes down the left side and MOD's along the top and the formula replicated accordingly.
If I remove the >0 in the last part of the formula it correctly sums each product by MOD so for example BPPCHP by Rail would be -319 (the sum of rows 6, 8 & 9). But when I add the >0 to sum the positive values, instead of giving the sum i.e. 1300 in this example it returns 1, the count.
I've tried using * instead of commas without success and using INDIRECT references returns the count as well. I thought it might be to do with the original report being loaded from a .csv file but I typed the above example with the same result.
Can anybody can see where I'm going wrong?
Thanks for looking.
