Another multiple condition question
Posted by Kathi on May 11, 2001 7:05 AM
This should be my last question. Aladin, too bad I cannot buy you dinner on the last question! It was a lifesaver! Here is my last question...I have three columns. One with a numeric financial data value, and in another column same row, the value "toys" and in the last column same row a value which I successfully represented in other formulas by "a/*". This value is a/one, a/two, a/three, etc, which I need excel to "see" as the same value. I need to sum the value in column A when the conditions of "toys" and "a/*" are met. Is this convoluted enough? Please if you can, help me out again.
Thanks!
Posted by Mark W. on May 11, 2001 7:25 AM
{=SUM((A1:A100)*(B1:B100="toys")*ISNUMBER(MATCH("a/*",C1:C100,0)))}
Posted by Aladin Akyurek on May 11, 2001 7:29 AM
With Mark also at dinner, it would have been great.
Aladin
{=SUM((A1:A100)*(B1:B100="toys")*ISNUMBER(MATCH("a/*",C1:C100,0)))}
Posted by Mark W. on May 11, 2001 7:34 AM
Well, maybe not...
Aladin, the MATCH() is returning a scalar value
rather than an array. If the C1 matches then
it applies a 1 to all subsequent rows. I'm
re-examining this formulation.
Posted by Mark W. on May 11, 2001 7:38 AM
Okay, here's the fix...
{=SUM(A1:A100*(B1:B100="toys")*(LEFT(C1:C100,2)="a/"))} , the MATCH() is returning a scalar value
