# 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 &amp; performing sums to the relevent row(s)

looks to be a problem for SUMPRODUCT / SUMIF functions

I think you upload an example file with the results expected...I am sure that will get you a prompt and positive response

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

not a problem, see attached

## Files

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

see the attached file...from what I understood I have calculated the amount to April 06 figures from total number of months from April 05 or the first month of joining whichever is later

hope that helps

pangolin

## Files

• Re: Searching columns for entries &amp; 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 &amp; 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 &amp; 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!