Sumproduct with a date range and contains query

[confusedjcd]

Hi all,

Okay, may be a easy solution but this has been driving me nuts as a novice.

I have a three column bank statement which follows:

Column a = date of transaction
column b = description of transaction (normally a line of text)
column c = amount

Now on a separate worksheet in the same document i want to be able to run a formula that returns a value for the amount spent in a certain date range on particular types of transaction.

For example:

- Value returned for wages spent in march
- Value returned for atm withdrawls in march.

For example I may have transactions described as "BILL PAYMENT TO J DENNEY WAGE REFERENCE J Denney MANDATE NO 0013"on the statement. So as well as the date range query I would want to run the query with 'contains' "denney wage". Now this is where i get stuck, as i have no idea how to run a contains formula, rather than equals. Especially one that allows me to search for a part string of text rather than exact.

Any help appreciated.

Ta

James

[JeanRage]

Hi,

You can try the Search() function ...
=Search("denney wage",A1,1)

HTH

[confusedjcd]

ta for that.

How would i incorpraote that into a formula that also searchs for a particular date range?

Cheers

[JeanRage]

Hi,

You do need to test it ...

Please Login or Register  to view this content.

HTH

[confusedjcd]

okay i'm a real novice and have tried:

IF(ISNUMBER(SEARCH(statement!"denney wage",b2:b16000),SUMPRODUCT(Statement!a2:a16000>=05/04/09),(Statement!a2:a16000<=30/04/09),--(Statement!C2:C16000)))

Any ideas what needs correcting?

b2:b16000 being the description column
a2:a16000 being the date column
c2:c16000 being the amount column

Statement is the main worksheet and summary is the worksheet where i want the results.

Can you believe I'm doing this on a saturday night ;-)

Cheers

[daddylonglegs]

Try like this

=SUMPRODUCT(ISNUMBER(SEARCH("denney wage",Statement!B2:B16000))*(Statement!A2:A16000>="05/04/09"+0)*(Statement!A2:A16000<="30/04/09"+0),Statement!C2:C16000)

[Ron Coderre]

With
On Sheet1...
A2:A50 contains dates
B2:B50 contains descriptions
C2:C50 contains amounts

and...
On Sheet2
A1: (a date.....eg 03-MAR-2009)
A2: (a key phrase to find.....eg Charlie Wage)

This formula returns the total amounts from Sheet1
where the dates are in the same month as the date A1
and the key phrase is found in the description

Please Login or Register  to view this content.

In the above example, the total is returned
for all dates in MAR 2009 where the description contains "Charlie wage".

Is that something you can work with?

[confusedjcd]

Blooming brilliant...all working! If only i posted this ealier i wouldn't of spent all day scratching my head. Thanks!

Cheers

James

[Minci33]

sorry to interrupt , may i know using this formula for the same date ?

[FDibbins]

sorry to interrupt , may i know using this formula for the same date ?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread