The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS), by MyOnlineTrainingHub on YouTube. 10 min 29 s.
- Video length
- 10 min 29 s
- Time to do it
- 15 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Set up your data as a proper table with one header per column and no blank rows. Database functions read the header names to know which field to use.
Build a criteria range in blank cells. Copy the exact header names you want to filter on into one row, then put the values underneath.
For a single condition, type =DSUM(database, field, criteria). Point database at the whole table including headers, field at the column header, and criteria at your criteria range.
For multiple AND criteria, add more header and value pairs side by side in the same criteria row. All conditions must match for a row to count.
For multiple OR criteria, put the extra header and value pairs in a new row below the first. Each row acts as a separate OR condition.
To mix AND and OR, combine both layouts: repeat headers across columns for AND and stack rows for OR.
To filter on a different field with OR logic, add that field's header and value in the second criteria row.
Use wildcards like * and? in criteria values to match partial text, such as all products starting with A.
To return a single matching value instead of a sum, use =DGET(database, field, criteria). It errors if more than one row matches.
Tips and common mistakes
Keep the criteria range headers spelled exactly like the table headers, or the function returns an error.
Leave the criteria value cell blank to match all rows for that field.
DGET returns #NUM! when several rows match, so make the criteria specific enough for one result.
Name your data range with a defined name so formulas stay short and easy to read.
Questions
What is the difference between DSUM and SUMIFS?
DSUM uses a separate criteria range that can hold many conditions, including OR logic, while SUMIFS only handles AND conditions and needs each criterion written into the formula.
Why does my database function return #VALUE!?
The field argument is usually wrong. Use the exact header text in quotes, or the column number within the database range.
Can I use database functions on a filtered table?
Yes, they work on any range. Just make sure the database range includes the header row and the criteria range sits outside it.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
