MINIF alternative

[Arlind123]

Hi,

Was hoping I could get some help with getting minif formula.

I was initially using below formula, on my laptop using office but doesn't seem to work on excel 16.

=MINIFS(Review!B:B,Review!F:F,"Urgent")
=minifs(Date,priority,”X”)

I'm using 2sheets.
Sheet 1 is like a dashboard, and sheet 2 contains data.

Formula is for two columns.
Column B is dates, Column text

The data I'm trying to pull in this is: if Column B is *urgent* the earliest date on column F is given.

 

[JoeMo]

MINIFS function is available only in Excel 2019 and MS 365. You might be able to get a solution here if you post some sample data using XL2BB.

This is an array formula that achieves the same result as MINIFS but is slower in calculating.

Book1.xlsm
	A	B	C	D
1	1
2	2			3
3	3	URGENT
4	4
5	5
6	6	URGENT
7	7	URGENT
8	8
9	9
10	10
Sheet2

Cell Formulas
Range	Formula
D2	D2	=MIN(IF(B1:B10="URGENT",A1:A10))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Arlind123]

Trying to do this for more than one column in same sheet but doesn't seem to work.

I.e pull information for urgent as you have on D2, and additional on D3

 

[JoeMo]

Trying to do this for more than one column in same sheet but doesn't seem to work.

I.e pull information for urgent as you have on D2, and additional on D3

Post some sample data using XL2BB (click on XL2BB on the reply toolbar) with an explanation of what you want as a result and maybe we can help you.

 

[Arlind123]

example1.xlsx
	A	B	C	D	E
3
4		Eggs	And	Bacon
5	Total Routine
6	Oldest date
7	Total Urgent
8	Oldest date
9
10
Dashboard

 

[Arlind123]

example1.xlsx
	A	B	C
1	Random	01/10/2021	Urgent
2	text	02/10/2021	Routine
3	generated	03/10/2021	Routine
4	here	04/10/2021	Urgent
5	to	05/10/2021	Urgent
6	give	06/10/2021	Urgent
7	example	07/10/2021	Routine
8	example	08/10/2021	Urgent
9	example	09/10/2021	Routine
10	example	10/10/2021	Urgent
11	example	11/10/2021	Routine
12	example	12/10/2021	Routine
13	example	13/10/2021	Routine
14	example	01/01/2020	Routine
Eggs

 

[Arlind123]

so as above, Id like to have 4 sheets, one called dashboard. Which will pull data from other 3 sheets.

In this case, eggs and bacon.
I'd like to fill Dashboard without doing it manually.

I've been able to get total amount of sum count. The only problem I'm having is the minif alternative.

 

[JoeMo]

Not sure, I fully understand your explanation of what you want. Focusing on the MINIFS alternative, is this what you want to find the Oldest Date? In your example, the oldest date for "Routine" would be 1/1/2020, is that correct? And for "Urgent" it would be 1/10/2021?

 

[Arlind123]

yes

 

[JoeMo]

This uses your example data from posts # 5 & 6 using two sheets in the same workbook named "Dashboard" and "Eggs" as shown below.
Note that the oldest date formulas should be entered using ctrl+shift+enter, not just enter if you are using a version of Excel earlier than 2019.

Book1.xlsm
	A	B	C	D
4		Eggs	And	Bacon
5	Total Routine	8
6	Oldest date	1/1/2020
7	Total Urgent	6
8	Oldest date	10/1/2021
Dashboard

Cell Formulas
Range	Formula
B5	B5	=COUNTIF(Eggs!$C$1:$C$14,"Routine")
B6	B6	=MIN(IF(Eggs!$C$1:$C$14="Routine",Eggs!$B$1:$B$14))
B7	B7	=COUNTIF(Eggs!$C$1:$C$14,"Urgent")
B8	B8	=MIN(IF(Eggs!$C$1:$C$14="Urgent",Eggs!$B$1:$B$14))
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1.xlsm
	A	B	C
1	Random	10/1/2021	Urgent
2	text	10/2/2021	Routine
3	generated	10/3/2021	Routine
4	here	10/4/2021	Urgent
5	to	10/5/2021	Urgent
6	give	10/6/2021	Urgent
7	example	10/7/2021	Routine
8	example	10/8/2021	Urgent
9	example	10/9/2021	Routine
10	example	10/10/2021	Urgent
11	example	10/11/2021	Routine
12	example	10/12/2021	Routine
13	example	10/13/2021	Routine
14	example	1/1/2020	Routine
Eggs