Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ---- |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Wildcard * | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |