AND function and what is wrong here?

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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
GHIJKLMN
1ArtKWArtROTO NwPrijs FactorOmschrijvingVKEDoosColumn1
213300053828016,121Kunststof Rasterblok 6x2 mm blauw - 1000 stuks10001#N/A
313300153828016,1210Kunststof Rasterblok 6x2 mm blauw - 100 stuks10010#N/A
413300253828217,421Kunststof Rasterblok 6x3 mm rood - 1000 stuks10001#N/A
513300353828217,4210Kunststof Rasterblok 6x3 mm rood - 100 stuks10010#N/A
613300453828419,271Kunststof Rasterblok 6x4 mm geel - 1000 stuks10001#N/A
713300553828419,2710Kunststof Rasterblok 6x4 mm geel - 100 stuks10010#N/A
813300653828620,991Kunststof Rasterblok 6x5 mm groen - 1000 stuks10001#N/A
913300753828620,9910Kunststof Rasterblok 6x5 mm groen - 100 stuks10010#N/A
1013300853899211,521Kunststof Rasterblok 8x1 mm wit - 1000 stuks10001#N/A
1113300953899211,5210Kunststof Rasterblok 8x1 mm wit - 100 stuks10010#N/A
1213301053899312,161Kunststof Rasterblok 8x2 mm blauw - 1000 stuks10001#N/A
ROTO
Cell Formulas
RangeFormula
N2:N12N2=IFS(AND(L2=1000,M2=1),I2)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
GHIJKLMN
1ArtKWArtROTO NwPrijs FactorOmschrijvingVKEDoosColumn1
213300053828016.121Kunststof Rasterblok 6x2 mm blauw - 1000 stuks1000116.12
313300153828016.1210Kunststof Rasterblok 6x2 mm blauw - 100 stuks10010#N/A
413300253828217.421Kunststof Rasterblok 6x3 mm rood - 1000 stuks1000117.42
513300353828217.4210Kunststof Rasterblok 6x3 mm rood - 100 stuks10010#N/A
613300453828419.271Kunststof Rasterblok 6x4 mm geel - 1000 stuks1000119.27
713300553828419.2710Kunststof Rasterblok 6x4 mm geel - 100 stuks10010#N/A
813300653828620.991Kunststof Rasterblok 6x5 mm groen - 1000 stuks1000120.99
913300753828620.9910Kunststof Rasterblok 6x5 mm groen - 100 stuks10010#N/A
1013300853899211.521Kunststof Rasterblok 8x1 mm wit - 1000 stuks1000111.52
1113300953899211.5210Kunststof Rasterblok 8x1 mm wit - 100 stuks10010#N/A
1213301053899312.161Kunststof Rasterblok 8x2 mm blauw - 1000 stuks1000112.16
Sheet1
Cell Formulas
RangeFormula
N2:N12N2=IFS(AND(L2=1000,M2=1),I2)
 
Upvote 0
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
GHIJKLMN
1ArtKWArtROTO NwPrijs FactorOmschrijvingVKEDoosColumn1
213300053828016.121Kunststof Rasterblok 6x2 mm blauw - 1000 stuks1000116.12
313300153828016.1210Kunststof Rasterblok 6x2 mm blauw - 100 stuks10010#N/A
413300253828217.421Kunststof Rasterblok 6x3 mm rood - 1000 stuks1000117.42
513300353828217.4210Kunststof Rasterblok 6x3 mm rood - 100 stuks10010#N/A
613300453828419.271Kunststof Rasterblok 6x4 mm geel - 1000 stuks1000119.27
713300553828419.2710Kunststof Rasterblok 6x4 mm geel - 100 stuks10010#N/A
813300653828620.991Kunststof Rasterblok 6x5 mm groen - 1000 stuks1000120.99
913300753828620.9910Kunststof Rasterblok 6x5 mm groen - 100 stuks10010#N/A
1013300853899211.521Kunststof Rasterblok 8x1 mm wit - 1000 stuks1000111.52
1113300953899211.5210Kunststof Rasterblok 8x1 mm wit - 100 stuks10010#N/A
1213301053899312.161Kunststof Rasterblok 8x2 mm blauw - 1000 stuks1000112.16
Sheet1
Cell Formulas
RangeFormula
N2:N12N2=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.
 
Upvote 0
ok,
in a cell - just do
L2+1
M2+1
see if it adds 1 to the value or you get an error
 
Upvote 0
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

Excel Formula:
=IF(AND(L2-1000=0,M2-1=0),I2,"")
 
Upvote 0
Solution
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

Excel Formula:
=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.
 
Upvote 0
if l2=1000
then l2-1000 should = 0
so
l2-1000=0
will give a TRUE if L2 =1000 - otherwise a FALSE
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top