Return a value if 2 Conditions apply |
|
Author |
Message |
dinee
|
Posted: Mon Dec 19 08:14:47 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
I want to return a value in a database listing if 2 conditions apply. This
value is Text. I have the following formula which works great when I am
search for a numeric field but doesn't work when the value is Text
=SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
The Consol.Comments range is the value I am trying to return. If I use the
above formula it returns 0, not the correct answer
Excel473
|
|
|
|
|
Don
|
Posted: Mon Dec 19 08:14:47 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
could be cuz
eur
eur
so try t**** it first
=SUMPRODUCT((TRIM(I2:I4)="eur")*1)
--
Don Guillett
SalesAid Software
>I want to return a value in a database listing if 2 conditions apply. This
>value is Text. I have the following formula which works great when I am
>search for a numeric field but doesn't work when the value is Text
>
> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>
> The Consol.Comments range is the value I am trying to return. If I use the
> above formula it returns 0, not the correct answer
>
>
>
|
|
|
|
|
Bernie
|
Posted: Mon Dec 19 08:15:12 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
John,
Something along the lines of
=INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
will work, if comments is a range that starts in row 1 - otherwise, you need to subtract an
offset...
HTH,
Bernie
MS Excel MVP
>I want to return a value in a database listing if 2 conditions apply. This value is Text. I have
>the following formula which works great when I am search for a numeric field but doesn't work when
>the value is Text
>
> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>
> The Consol.Comments range is the value I am trying to return. If I use the above formula it
> returns 0, not the correct answer
>
>
>
|
|
|
|
|
John
|
Posted: Mon Dec 19 08:30:31 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
Perfect Bernie, thanks
> John,
>
> Something along the lines of
>
> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>
> will work, if comments is a range that starts in row 1 - otherwise, you
> need to subtract an offset...
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>>I want to return a value in a database listing if 2 conditions apply. This
>>value is Text. I have the following formula which works great when I am
>>search for a numeric field but doesn't work when the value is Text
>>
>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>
>> The Consol.Comments range is the value I am trying to return. If I use
>> the above formula it returns 0, not the correct answer
>>
>>
>>
>
>
|
|
|
|
|
John
|
Posted: Mon Dec 19 08:44:13 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
Bernie, I'm getting inconsistent returns, where I expect a certain value it
returns correct. I've amended your formula slightly to account for Zero's
and thus return <blank>, not sure why I am not getting the correct result
=IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
> John,
>
> Something along the lines of
>
> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>
> will work, if comments is a range that starts in row 1 - otherwise, you
> need to subtract an offset...
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>>I want to return a value in a database listing if 2 conditions apply. This
>>value is Text. I have the following formula which works great when I am
>>search for a numeric field but doesn't work when the value is Text
>>
>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>
>> The Consol.Comments range is the value I am trying to return. If I use
>> the above formula it returns 0, not the correct answer
>>
>>
>>
>
>
|
|
|
|
|
Bernie
|
Posted: Mon Dec 19 08:48:05 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
If I understand correctly, try:
=IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
HTH,
Bernie
MS Excel MVP
> Bernie, I'm getting inconsistent returns, where I expect a certain value it returns correct. I've
> amended your formula slightly to account for Zero's and thus return <blank>, not sure why I am not
> getting the correct result
>
> =IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>
>
>> John,
>>
>> Something along the lines of
>>
>> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>>
>> will work, if comments is a range that starts in row 1 - otherwise, you need to subtract an
>> offset...
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>>I want to return a value in a database listing if 2 conditions apply. This value is Text. I have
>>>the following formula which works great when I am search for a numeric field but doesn't work
>>>when the value is Text
>>>
>>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>>
>>> The Consol.Comments range is the value I am trying to return. If I use the above formula it
>>> returns 0, not the correct answer
>>>
>>>
>>>
>>
>>
>
>
|
|
|
|
|
John
|
Posted: Mon Dec 19 08:58:57 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
I just can't get it to work Bernie, it returns Zero now where I expect a
value, not sure if I'm explaining it correctly
> If I understand correctly, try:
>
> =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>> Bernie, I'm getting inconsistent returns, where I expect a certain value
>> it returns correct. I've amended your formula slightly to account for
>> Zero's and thus return <blank>, not sure why I am not getting the correct
>> result
>>
>> =IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>
>>
>>> John,
>>>
>>> Something along the lines of
>>>
>>> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>>>
>>> will work, if comments is a range that starts in row 1 - otherwise, you
>>> need to subtract an offset...
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>>
>>>>I want to return a value in a database listing if 2 conditions apply.
>>>>This value is Text. I have the following formula which works great when
>>>>I am search for a numeric field but doesn't work when the value is Text
>>>>
>>>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>>>
>>>> The Consol.Comments range is the value I am trying to return. If I use
>>>> the above formula it returns 0, not the correct answer
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
|
|
|
|
John
|
Posted: Mon Dec 19 09:04:24 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
Okay this is getting confusing Bernie, from a brief sample your code seems
to work, but when Consol.Comments = <blank> instead of return <blank> as per
your formula it returns Zero
>I just can't get it to work Bernie, it returns Zero now where I expect a
>value, not sure if I'm explaining it correctly
>
>
>> If I understand correctly, try:
>>
>> =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>> Bernie, I'm getting inconsistent returns, where I expect a certain value
>>> it returns correct. I've amended your formula slightly to account for
>>> Zero's and thus return <blank>, not sure why I am not getting the
>>> correct result
>>>
>>> =IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>>
>>>
>>>> John,
>>>>
>>>> Something along the lines of
>>>>
>>>> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>>>>
>>>> will work, if comments is a range that starts in row 1 - otherwise, you
>>>> need to subtract an offset...
>>>>
>>>> HTH,
>>>> Bernie
>>>> MS Excel MVP
>>>>
>>>>
>>>>>I want to return a value in a database listing if 2 conditions apply.
>>>>>This value is Text. I have the following formula which works great when
>>>>>I am search for a numeric field but doesn't work when the value is Text
>>>>>
>>>>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>>>>
>>>>> The Consol.Comments range is the value I am trying to return. If I use
>>>>> the above formula it returns 0, not the correct answer
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
|
|
|
|
Bernie
|
Posted: Mon Dec 19 09:44:58 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
John,
This returns "" when the corresponding comment is blank, otherwise it returns the comment string:
=IF(INDEX(Consol.Comments,SUMPRODUCT(--
(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,
"",INDEX(Consol.Comments,SUMPRODUCT(--
(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
HTH,
Bernie
MS Excel MVP
> Okay this is getting confusing Bernie, from a brief sample your code seems to work, but when
> Consol.Comments = <blank> instead of return <blank> as per your formula it returns Zero
>
>
>>I just can't get it to work Bernie, it returns Zero now where I expect a value, not sure if I'm
>>explaining it correctly
>>
>>
>>> If I understand correctly, try:
>>>
>>> =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>>
>>>> Bernie, I'm getting inconsistent returns, where I expect a certain value it returns correct.
>>>> I've amended your formula slightly to account for Zero's and thus return <blank>, not sure why
>>>> I am not getting the correct result
>>>>
>>>> =IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>>>
>>>>
>>>>> John,
>>>>>
>>>>> Something along the lines of
>>>>>
>>>>> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>>>>>
>>>>> will work, if comments is a range that starts in row 1 - otherwise, you need to subtract an
>>>>> offset...
>>>>>
>>>>> HTH,
>>>>> Bernie
>>>>> MS Excel MVP
>>>>>
>>>>>
>>>>>>I want to return a value in a database listing if 2 conditions apply. This value is Text. I
>>>>>>have the following formula which works great when I am search for a numeric field but doesn't
>>>>>>work when the value is Text
>>>>>>
>>>>>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>>>>>
>>>>>> The Consol.Comments range is the value I am trying to return. If I use the above formula it
>>>>>> returns 0, not the correct answer
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
|
|
|
|
John
|
Posted: Mon Dec 19 10:49:59 CST 2005 |
Top |
Excel Programming >> Return a value if 2 Conditions apply
Thats exactly what I want, thanks for your help Bernie
> John,
>
> This returns "" when the corresponding comment is blank, otherwise it
> returns the comment string:
>
> =IF(INDEX(Consol.Comments,SUMPRODUCT(--
> (Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,
> "",INDEX(Consol.Comments,SUMPRODUCT(--
> (Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>> Okay this is getting confusing Bernie, from a brief sample your code
>> seems to work, but when Consol.Comments = <blank> instead of return
>> <blank> as per your formula it returns Zero
>>
>>
>>>I just can't get it to work Bernie, it returns Zero now where I expect a
>>>value, not sure if I'm explaining it correctly
>>>
>>>
>>>> If I understand correctly, try:
>>>>
>>>> =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>>>
>>>> HTH,
>>>> Bernie
>>>> MS Excel MVP
>>>>
>>>>
>>>>> Bernie, I'm getting inconsistent returns, where I expect a certain
>>>>> value it returns correct. I've amended your formula slightly to
>>>>> account for Zero's and thus return <blank>, not sure why I am not
>>>>> getting the correct result
>>>>>
>>>>> =IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments)))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Consol.Comments))))
>>>>>
>>>>>
>>>>>> John,
>>>>>>
>>>>>> Something along the lines of
>>>>>>
>>>>>> =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Consol.Comments))
>>>>>>
>>>>>> will work, if comments is a range that starts in row 1 - otherwise,
>>>>>> you need to subtract an offset...
>>>>>>
>>>>>> HTH,
>>>>>> Bernie
>>>>>> MS Excel MVP
>>>>>>
>>>>>>
>>>>>>>I want to return a value in a database listing if 2 conditions apply.
>>>>>>>This value is Text. I have the following formula which works great
>>>>>>>when I am search for a numeric field but doesn't work when the value
>>>>>>>is Text
>>>>>>>
>>>>>>> =SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Consol.Comments)))
>>>>>>>
>>>>>>> The Consol.Comments range is the value I am trying to return. If I
>>>>>>> use the above formula it returns 0, not the correct answer
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
|
|
|
|
|
|