Forum Discussion
sumproduct
I am a novice with Excel so seeking advice on the use of SUMPRODUCT. From a summary table on a separate worksheet, I am seeking to refer to a table with data in columns A,B and F on worksheet CASH RECEIPTS (Column A - date range dd/mm/yy, Column B - categories and Column F - $ amounts. For a particular month (in the formula, January as MONTH =1) I wish to sum the amounts for a particular category (in this example JUDGES EXPENSES. This is the formula I have compiled:
= SUMPRODUCT((MONTH (CASH RECEIPTS!$A$4:$A$6) =1)*((CASH RECEIPTS!$B$4:$B$6)='Judges Expenses')*(CASH RECEIPTS!$F$4:$F$6))
However, the following error is generated which I do not understand or solves the problem if acted upon. The message is:
Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:
• you type: =1+1, cell shows: 2
To get around this, type an apostrophe ( ' ) first:
• you type: '=1+1, cell shows: =1+1
10 Replies
pls see attached sample..
Hi Lorenzo
Thank you very much for your efforts on this, much appreciated. In retrospect, I should have attached an example of the spreadsheet. As the CASH PAYMENT and CASH RECEIPT sheets are completed, I was hoping that the PROFIT/LOSS sheet would automatically update with the total $amounts according to month and category. Note that the category column is populated with the aid of a drop down menu. Not sure if that complicates the issue. If you could suggest a formula for one of the cells on the PROFIT/LOSS sheet then that would be a great help. Regards Mark
pls find the sample attached.
hope you can make something out of this.
thanks..
