Wild Cards With Arrays
 

I have a spreadsheet with the following columns....Date, Account, Description
and Amount.
I'd like to create a lookup formula whereby all data in the Amount column
will be added together if they are deposited in a specific account and the
description has the word "Discover" in it.
I am semi-familiar with array formulas, but the problem I have is that the
Description column has several words in it, and I want the formula to only
pickup rows that have the word Discover in the description field.
How can I get this to work?

With the Description in Column C, and the Amounts in Column D, try this:

=SUMIF(C1:C100,"*discover*",D1:D100)

If you would like to reference a particular cell, so that you can change the
description of the account you're looking to sum, try thiis, with the acct.
description entered in E1:

=SUMIF(C1:C100,"*"&E1&"*",D1:D100)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Brian" wrote in message
...
I have a spreadsheet with the following columns....Date, Account,
Description
and Amount.
I'd like to create a lookup formula whereby all data in the Amount column
will be added together if they are deposited in a specific account and the
description has the word "Discover" in it.
I am semi-familiar with array formulas, but the problem I have is that the
Description column has several words in it, and I want the formula to only
pickup rows that have the word Discover in the description field.
How can I get this to work?

Try...

=SUMPRODUCT(--(AccountRange=AcctNum),--(ISNUMBER(SEARCH("discover",Descri
ptionRange))),AmountRange)

Hope this helps!

In article ,
"Brian" wrote:

I have a spreadsheet with the following columns....Date, Account, Description
and Amount.
I'd like to create a lookup formula whereby all data in the Amount column
will be added together if they are deposited in a specific account and the
description has the word "Discover" in it.
I am semi-familiar with array formulas, but the problem I have is that the
Description column has several words in it, and I want the formula to only
pickup rows that have the word Discover in the description field.
How can I get this to work?

Thanks...but the problem is that I need only to add data that meets specific
criteria in column B, and has "Discover" in Column C.
The formula you gave me will add up all discover totals in all accounts...I
only need Discover totals for specific accounts.
Brian

"RagDyer" wrote:

With the Description in Column C, and the Amounts in Column D, try this:

=SUMIF(C1:C100,"*discover*",D1:D100)

If you would like to reference a particular cell, so that you can change the
description of the account you're looking to sum, try thiis, with the acct.
description entered in E1:

=SUMIF(C1:C100,"*"&E1&"*",D1:D100)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Brian" wrote in message
...
I have a spreadsheet with the following columns....Date, Account,
Description
and Amount.
I'd like to create a lookup formula whereby all data in the Amount column
will be added together if they are deposited in a specific account and the
description has the word "Discover" in it.
I am semi-familiar with array formulas, but the problem I have is that the
Description column has several words in it, and I want the formula to only
pickup rows that have the word Discover in the description field.
How can I get this to work?

Then Domenic has your solution.
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Brian" wrote in message
...
Thanks...but the problem is that I need only to add data that meets
specific
criteria in column B, and has "Discover" in Column C.
The formula you gave me will add up all discover totals in all
accounts...I
only need Discover totals for specific accounts.
Brian

"RagDyer" wrote:

With the Description in Column C, and the Amounts in Column D, try this:

=SUMIF(C1:C100,"*discover*",D1:D100)

If you would like to reference a particular cell, so that you can change
the
description of the account you're looking to sum, try thiis, with the
acct.
description entered in E1:

=SUMIF(C1:C100,"*"&E1&"*",D1:D100)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Brian" wrote in message
...
I have a spreadsheet with the following columns....Date, Account,
Description
and Amount.
I'd like to create a lookup formula whereby all data in the Amount
column
will be added together if they are deposited in a specific account and
the
description has the word "Discover" in it.
I am semi-familiar with array formulas, but the problem I have is that
the
Description column has several words in it, and I want the formula to
only
pickup rows that have the word Discover in the description field.
How can I get this to work?