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.

 

[Peter_SSs]

answered already :D thank you again.

You're welcome. (I thought that you must have missed my post. )

 

[Arlind123]

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

Is there a way I can get it to count for two set things in same column.

Also if there is a count of 0, the minif formula shows as an error at the moment. Is there a way get it to show as - or 0

 

[Peter_SSs]

As has already been asked a couple of times in the thread: Please post some sample data and expected results with XL2BB.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Arlind123]

Book1
	A	B	C	D
1		Eggs	And	Bacon
2	Total Routine	0	2	13
3	Oldest date	#NUM!	01 January 2020	01 January 2020
4	Total Urgent	0	12	1
5	Oldest date	#NUM!	10 October 2010	01 October 2021
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTIF(Eggs!$C$1:$C$14,"Routine")
C2	C2	=COUNTIF(And!$C$1:$C$14,"Routine")
D2	D2	=COUNTIF(Bacon!$C$1:$C$14,"Routine")
B3	B3	=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
C3	C3	=AGGREGATE(15,6,And!B1:B14/(And!C1:C14="Routine"),1)
D3	D3	=AGGREGATE(15,6,Bacon!B1:B14/(Bacon!C1:C14="Routine"),1)
B4	B4	=COUNTIF(Eggs!$C$1:$C$14,"Urgent")
C4	C4	=COUNTIF(And!$C$1:$C$14,"Urgent")
D4	D4	=COUNTIF(Bacon!$C$1:$C$14,"Urgent")
B5	B5	=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1)
C5	C5	=AGGREGATE(15,6,And!B1:B14/(And!C1:C14="Urgent"),1)
D5	D5	=AGGREGATE(15,6,Bacon!B1:B14/(Bacon!C1:C14="Urgent"),1)

 

[Arlind123]

Even though on Egg's tabs, there is a count of 0. It shows as #num! - is there a way to show as - or 0?

 

[Peter_SSs]

It shows as #num! - is there a way to show as - or 0?

=IFERROR(your_existing_formula,"-")
or
=IFERROR(your_existing_formula,0)