IF - one result or another
=IF(condition, value_if_true, value_if_false)
=IF(C16>24, "ok", "under age") shows ok for anyone older than 24.
Garment use: =IF(Efficiency>=60%, "On target", "Below") in a line report.
SUMIF - add only what meets a condition
=SUMIF(range, criteria, sum_range)
=SUMIF(D15:D19, ">24", D15:D19) adds the ages above 24. More useful: =SUMIF(Line, "Line-3", Output) gives the total output of Line 3 from a daily data sheet.
COUNTIF - count what meets a condition
=COUNTIF(E16:E20, ">24") counts people older than 24. =COUNTIF(Status, "Absent") counts absent operators.
Tip: For more than one condition use SUMIFS and COUNTIFS:
=SUMIFS(Output, Line, "Line-3", Date, TODAY()).
FREQUENCY - how many fall in each band
=FREQUENCY(data, bins) returns how many values fall into each range. Example: efficiency bands 40, 50, 60, 70, 80 → how many operators are in each band. It is an array formula: select the output cells, type the formula and press Ctrl + Shift + Enter (Excel 365 does this by itself).
Pivot tables - summaries in seconds
A Pivot table combines and compares large data without formulas. From a list of people with Name, Age, Address and Salary you can show total salary by address, with names under each and ages across the top - in about ten clicks.
- Click inside the data → Insert → PivotTable.
- Drag Address and Name to Rows, Age to Columns, Salary to Values.
- Refresh after the data changes.
Uses: output by line and date, defects by type and line, absenteeism by section - many reports from one database.
Combining formulas - remaining quantity
Lesson 13 of the workbook combines three formulas to show the balance still to produce for each order line:
=IF(H12="close", "", IF($G12 - SUMIF(F12:F15, CONCATENATE(B12,D12,E12), C12:C15) < 0, 0, $G12 - SUMIF(F12:F15, CONCATENATE(B12,D12,E12), C12:C15)))
Read it from the inside out:
- CONCATENATE joins style, colour and size into one key.
- SUMIF adds everything already produced for that key.
- Order quantity minus produced = balance; if it is negative show 0.
- IF hides the result when the order status is "close".
Combine IF with TODAY to flag deadlines: =IF(B9-10=TODAY(), "yes", "no") shows yes exactly ten days before the date in B9.