|
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"),((Conso l.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 |
Return a value if 2 Conditions apply
could be cuz
eur eur so try trimming it first =SUMPRODUCT((TRIM(I2:I4)="eur")*1) -- Don Guillett SalesAid Software "John" wrote in message ... 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"),((Conso l.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 |
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(Con sol.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" wrote in message ... 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"),((Conso l.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 |
Return a value if 2 Conditions apply
Perfect Bernie, thanks
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
Return a value if 2 Conditions apply
If I understand correctly, try:
=IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... If I understand correctly, try: =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
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 "John" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... If I understand correctly, try: =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
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(Con sol.Comments)))=0, "",INDEX(Consol.Comments,SUMPRODUCT(-- (Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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 "John" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... If I understand correctly, try: =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
Return a value if 2 Conditions apply
Thats exactly what I want, thanks for your help Bernie
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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(Con sol.Comments)))=0, "",INDEX(Consol.Comments,SUMPRODUCT(-- (Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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 "John" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... If I understand correctly, try: =IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) HTH, Bernie MS Excel MVP "John" wrote in message ... 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(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, Something along the lines of =INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.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" wrote in message ... 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"),((Conso l.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 |
| All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com