MINIF alternative

A

Arlind123

New Member
Joined
Nov 3, 2021
Messages
21
Platform
  1. Windows
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.
 
Sort by date Sort by votes
Another option would be to use the AGGREGATE function which does not require the Ctrl+Shift+Enter confirmation.

Same 'Eggs' sheet as above

Arlind123.xlsm
AB
4Eggs
5Total Routine8
6Oldest date1/01/2020
7Total Urgent 6
8Oldest date1/10/2021
Dashboard
Cell Formulas
RangeFormula
B5B5=COUNTIF(Eggs!$C$1:$C$14,"Routine")
B6B6=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
B7B7=COUNTIF(Eggs!$C$1:$C$14,"Urgent")
B8B8=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1)
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
<table><tr><td style="height:12.75pt"></td><td style="vertical-align:middle">Eggs</td></tr><tr><td style="text-align:left;vertical-align:middle;height:12.75pt">Total&#32;Routine</td><td class="fx" style="vertical-align:middle">=COUNTIF(Eggs!$C$1:$C$14,"Routine")</td></tr><tr><td style="text-align:left;vertical-align:middle;height:12.75pt">Oldest&#32;date</td><td class="fx" style="vertical-align:middle">=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)</td></tr><tr><td style="text-align:left;vertical-align:middle;height:12.75pt">Total&#32;Urgent&#32;</td><td class="fx" style="vertical-align:middle">=COUNTIF(Eggs!$C$1:$C$14,"Urgent")</td></tr><tr><td style="text-align:left;vertical-align:middle;height:12.75pt">Oldest&#32;date</td><td class="fx" style="vertical-align:middle">=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1)</td></tr></table>

would it be possible if i added a 2nd criteria?

example =AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1) +A1:A14!="Breakfast*")

The information it would pull out if the min date for Urgent for breakfast
 
Upvote 0
You cannot show a table using HTML tags like that. Use XL2BB like you did before.

For two criteria you could use a structure like this

Excel Formula: 
=AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(Eggs!A1:A14="Breakfast")),1)
 
Upvote 0
this is great and works well.

wanted to know if there is additional to change / ammend.
If i wanted to there was a way to add part of text.

e.g on - =AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(Eggs!A1:A14="Breakfast")),1)
Have breakfast*
as it only counts breakfast not breakfast menu from cell.

also noticed on
=AGGREGATE(15,6,And!B1:B14/(Eggs!C1:C14="Routine"),1)
why i tried to count full columns, it would just give me an error.

is there a way around this?
 
Upvote 0
example1.xlsx
ABCDEFG
3
4EggsAndBacon
5Total Routine8213
6Oldest date01 January 201501 January 202001 January 2015
7Total Urgent 6121
8Oldest date01 January 201901 January 201601 October 2021
9
10
1101 January 2019
12
13
14
15
16
Dashboard
Cell Formulas
RangeFormula
B5B5=COUNTIF(Eggs!$C$1:$C$14,"Routine")
C5C5=COUNTIF(And!$C$1:$C$14,"Routine")
D5D5=COUNTIF(Bacon!$C$1:$C$14,"Routine")
B6B6=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
C6C6=AGGREGATE(15,6,And!B1:B14/(Eggs!C1:C14="Routine"),1)
D6D6=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
B7B7=COUNTIF(Eggs!$C$1:$C$14,"Urgent")
C7C7=COUNTIF(And!$C$1:$C$14,"Urgent")
D7D7=COUNTIF(Bacon!$C$1:$C$14,"Urgent")
B8B8=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1)
C8C8=AGGREGATE(15,6,And!B1:B14/(And!C1:C14="Urgent"),1)
D8D8=AGGREGATE(15,6,Bacon!B1:B14/(Bacon!C1:C14="Urgent"),1)
C11C11=AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(Eggs!A1:A14="Breakfast")),1)
 
Upvote 0
example1.xlsx
ABCD
1Breakfast01/01/2019Urgent
2Breakfast menu01/01/2015Routine
3Breakfast menu03/10/2021Routine
4Lunch Menu04/10/2021Urgent
5Lunch Menu05/10/2021Urgent
6Breakfast menu06/10/2021Urgent
7Lunch Menu07/10/2021Routine
8Desert Menu08/10/2021Urgent
9Desert Menu09/10/2021Routine
10Breakfast menu10/10/2021Urgent
11Breakfast menu11/10/2021Routine
12Breakfast menu12/10/2021Routine
13Lunch Menu13/10/2021Routine
14Lunch Menu01/01/2020Routine
15Desert Menu01/05/2019Urgent
16
Eggs
 
Upvote 0
Arlind123 said:
e.g on - =AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(Eggs!A1:A14="Breakfast")),1)
Have breakfast*
as it only counts breakfast not breakfast menu from cell.
Click to expand...

Excel Formula: 
=AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(LEFT(Eggs!A1:A14,9)="Breakfast")),1)


Arlind123 said:
also noticed on
=AGGREGATE(15,6,And!B1:B14/(Eggs!C1:C14="Routine"),1)
why i tried to count full columns, it would just give me an error.
Click to expand...
Not quite sure what you are saying here as your formula is referring to two different sheets and I don't know what is on 'And' sheet.
In any case I would strongly recommend not using whole column references. Asking Excel to process over a million cells in each column when I presume that you are using much less than that will just bog your sheet down. If you don';t want to be adjusting the formula ranges all the time, then just make the ranges big enough to cover any amount of data you are likely to have. For example, if you might have just a few hundred rows, then make the range, say, 3,000 rows. That is still a tiny fraction of one million and will calculate much faster.
 
Upvote 0
Hi Peter,

appreciate your help this late.

I've made 3 sheets called, Eggs , And, Bacon for the purpose to extract date into the main dashboard.
When I use - =AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(LEFT(Eggs!A1:A14,9)="Breakfast")),1)
it pulls data from Text on Column A exact as "Breakfast".

I tried to add Breakfast* so it's counted if its called "breakfast menu" etc.

is there a way around this
 
Upvote 0
example1.xlsx
ABCD
4EggsAndBacon
5Total Routine8213
6Oldest date01 January 201510 October 201001 January 2015
7Total Urgent 6121
8Oldest date01 January 201910 October 201001 October 2021
9
10
1101 January 201901 January 2019
Dashboard
Cell Formulas
RangeFormula
B5B5=COUNTIF(Eggs!$C$1:$C$14,"Routine")
C5C5=COUNTIF(And!$C$1:$C$14,"Routine")
D5D5=COUNTIF(Bacon!$C$1:$C$14,"Routine")
B6B6=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
C6C6=AGGREGATE(15,6,And!B1:B14/(Eggs!C1:C14="Routine"),1)
D6D6=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Routine"),1)
B7B7=COUNTIF(Eggs!$C$1:$C$14,"Urgent")
C7C7=COUNTIF(And!$C$1:$C$14,"Urgent")
D7D7=COUNTIF(Bacon!$C$1:$C$14,"Urgent")
B8B8=AGGREGATE(15,6,Eggs!B1:B14/(Eggs!C1:C14="Urgent"),1)
C8C8=AGGREGATE(15,6,And!B1:B14/(And!C1:C14="Urgent"),1)
D8D8=AGGREGATE(15,6,Bacon!B1:B14/(Bacon!C1:C14="Urgent"),1)
C11C11=AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(Eggs!A1:A14="Breakfast")),1)
D11D11=AGGREGATE(15,6,Eggs!B1:B14/((Eggs!C1:C14="Urgent")*(LEFT(Eggs!A1:A14,9)="Breakfast")),1)



example1.xlsx
ABCD
1Breakfast01/01/2019Urgent
2Breakfast menu01/01/2015Routine
3Breakfast menu03/10/2021Routine
4Lunch Menu04/10/2021Urgent
5Lunch Menu05/10/2021Urgent
6Breakfast menu06/10/2021Urgent
7Lunch Menu07/10/2021Routine
8Desert Menu08/10/2021Urgent
9Desert Menu09/10/2021Routine
10Breakfast menu10/10/2021Urgent
11Breakfast menu11/10/2021Routine
12Breakfast menu12/10/2021Routine
13Lunch Menu13/10/2021Routine
14Lunch Menu01/01/2020Routine
15Desert Menu01/05/2019Urgent
16
17
18
19
20
Eggs



example1.xlsx
ABC
1Breakfast menu01 January 2016Urgent
2Breakfast menu02 October 2021Urgent
3Breakfast menu10 October 2010Urgent
4Lunch Menu04 October 2021Urgent
5Lunch Menu05 October 2021Urgent
6Breakfast menu06 October 2021Urgent
7Lunch Menu07 October 2021Urgent
8Desert Menu08 October 2021Urgent
9Desert Menu09 October 2021Urgent
10Breakfast menu10 October 2021Urgent
11Breakfast menu11 October 2021Routine
12Breakfast menu12 October 2021Urgent
13Lunch Menu13 October 2021Urgent
14Lunch Menu01 January 2020Routine
15Desert Menu01 May 2019Urgent
16
And


example1.xlsx
ABC
1Breakfast menu01 October 2021Urgent
2Breakfast menu02 October 2021Routine
3Breakfast menu03 October 2021Routine
4Lunch Menu04 October 2021Routine
5Lunch Menu05 October 2021Routine
6Breakfast menu06 October 2021Routine
7Lunch Menu07 October 2021Routine
8Desert Menu08 October 2021Routine
9Desert Menu09 October 2021Routine
10Breakfast menu10 October 2021Routine
11Breakfast menu11 October 2021Routine
12Breakfast menu12 October 2021Routine
13Lunch Menu13 October 2021Routine
14Lunch Menu01 January 2020Routine
15Desert Menu01 May 2019Routine
Bacon
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
MinIfs Formula (or alternative approach) to find the lowest value per row based on 2 criteria
Replies
3
Views
469
Peter_SSs
Peter_SSs
WaqasTariq
  • Solved
Based on visible rows calculate (COUNTIF, MINIF, AVERAGEIF, MAXIF)
Replies
7
Views
294
Fluff
Fluff
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
369
GeneBF
G
C
  • Solved
Index Matching + Data ranges inside a MINIFS()
Replies
3
Views
1K
Alex Blakenburg
A
duteberta
  • Question Question
Multiple conditional formatting MINIFS
2
Replies
10
Views
133
Cubist
Cubist

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top