AND function and what is wrong here?

[Romano_odK]

Good morning,

I am trying to make this IFS and AND function to work in the last column. If the value in the column VKE = 1000 and the value in column Doos = 1 then I want to see the value in column NwPrijs. My approach is obviously wrong because I get N/A everywhere. I need more of these AND function in the same formula so I thought using IFS and AND would be best.

Thank you for you time.

Romano

Onderhoud prijzen 9.4.04F.xlsm
	G	H	I	J	K	L	M	N
1	ArtKW	ArtROTO	NwPrijs	Factor	Omschrijving	VKE	Doos	Column1
2	133000	538280	16,12	1	Kunststof Rasterblok 6x2 mm blauw - 1000 stuks	1000	1	#N/A
3	133001	538280	16,12	10	Kunststof Rasterblok 6x2 mm blauw - 100 stuks	100	10	#N/A
4	133002	538282	17,42	1	Kunststof Rasterblok 6x3 mm rood - 1000 stuks	1000	1	#N/A
5	133003	538282	17,42	10	Kunststof Rasterblok 6x3 mm rood - 100 stuks	100	10	#N/A
6	133004	538284	19,27	1	Kunststof Rasterblok 6x4 mm geel - 1000 stuks	1000	1	#N/A
7	133005	538284	19,27	10	Kunststof Rasterblok 6x4 mm geel - 100 stuks	100	10	#N/A
8	133006	538286	20,99	1	Kunststof Rasterblok 6x5 mm groen - 1000 stuks	1000	1	#N/A
9	133007	538286	20,99	10	Kunststof Rasterblok 6x5 mm groen - 100 stuks	100	10	#N/A
10	133008	538992	11,52	1	Kunststof Rasterblok 8x1 mm wit - 1000 stuks	1000	1	#N/A
11	133009	538992	11,52	10	Kunststof Rasterblok 8x1 mm wit - 100 stuks	100	10	#N/A
12	133010	538993	12,16	1	Kunststof Rasterblok 8x2 mm blauw - 1000 stuks	1000	1	#N/A
ROTO

Cell Formulas
Range	Formula
N2:N12	N2	=IFS(AND(L2=1000,M2=1),I2)

 

[etaf]

working for me

are you sure the numbers are numbers and NOT text
They seem ok, looking at the justification
and of course will copy into excel OK

Book1
	G	H	I	J	K	L	M	N
1	ArtKW	ArtROTO	NwPrijs	Factor	Omschrijving	VKE	Doos	Column1
2	133000	538280	16.12	1	Kunststof Rasterblok 6x2 mm blauw - 1000 stuks	1000	1	16.12
3	133001	538280	16.12	10	Kunststof Rasterblok 6x2 mm blauw - 100 stuks	100	10	#N/A
4	133002	538282	17.42	1	Kunststof Rasterblok 6x3 mm rood - 1000 stuks	1000	1	17.42
5	133003	538282	17.42	10	Kunststof Rasterblok 6x3 mm rood - 100 stuks	100	10	#N/A
6	133004	538284	19.27	1	Kunststof Rasterblok 6x4 mm geel - 1000 stuks	1000	1	19.27
7	133005	538284	19.27	10	Kunststof Rasterblok 6x4 mm geel - 100 stuks	100	10	#N/A
8	133006	538286	20.99	1	Kunststof Rasterblok 6x5 mm groen - 1000 stuks	1000	1	20.99
9	133007	538286	20.99	10	Kunststof Rasterblok 6x5 mm groen - 100 stuks	100	10	#N/A
10	133008	538992	11.52	1	Kunststof Rasterblok 8x1 mm wit - 1000 stuks	1000	1	11.52
11	133009	538992	11.52	10	Kunststof Rasterblok 8x1 mm wit - 100 stuks	100	10	#N/A
12	133010	538993	12.16	1	Kunststof Rasterblok 8x2 mm blauw - 1000 stuks	1000	1	12.16
Sheet1

Cell Formulas
Range	Formula
N2:N12	N2	=IFS(AND(L2=1000,M2=1),I2)

 

[Romano_odK]

working for me

are you sure the numbers are numbers and NOT text
They seem ok, looking at the justification
and of course will copy into excel OK

Book1
	G	H	I	J	K	L	M	N
1	ArtKW	ArtROTO	NwPrijs	Factor	Omschrijving	VKE	Doos	Column1
2	133000	538280	16.12	1	Kunststof Rasterblok 6x2 mm blauw - 1000 stuks	1000	1	16.12
3	133001	538280	16.12	10	Kunststof Rasterblok 6x2 mm blauw - 100 stuks	100	10	#N/A
4	133002	538282	17.42	1	Kunststof Rasterblok 6x3 mm rood - 1000 stuks	1000	1	17.42
5	133003	538282	17.42	10	Kunststof Rasterblok 6x3 mm rood - 100 stuks	100	10	#N/A
6	133004	538284	19.27	1	Kunststof Rasterblok 6x4 mm geel - 1000 stuks	1000	1	19.27
7	133005	538284	19.27	10	Kunststof Rasterblok 6x4 mm geel - 100 stuks	100	10	#N/A
8	133006	538286	20.99	1	Kunststof Rasterblok 6x5 mm groen - 1000 stuks	1000	1	20.99
9	133007	538286	20.99	10	Kunststof Rasterblok 6x5 mm groen - 100 stuks	100	10	#N/A
10	133008	538992	11.52	1	Kunststof Rasterblok 8x1 mm wit - 1000 stuks	1000	1	11.52
11	133009	538992	11.52	10	Kunststof Rasterblok 8x1 mm wit - 100 stuks	100	10	#N/A
12	133010	538993	12.16	1	Kunststof Rasterblok 8x2 mm blauw - 1000 stuks	1000	1	12.16
Sheet1

Cell Formulas
Range	Formula
N2:N12	N2	=IFS(AND(L2=1000,M2=1),I2)

How can I check that and change? I edited the cells to "Number with two decimals", but that isn't enough it seems.

 

[etaf]

ok,
in a cell - just do
L2+1
M2+1
see if it adds 1 to the value or you get an error

 

[Peter_SSs]

I suspect that it is a text v numbers issue.
Two other things
- You don't need IFS since you only have one test to do
- You formula does not say what to return if the two values are not 1000 and 1

However, try this to start with

=IF(AND(L2-1000=0,M2-1=0),I2,"")

 

[Romano_odK]

I suspect that it is a text v numbers issue.
Two other things
- You don't need IFS since you only have one test to do
- You formula does not say what to return if the two values are not 1000 and 1

However, try this to start with

=IF(AND(L2-1000=0,M2-1=0),I2,"")

Good morning,
I totally do not understand this formula. L2-1000=0? Yes it works. I need the IFS statement, so I will build further on this. Thank you.

 

[etaf]

if l2=1000
then l2-1000 should = 0
so
l2-1000=0
will give a TRUE if L2 =1000 - otherwise a FALSE

 

[Peter_SSs]

I need the IFS statement

You can use IFS if you want, but I don't much like the function. The reason is that every section has to be evaluated before the formula returns a result.
Example
=IFS(1<2,"a",3>4,"b",5>3,"c")
Here all three test will be evaluated before the formula returns "a" from the first test.
To me, the following makes more sense since as soon as the first test returns TRUE, the formula does not need to check all the other tests and simply immediately returns "a"
=IF(1<2,"a",IF(3>4,"b",IF(5>3,"c","")))

I totally do not understand this formula. L2-1000=0

It is just another way of writing L2=1000. After all the only way either of them can be true is if L2 is 1000.

If L2 is a text "1000" then L2=1000 will return false. However L2-1000 forces the text 1000 into a numerical 1000