Need to find MAX/MIN of figures in a colum with criteria coming from different sheets

[rcane]

I have two sheets with a column for ticket numbers in both of them--though they are out of order.

1. starting from sheet one, I must identify the ticket numbers that go with "dodge"
2. then take those ticket numbers over to sheet 2 where i can then examine those same found ticket numbers and see which have sold in the "Y" column.
3. finally, there is a days sold column, where i need to find the max and min days taken to sell.

4. i can't make any changes to the two sheets, so I have to work it all on a third sheet of my own. Same workbook, just a new sheet.

The max should give "8"; the min, "7" as those are the only two dodges that sold.

I've already had some good guidance on how to 'count' the number of dodge's that sold, but I cant seem to take that formulation one step further to take a max/min...or maxifs/minifs.

Excel Pro + 2019 on Windows10.


I had been looking at something like this:

=max(if(sale!b2:b14="y", if(index(type!b2:b14, match(sale!a2:a14, type!a2:a14,0))="dodge", sale!c2:c14)


or


=MINIFS(sale!c2:c14, sale!b2:b14, "Y", INDEX(type!B2:B14, MATCH(sale!a2:a14, type!a2:a14, 0)), "DODGE")

[MarvinP]

Hi rcane,

It looks like you need to create a relationship between your two tables and then do a simple Pivot Table. Does the below site help?
https://support.microsoft.com/en-us/...9-8a3848820be3

[rcane]

Do you think there is anyway to do this without relationships? I'm trying to set this up where I get the new workbook and import the sheets into my workbook. Setting up new relationships is going to get tedious.

[MarvinP]

Hi rcane,

It appeared to me that you need to get both tables together somehow on a third sheet to do what you wanted. If you had a good standard of where both sets of data will land on the first two sheets, a macro might do what you want. I also think Power Query can put those two tables together. That might be your best bet.

[kvsrinivasamurthy]

Pl see file.Formulas will work even the number rows are different in sale and type sheets.
ARRAY formulas are used
For Max in B2 of Sheet1

Please Login or Register  to view this content.

For Min in B3 of Sheet1

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[Gregb11]

For the MAX, you can also try this:

=MAX(COUNTIFS(sale!$A$2:$A$14,($A$2:$A$14)*($B$2:$B$14="DODGE"),sale!$B$2:$B$14,"Y")*sale!$C$2:$C$14)

(if not on O365, enter as an array formula)