Formula with multiple criterion

I need to come up with a formula in excel with 2 criterion. Here's a simplified description of the worksheet:

Column A - Date
Column B - Transaction Code
Column C - Amount

What I'm looking for is the sum of all transactions in a certain date where the transaction code starts with a specific number.

Any ideas?

Thank you VERY much in advance to anyone who can help.

Are the transaction codes numeric or text?

Are you familiar with SUMPRODUCT() function?

Originally Posted by Cutter

Are the transaction codes numeric or text?

Are you familiar with SUMPRODUCT() function?

The transaction codes are numeric.

I've dabbled in SUMPRODUCT function the past few days but can't figure it out. I've come up with these formulas to work separately, but can't seem to put the two together. Here's what I have:

=SUMIF(B:B,"5*",C:C) to find the appropriate transaction codes that start with 5.

=SUMPRODUCT(($A$2:$A$1000>=40544)*($C$2:$C$1000<=40574)*($C$2:C$1000)) for transactions in rows A1-A1000 that are from 1/1/11 to 1/31/11.

Thanks.

Originally Posted by bastage

I need to come up with a formula in excel with 2 criterion. Here's a simplified description of the worksheet:

Column A - Date
Column B - Transaction Code
Column C - Amount

What I'm looking for is the sum of all transactions in a certain date where the transaction code starts with a specific number.

Any ideas?

Thank you VERY much in advance to anyone who can help.

Try putting criteria1 as date in D1 and criteria2 as TR_Code in D2
type this formula in e4.

=sumproduct(--(a:a=$D$1)*--(left(b:b,5)=$d$2*(c:c))

Notice......(left(b:b,5) I am assuming you have a 5 character long code.

Give me your feed back.

Originally Posted by ashmakda

Try putting criteria1 as date in D1 and criteria2 as TR_Code in D2
type this formula in e4.

=sumproduct(--(a:a=$D$1)*--(left(b:b,5)=$d$2*(c:c))

Notice......(left(b:b,5) I am assuming you have a 5 character long code.

Give me your feed back.

It comes back as 0.

The transaction code is in text format.

Sorry, I saw that you went offline after I posted so I shut down for the night and watched The Dirty Dozen (again)

Anyway

Try this

=SUMPRODUCT((A1:A31>=40544)*(A1:A31<=40574)*(LEFT(B1:B31,1)="5")*(C1:C31))

In this case it will return amounts within the range with trans code beginning with "5"

If you replace the date values and the start 'number' with cell references make sure you format the cell containing the start 'number' as TEXT