I explored ideas to make DSUM function’s criteria argument as flexible as a gymnast. Is it a spreadsheet miracle? Maybe.
(Spreadsheet Miracle: see bottom of post for info on artist Kim Murton)
Excel Files
I used two files: (1) using original data here and (2) cleaned up source data here.
Why DSUM?
It’s a database function that sums values based on sets of conditions. Sum a column based on these conditions or these conditions or these conditions, etc
It’s easy to modify the conditions. What else can do that?
SUMIFS and Pivots are great but not as flexible as DSUM if you need to modify conditions quickly and easily. I’ll show you tricks to make DSUM even more flexible.
The Dataset
The Blockbuster database dataset turned out to be tricky to work with. Why?
- Column studio can have multiple studios in the same cell (they collaborate on a movie). There’s no indication how earnings were shared.
- Studio column had “Fox” and “20th Century Fox”. Is it the same studio?
- Column genre can list multiple genres in a cell. Maybe I should have used column Genre_1.
This created issues with double counting/summing but it was still a fun dataset to play with.
DSUM recap
Sheet DSUM 1: cell H9 has a basic DSUM example.
It has 3 arguments:
- database the range of entire dataset
- field the column to sum
- criteria based on conditions we specify
Cell H9 sums column worldwide_gross when column genre contains Comedy and column studio contains 20th Century Fox. Note: I didn’t say equals as I surround the text with wildcards (asterisks).
DSUM multiple criteria
Sheet DSUM 2: cell H11 sums column worldwide_gross when:
genres contains comedy and studio contains 20th Century Fox
or
genres contains crime and studio contains Marvel
or
genres contains thriller and studio contains Lionsgate Films
As the formula is dragged down the criteria range expands.
DSUM gone wild!
Sheet DSUM 3: in cell H11 only studio column is currently used as criteria. It’s both start & stop columns but it’s easy to change using the data validation in cells H4 & H5.
Criteria part:
INDEX($B$8:$E$14,1,MATCH(H$4,$B$8:$E$8,0)):INDEX($B$9:$E$14,A11,MATCH(H$5,$B$8:$E$8,0))
What does it do? It restricts & expands criteria values. Seeing is believing:
- inside cell H11 highlight criteria part and press F9 key to see criteria
- press escape key and change cell H5 to year
- repeat step 1 to see that columns studio, worldwide_gross, and year are all part of the criteria
Warning: now I take it to another crazy level. You’ll never need this but I wanted to know if I could do it!
DSUM Miracle?
Sheet DSUM 4: the limitation in sheet DSUM 3 is that you select start & end columns and all columns in-between are included. How can I allow any combination of columns?
Checkboxes! Checked: lower area displays the value. Not checked: it displays all values (row 16).
Row 16 “any” text or number values
How can I explain my journey in creating the all values in row 16 above? I’ll try…
I found some syntax ideas from Mr Excel’s awesome message board!
You can select any column in cells B8:E8. I needed a way to dynamically change the any value (=* for text, <>0 for numbers) for unchecked columns. This INDEX MATCH retrieves the correct value:
=INDEX(‘currency column list’!$A$20:$T$20,MATCH(C$8,’blockbuster-top_ten_movies_per_’!$A$1:$T$1,0))
Audit it and see how it works. It depends on these formulas in other sheets:
=CELL(“format”,’blockbuster-top_ten_movies_per_’!A2)
=INDEX(codes!$E$2:$E$23,MATCH(LEFT(A1,1),codes!$C$2:$C$23,0))
My solution wasn’t complex but rather creative. I used CELL function to extract & use column formats and assign the corresponding any syntax. Was it a miracle? No, but it was fun to create this and discover something that I didn’t know was possible!
Source
Google search “Excel Dsum Criteria Is Blank” led me to Mr Excel’s amazing forum. Answers from Excel legends Aladin Akyurek and mikerickson inspired the wild & miracle sections.
Spreadsheet Miracle?
A special thanks to artist Kim Murton who gave me permission to use her “Spreadsheet Miracle” drawing. I really like Kim’s unique drawing style and her pottery is incredible. I bought her book Mindless Comic. It arrives on Tuesday!
About Me
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
While I was writing this post I heard a song on Spotify called “The Spreadsheet“. That was unexpected and kind of weird.
Anyway, a couple of weeks ago I started to wonder how flexible the criteria argument was for Excel’s database functions. It led me down a path of research and experimentation.