Return a value if 2 Conditions apply  
Author Message
dinee





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>