Statistics › MINIFS, MAXIFS28 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeMINIFSMAXIFS

MINIFS & MAXIFS: comparing supplier quotes

Intan works in procurement at a notary office in Bogor. She received stationery price quotes from three shops and wants the cheapest and the most expensive price for each item.

Task

Fill F2:F4 with the cheapest price using MINIFS and G2:G4 with the most expensive price using MAXIFS, for the items in E2:E4.

Cells to fillF2:F4G2:G4

Required functionsMINIFSMAXIFS

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Like SUMIFS, the result range comes first: MINIFS(value_range, criteria_range, criteria).
Hint 2
Value range = the Price column (C2:C10), criteria range = the Item column (A2:A10), criteria = E2.
Hint 3
MAXIFS takes exactly the same arguments. Lock the data ranges with $ before copying.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F2:F4

=MINIFS($C$2:$C$10,$A$2:$A$10,E2)

Write it in F2, then fill down to F4.

G2:G4

=MAXIFS($C$2:$C$10,$A$2:$A$10,E2)

Write it in G2, then fill down to G4.

Explanation

=MINIFS($C$2:$C$10,$A$2:$A$10,E2) finds the smallest price only among rows whose item matches E2, and MAXIFS finds the largest. The gap between them shows how much you can save, for example Rp13,000 per bottle of ink. Both functions are available since Excel 2019. If no row matches, the result is 0 rather than an error, so make sure item names are spelled exactly the same.

Starting worksheet: Sheet1
Row	A	B	C	E	F	G
1	Item	Shop	Price	Item	Cheapest	Highest
2	A4 paper (ream)	Toko Sinar	52,000	A4 paper (ream)
3	Printer ink	Toko Sinar	85,000	Printer ink
4	Plastic folders (dozen)	Toko Sinar	18,000	Plastic folders (dozen)
5	A4 paper (ream)	CV ATK Jaya	49,500
6	Printer ink	CV ATK Jaya	92,000
7	Plastic folders (dozen)	CV ATK Jaya	16,500
8	A4 paper (ream)	UD Berkah	51,000
9	Printer ink	UD Berkah	79,000
10	Plastic folders (dozen)	UD Berkah	19,000
11
12
13
14

Fill in the requested cells, then press Check answer.

Validation result