“By the way, have you used D functions before?”
Saito asked as he poured a beer at in the staff room after we finished our quest. I had heard of it before, but I don’t remember using it.
“No, I don’t think so. What are they?”
“As expected. Even though you’re an Excel expert, there are surprisingly many people who don’t know anything about it. People like you, who worked in advertising areas, tend to be very good with SUMIFS but have never touched D functions.”
“Are they functions unique to Google Spreadsheets or are they also Excel functions?”
“Yeah, they are traditional functions.”
Traditional?
“D functions are database functions to be exact. They include DSUM, DAVERAGE, and others. There’s no need to just think of SQL when it comes to databases. Just think of them as alternative SUMIFS and AVERAGEIFS, written in different ways.”
Saito pulled out a nearby whiteboard and began writing down an explanation.
Name Gender Age
—————- ———– ——-
Takahashi Male 27
Saito Male 41
Inoue Female 29
Compared to what I had seen before, Saito’s age had become younger and Inoue’s age seemed to have changed to something more realistic. I don’t know what the truth is, but there’s no point worrying about it now.
“Suppose this is the data in cells A1:C4. What would you do if you wanted to count the number of men?”
Well, I would normally use COUNTIFS. I wrote the formula onto the whiteboard.
=COUNTIFS(B2:B4, “Male”)
“That’s how you would write it. The way it’s written in a D function is quite different.”
=DCOUNTA(Database, Field, Condition)
=DCOUNTA(A1:C4, A1, E1:G2)
“The database is all your data, including aggregates and conditional columns, the top row must also always contain a header. The field is the header of the column to be aggregated. The most unusual is the last condition.”
Name(E1) Gender Age
————— ———– ————
(Blank) Male (Blank)
“The basic idea is to write the conditional column somewhere, such as E1:G2, and to refer to it.”
“This description sounds complicated.”
“It is certainly true if you’re using it for this level of aggregation. However, the advantage of the formula is that it is compact, even when using complex conditions. There are SUMIFS that are so long that they can’t be read easily if there are too many condition.”
It’s a story I remember. As the number of SUMIFS conditions increased, even the person writing them down became unsure of what was going on.
=SUMIFS(E:E, B:B, “Condition 1”, C:C, “<=Condition 2”, C:C, “Condition 3”, D:D, “Condition 4”)
It was painstaking to create and maintain these formulas. It was also hard to understand because these conditions often included functions as well.
=SUMIFS(E:E,
B:B, “Condition 1”,
C:C, “<=Condition 2”,
C:C, “>Condition 3”,
D:D, “Condition 4”
)
Sometimes I would add line breaks to make it easier to read, but then I had to widen the formula editing field, which was somewhat frustrating. While I was thinking about that, Saito continued.
“Another important note is how to write OR conditions. What would you do in a COUNTIFS if you needed to count the number of men over 40 or women over 25?”
“When would you ever do age based tallies for men and women?”
“Ah? Just assume it’s qualifications to participate in a matchmaking party.”
“Oh, right. Sorry.”
I didn’t feel like asking why Saito had thought of matchmaking parties, so I just wrote down the formula.
=COUNTIFS(B2:B4, “Male”, C2:C4, “>=40”)+COUNTIFS(B2:B4, “Female”, C2:C4, “>=25”)
“That is how I would add two COUNTIFS. But, in the first place, it doesn’t work very well with the OR condition.”
“That’s right. COUNT is fine, but if you wanted to use OR or AVERAGE, you would be stuck. The D functions are compatible with OR in that respect.”
Name Gender Age
———- ———– ——-
(Blank) Male >=40
(Blank) Female >=25
=DCOUNTA(A1:C4, A1, E1:G3)
“By increasing the number of rows in the condition range, it becomes an OR. Even if the conditions become complex, it’s still easy to understand, right?”
That was certainly the case. It seemed to be strong for complex condition aggregation. However, there were some practical issues.
“I understand how it works. But if I actually use it and I want to create a table like this, wouldn’t it be difficult to create each condition in a range of cells?”
E F
Gender Number of People
———— —————————–
Male =COUNTIFS($B$2:$B$4, E2)
Female =COUNTIFS($B$2:$B$4, E3)
COUNTIFS and SUMIFS were rarely used to write conditions in formulas, but they were common in tabulating based on conditions written in other cells.
“That’s the point. Your idea of using it is wrong in the first place.”
Saito drank from the mug in his hand.
“In the advertising area, the basic purpose of Excel is to report to your customers, right? IN that case, you would first create a table with the conditions you want to aggregate, then based on what you have written, you would fill in the numbers using COUNTIFS and SUMIFS. That’s not the case with D functions. You use them when you want to do analysis yourself, trying various things without having decided on the conditions you want to aggregate over. Don’t assume that everyone is using functions to show to customers.”
“Huh…”
“For tomorrow’s quest, I’ll be asking you to process D functions, maybe you should revise the syntax. Well, I’m going to bed now. Good work.”
Saito went back to his room. Since I started working with engineering spreadsheets, I’ve come across many functions I hadn’t known of. I had thought that different jobs used different functions, but now I was painfully aware that even the functions that did come up were all different.
※Functions
DSUM https://support.google.com/docs/answer/3094281
DAVERAGE https://support.google.com/docs/answer/3094144
AVERAGEIFS https://support.google.com/docs/answer/3256534
COUNTIFS https://support.google.com/docs/answer/3256550
DCOUNTA https://support.google.com/docs/answer/3094147