Return a value if 2 Conditions apply

[John]

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

 

[Don Guillett]

could be cuz
eur
eur
so try trimming it first
=SUMPRODUCT((TRIM(I2:I4)="eur")*1)

 

[Bernie Deitrick]

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

 

[John]

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

 

[John]

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))))

 

[Bernie Deitrick]

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

 

[John]

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

 

[John]

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

 

[Bernie Deitrick]

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

 

[John]

Thats exactly what I want, thanks for your help Bernie