Multiple Criteria For One Field In Nested MIN(IF Formula

[Wookiee]

I'm tasked with identifying some very specific figures in an inventory report. I could certainly accomplish my task by using some VBA, but I prefer to use a formula.

My data is in a table (tblInv) and the fields from which I need to draw data are:

[Rep] = employee's name, stored in named range nrRep
[Category] = type of work
[Batch Status] = will always use a value of "Open"
[Received] = date the work was received

I have successfully constructed some nested MIN(IF array formulas which only take one criterion per field, but I need to use a fuzzy parameter and none of my attempts so far have worked.

My reporting requirements dictate that I extract the oldest record (minimum value in Received) for the chosen employee (nrRep) where the batch status is "Open".

The tricky part of the equation is the criteria for the Category field. All work is categorized as "Tape" or "Paper". There are two specific values which fall under "Tape" (Tape File and Tape PG); every other value is considered "Paper".

I was able to get the correct date for the Oldest Tape using the array formula below...

{=MIN(IF(tblInv[Rep]=nrRep,IF(tblInv[Batch Status]="Open",IF(OR(tblInv[Category]="Tape File",tblInv[Category]="Tape PG"),tblInv[Received]))))}

but the formula to exclude those two categories continues to elude me. I tried IF(tblInv[Category]<>"Tape*", IF(tblInv[Category]="<>Tape*", as well as IF(AND and IF(OR constructions using both of the Tape categories.

Unfortunately, I can't sit around and wait for Microsoft to introduce the MINIFS function, so can someone please offer any advice?

Thanks all!

 

[Wookiee]

I put together a mockup version of the file and, in the process, discovered that the array formula I originally assumed was working correctly does not do so in every case. Anyways, if anyone's feeling adventurous, I saved the file with example data as well as the results which should be returned. It can be viewed via the link below:

https://drive.google.com/file/d/0B03QVPPcP2UwVXZPTnI4R0trVXM/edit?usp=sharing

 

[Wookiee]

I finally came up with a solution for this issue. It's not as elegant as I would like, but it enabled me to construct a MIN(IF array formula which returns the correct result.

First, I added another column to my data table (Type) so that I could determine whether the primary category was Paper or Tape. This was the formula I used in that column:

=IF(LEFT(tblInv[[#This Row],[Category]],4)="Tape","Tape","Paper")

Once that column was in place, I was able to point my array formula to the new column instead of confusing Excel with my IF and OR constructions. The final formula was:

{=MIN(IF(tblInv[Batch Status]="Open", IF(tblInv[Rep]=nrRep, IF(tblInv[Type]="Tape", tblInv[Received]))))}