Excel Magic Trick #236: Excel For Home Improvement

See how to create a database for keeping carpentry measurements, then see Advanced Filter Extract Unique Records and COUNTIF function formula for calculating how many lumber pieces you need to cut for your home improvement project.
Use Excel For Home Improvement Calculations
Custom Number Format (Double Quote, Space, Single Quote, Single Quote, Double Quote): 0.00 " '' "
Create a list to collect data and to automatically bring formatting down as you enter data
The SUBTOTAL function requires that you know the numbering for each function. The easy way to figure this out is to use the Function Arguments Dialog Box and then click on the blue Help link.
Advanced Filter (2003 Data menu, Filter, Advanced Filter; 2007 Data Ribbon, Sort & Filter, Advanced Filter) can extract Unique Records: 1) highlight data (be sure to include Field name/Header); 2) click "Copy to another location", click "Unique records only", select "Copy to" cell.
The COUNIF function will count the number of occurrences in a range of values.

Category:

Science & Technology

Tags:

• Excel
• Table
• List
• Database
• Advanced
• Filter
• COUNTIF
• function
• formula
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[MrHelal123]

if you see the 3:10 mark, you will see a drop own box next to the total, you can change to subtotal to whatever function. If you have a look at your workbook you will see you can change the subtotal to average, count, sum etc...

Thanks

[ExcelIsFun]

If you data is set up with field names in the first row and records in al the other rows and no blanks, use Ctrl + T (2003 Ctrl + L) to convert to table, then Alt + = in cell below last row and the subtotal row will be added automatically. Or if in 2007, you can use ribbon to add total row.

[MrHelal123]

Thank you for all your support

How did you manage to change the subtotal to change to count, sum, average etc...there is a drop down list so that i could change to what ever function but its not done by data validation, how did you do it?

Thank you

[ExcelIsFun]

What minute mark?

You might try this video:

Highline Excel Class 46: CHOOSE function 4 Examples

[Oakley578]

1st comment!!