"IFAND" help (I think!)

[carefreeant]

Hi,

Bit of a complicated one but hopefully it makes sense.

• To the left of my worksheet I have a list of items sold, and the price charged
• To the right of my sheet I have a price list. This shows the item, the start date of the price, and then the end date of the price

What I am basically try to write, is a formula that achieves the following:

• If the product code in cell B2
• Matches the product code in X2
• AND
• The date in A2
• Is greater than the date in Y2
• AND
• The Date in A2
• Is less than the date in Z2
• Then pull through the price in AA2

Could anybody assist with this please? I have tried various version of my base formula, but just cannot quite get it to achieve exactly what I am after.

 

[KennyGreens]

Hi there, you can try this:
<z2), aa2,="" "")


Edit: For some reason it's cutting off what I am typing...</z2),>

=IF(AND(B2=X2, A2>Y2, Z2>A2), AA2, "")

Edit2: There we go

 

[carefreeant]

Hi there, you can try this:
<z2), aa2,="" "")


Edit: For some reason it's cutting off what I am typing...</z2),>

=IF(AND(B2=X2, A2>Y2, Z2>A2), AA2, "")

Edit2: There we go

Hi,

Many thanks for this.

For some reason, however, I can only pull through blank cells. I have re-arranged my sheet slightly so that it is easier to read this end, and the formula reads as follows:

=IF(AND(B2=Q2,A2>S2,T2>A2),R2,"")

I have tried various versions of this but just cannot seem to pull anything through apart from a blank. Have I missed something?

 

[AlanY]

it works for me, may be the date format for your cells are not the same

Book1
	A	B	C	D	E	F	G
1
2	06/02/2019	Prod1	Prod1	30/01/2019	13/02/2019	100	100
Sheet1

Cell Formulas
Range	Formula
G2	=IF(AND(B2=C2,A2D2),F2,"")

 

[carefreeant]

it works for me, may be the date format for your cells are not the same

	A	B	C	D	E	F	G
1
2	06/02/2019	Prod1	Prod1	30/01/2019	13/02/2019	100	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =IF(AND(B2=C2,A2<E2,A2>D2),F2,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks.

I think I can see why we are still having trouble.

Our Sales Data (to the left of the sheet) run through over 3000 rows
The Price List to the right, is only across 20 rows (we are only looking at the prices of 20 products).

The formula being using would therefore need to point solely at the 20 rows of data in the Price List. We therefore need the formula to recognise these cells as a range I believe?

 

[jtakw]

Hi,

If I understand correctly, this should do what you want.

Change/adjust cell references/range as needed, formula copied down:

Book1
	A	B	C	D	X	Y	Z	AA
1	Sale date	Prod code	Price		Prod code	Price start	Price end	Price
2	2/6/2019	a123	400		b345	1/28/2019	1/30/2019	100
3	1/29/2019	b345	100		d456	1/29/2019	2/2/2019	200
4	1/30/2019	a123	Not Found		e789	1/30/2019	2/10/2019	300
5	1/30/2019		Not Found		a123	2/1/2019	2/15/2019	400
6	2/10/2019	e789	300		v895	2/2/2019	2/3/2019	500
7	2/11/2019	e789	Not Found
8	2/1/2019	d456	200
Sheet548

Cell Formulas
Range	Formula
C2	=IFERROR(1/(1/(SUMPRODUCT((X$2:X$6=B2)*(Y$2:Y$6<=A2)*(Z$2:Z$6>=A2)*AA$2:AA$6))),"Not Found")

 

[carefreeant]

Hi,

If I understand correctly, this should do what you want.

Change/adjust cell references/range as needed, formula copied down:

	A	B	C	D	X	Y	Z	AA
1	Sale date	Prod code	Price		Prod code	Price start	Price end	Price
2	2/6/2019	a123	400		b345	1/28/2019	1/30/2019	100
3	1/29/2019	b345	100		d456	1/29/2019	2/2/2019	200
4	1/30/2019	a123	Not Found		e789	1/30/2019	2/10/2019	300
5	1/30/2019		Not Found		a123	2/1/2019	2/15/2019	400
6	2/10/2019	e789	300		v895	2/2/2019	2/3/2019	500
7	2/11/2019	e789	Not Found
8	2/1/2019	d456	200

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet548​

Worksheet Formulas Cell Formula C2 =IFERROR(1/(1/(SUMPRODUCT((X$2:X$6=B2)*(Y$2:Y$6<=A2)*(Z$2:Z$6>=A2)*AA$2:AA$6))),"Not Found") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you for this - really appreciate it. It definitely seems to work (I have absolutely no idea why or how but it has solved the problem we are having, so I am really grateful.

Many thanks

 

[jtakw]

You're welcome, welcome to the forum, thanks for the feedback.

 

[Eric W]

A SUMIFS would also work.

C2: =IFERROR(1/(1/(SUMIFS($AA:$AA,$X:$X,B2,$Y:$Y,"<="&A2,$Z:$Z,">="&A2))),"Not Found")

and it's more forgiving of whole column references.