Sum Between Conditions

  • Not quite sure how to start this, but i'll give it a go anyway!


    I'm (and you are too!) still helping to streamline a charity's spreadsheet.
    There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit).


    In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors.


    We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.


    The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.


    A long winded challenge to explain - please ask if anything is unclear!!


    Any help here would be highly appreciated by all of us at the Fundraising Deptartment

  • Re: Searching columns for entries & performing sums to the relevent row(s)


    Code
    =SUMPRODUCT(('List of DD Donors'!$D$2:$D$21)*('List of DD Donors'!$E$2:$E$21='DD Tally'!A20)*IF(DATEDIF(('List of DD Donors'!$F$2:$F$21)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!$F$2:$F$21)-1,DATE(2006,3,31),"m")))


    Seems to do the trick!


    could u maybe help me a little? some questions follow:


    can the

    Code
    $D$2:$D$21

    be changed to just

    Code
    D:D

    as the spreadsheet is likely to grow?


    i presume that it refers to A20 so it knows what to search for? (in this case "Monthly")


    how can i change the date when a new financial year starts?


    and is it easy to set up similar things for the Quarterly, Semi-Annually and Annually? (i presume i could just divide the calculated monthly sum by 4, 6 and 12 respectively...)

  • Re: Searching columns for entries & performing sums to the relevent row(s)


    Just had a thought that maybe the SUMPRODUCT could refer to a cell that has the month and year of the end of the financial year in it... would this be easy to implement?


    The work you've done so far is great!


    Just tried the D:D idea... doesnt work, so as a short term solution i added $D2:$D5000.


    Also, my idea of dividing by 4, 6 and 12 only works for the Semi-annually one! prob coz there's only one of them.

  • Re: Searching columns for entries & performing sums to the relevent row(s)


    the D:D shud work. provided each of the arrays are of the same dimension


    the date is defined in the third array of the formula and although here I have hard coded the same you can link it to a cell for flexibility


    quarters, semi annual periods can similarly be incorporated although here you will have to change to "whole" number of periods..for eg if the number of months from 1st month to 31st March is six then you would have two full quarter of donation and not 1.5 quarters


    hope that helps


    pangolin

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!