create a list of articles (also duplicates) of a database with 2 conditions

[Superutz]

Hi everybody!!!
I already get help from the forum few days before about create a list of articles of a db with one conditions, and it works perfectly!!!
So I've tried to follow this formula to get also a list of articles with 2 conditions simply adding the second conditions after the first one but i didn't get any result, or even I get the wrong value.
Here is the file report and stats 2.0.xlsx
I want to make a list of all the trucks of the column "FROM" in imported data sheet (all the trucks have in their value the "-" character) reported in the "CODE" sheet in "FROM" table only once that also have in "TYPE" column the value "0".
After this, I want to know in the following cell how many times these value of the trucks are in the imported data also in the "code" column = 0
I hope I explained pretty well the situations.
Thank you all in advance
Superutz

[Superutz]

Is it possible?

[JohnTopley]

Try this: AND condition is done by multiplying the criteria.

Use SEARCH to find "-" in your string

Enter with Ctrl+Shift+Enter

=IFERROR(INDEX('imported data'!$M$2:$M$20,SMALL(IF(ISNUMBER((SEARCH("-",'imported data'!$M$2:$M$20)))*('imported data'!$C$2:$C$20=0),ROW('imported data'!$M$2:$M$20)-1),ROWS($A$1:A1))),"")

[Superutz]

JohnToplay, thank you! finally it shows me the values...Just one thing, I don't want to have in the list the duplicate values do you think it's possible???
Thanks

[JohnTopley]

Not sure if/how to do this.

[Superutz]

thank you anyway!!!
Does anybody know if it could be done???


Sorry also for my English

[JohnTopley]

If you can use (for example) column A as a "helper" column you can remove duplicates. I changed some data just to test it.

[micope21]

Hi

To remove duplicates?

B10

Please Login or Register  to view this content.

Non-array! Just press enter, then copy down.

[oeldere]

Please don't PM forummembers.

Hello!!! Sorry for disturbing you but I'm in a mess with oner formula...
You gave me the solution in a thread of some days ago and considerring that it's almost the same formula (but with 2 conditions and creating a list without reporting more than one time the same value) I'm writing you for a help...
Could you have a look and be so kindly to tell me if what I need to do is possible?
Thank you in advance and sorry for my English.
here is the link of my thread:
http://www.excelforum.com/excel-form...ml#post4128151

Thank you!!!
Superutz

I think I already sent you this message one minute ago but in Sent Items there isn't any message

[JohnTopley]

Minor typo: COUNTIF should be B$9:B9

[Superutz]

Could this formula recognise for example both "bot-25-tc" and "245-sp-31" values (because i've just realized that the name of the trucks could starts either with a letter and a number)??? Thank you

[JohnTopley]

Any of the formula will work with trucks starting with number or letter. See the excellent formula submitted by Micope21 but change the COUNTIF reference from C to B (see my previous post).

[micope21]

Thank you John point out!

My fault as I working next to Column B

Superutz. Copy and paste it on your files above #8 post as I put it right this time?

Should be on COUNTIF(B$9:B9 not COUNTIF(C$9:C9

[Superutz]

Ok thank you micope21 and JohnTopley I try and I will let you know the outcome

[Superutz]

I have tried but I'm wrong in something... (no value!)
This is the file report and stats 2.0.xlsx

[micope21]

See the files

[Superutz]

That's perfect!!! Thanks a lot!!! I will study this formula to understand how it works!!! Tha's amazing!!!