Sumproduct Wildcard
 

=SUMPRODUCT(--(C6:C20=38473)*(C6:C20<=38503),--(D6:D20="food"),F6:F20)

Expert,
Above is the formula i am using, however it will only return a value when
only 'food' is contained in the cell. With this formula I have built a simple
check-book for my own personal finance. what can i do to this formula to look
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?

Thank you,
RB

One way ..

Try replacing: --(D6:D20="food")

with: --ISNUMBER(SEARCH("food",D6:D20)

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"RB" wrote in message
...
=SUMPRODUCT(--(C6:C20=38473)*(C6:C20<=38503),--(D6:D20="food"),F6:F20)

Expert,
Above is the formula i am using, however it will only return a value when
only 'food' is contained in the cell. With this formula I have built a
simple
check-book for my own personal finance. what can i do to this formula to
look
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?

Thank you,
RB

One way:

=SUMPRODUCT(--(C6:C20=38473),--(C6:C20<=38503),
--ISNUMBER(FIND("food",D6:D20)), F6:F20)

In article ,
"RB" wrote:

=SUMPRODUCT(--(C6:C20=38473)*(C6:C20<=38503),--(D6:D20="food"),F6:F20)

Expert,
Above is the formula i am using, however it will only return a value when
only 'food' is contained in the cell. With this formula I have built a simple
check-book for my own personal finance. what can i do to this formula to look
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?

Thank you,
RB

Thought the OP wanted it case-insensitive <g, as per line
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks alot.... i tried using Find as sugested by JE ( and thank you as
well!),but SEARCH seemed to work great!!!!

"Max" wrote:

One way ..

Try replacing: --(D6:D20="food")

with: --ISNUMBER(SEARCH("food",D6:D20)

in the formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"RB" wrote in message
...
=SUMPRODUCT(--(C6:C20=38473)*(C6:C20<=38503),--(D6:D20="food"),F6:F20)

Expert,
Above is the formula i am using, however it will only return a value when
only 'food' is contained in the cell. With this formula I have built a
simple
check-book for my own personal finance. what can i do to this formula to
look
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?

Thank you,
RB

In article ,
"Max" wrote:

Thought the OP wanted it case-insensitive <g, as per line
for 'food' in cells such as ' Giant Food Store' of 'Food Lion'?

True - should use SEARCH instead of FIND. Thanks for the correction.

You're welcome !
SEARCH is case-insensitive, while FIND is
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"RB" wrote in message
...
Thanks alot.... i tried using Find as sugested by JE ( and thank you as
well!),but SEARCH seemed to work great!!!!