sumproduct

Copper Contributor

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..

Thank you very much Lorenzo; this is certainly way outside my capabilities with regards use of formulae etc.  I will test and try and understand what the formula is doing when I have more time over the weekend.  At a glance it appears that the formula automatically recognises the date  and category in the CASH RECEPT sheet without specifying eg 1,2,3 for Jan, Feb Mar etc or ="Judges Expenses"... etc.  Once I understand, I will set up the CASH PAYMENT calculations.  Just to clarify, once this is done, I assume the PROFIT/LOSS sheet will automatically update as I enter data in the other sheets.  Much appreciated - Mark

pls find as attached the revised sample.

if you have any questions - pls feel free to ask in this forum.

there are many good men here willing to help.

thanks..

<Just to clarify, once this is done, I assume the PROFIT/LOSS sheet will automatically update as I enter data in the other sheets.>
It will.. if not, attach a sample of your "non-sensitve" file for analysis.
thanks..

you have to adjust the ranges in your Data Validation List (lookup) and Formulas to suit..

Hello Lorenzo Kim

Once again many thanks for your valuable input with this spreadsheet.  I have made some refinements and fully tested with no problems (see attached).  One last question if I may;  On the Profit/Loss spreadsheet, the value in cell C55 may be a positive or a bracketed negative value.  Is it possible to take into account the value in the YTD column (cell O47) and express the result as a positive or bracketed value in cell O49?  ie accommodate the scenarios where C55 and O47 values respectively may both be positive (then add), positive and negative or reverse (then subtract)?  Many thanks Mark

if I get what you mean, the formula in O49 should simply be = C55+O47
because if O47 is negative or bracketed it will be deducted from C55 otherwise ADDED.
opps, that is dumb of me. Many thanks