• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct - with dates and separate spreadsheets

S

Sparkgirl

New Member
Until Sunday I was a Sumproduct virgin and now I am trying to crack the world or rather my accounting system!


In one spreadsheet (called Bus Exp) I record all of my business expenses including mileage and other information including the date (column B) and the amount (Column d).


I created the following formula which by changing the arguments will allow me to calculate specfic expenses between specfic dates - it works perfectly.

=SUMPRODUCT(($B$3:$B$1001>=DATE(2010,4,4))*($B$3:$B$1001<=DATE(2010,5,2))*($A$3:$A$1001="Mileage"),--$D$3:$D$1001)


The final step I want to use the formula on a different 'summary' spreadsheet within the same workbook, which will enable me to monitor my business profitability on a more frequent basis. Can anyone provide me with an amended formula including sheet name e.g. Bus Exp!


I've had several attempts and failed each time!


HEEEEEEELP


Thanks

Sparkgirl
 
Hi, Sparkgirl!

Supposing you want to replace column D from 'Bus Exp' worksheet with column F from 'Other figures' worksheet, and mantain the columns A & B from the first one, you may try this:

=SUMPRODUCT(('Bus Exp'!$B$3:$B$1001>=DATE(2010,4,4))*('Bus Exp'!$B$3:$B$1001<=DATE(2010,5,2))*('Bus Exp'!$A$3:$A$1001="Mileage"),--$F$3:$F$1001)

and place this formula in 'Other figures' worksheet.

Briefly, the worksheet qualification for a range has the format XXX! where XXX is the worksheet name (single quoted if has spaces).

Regards!
 
You must log in or register to reply here.
Back
Top