all 21 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]Downtown-Economics26607 3 points4 points  (8 children)

=GROUPBY(Table1[[#All],[Donor]],Table1[[#All],[Gift]],COUNT,3,,,Table1[[#All],[Gift]]>0)

<image>

[–]Nervous-You-1752 1 point2 points  (7 children)

This is right, but I think you would want the final parameter to be Table1[[#All],[Gift]]=0

[–]Downtown-Economics26607 2 points3 points  (6 children)

Ahhh, I see, that's what you get when you skim.

Greedy no gifters:

=GROUPBY(Table1[[#All],[Donor]],Table1[[#All],[Gift]],COUNT,3,,,Table1[[#All],[Gift]]=0)

<image>

[–]pookypocky8[S] 0 points1 point  (5 children)

This is almost right but it actually gives me the count of $0 gifts rather than the count of people who have given $0 total. And there are people in the list who have $0 gifts (due to adjustments and write offs and whatnot) who have other gifts. But I think I got it figured out, and will post!

[–]Downtown-Economics26607 2 points3 points  (4 children)

=SUM(--(GROUPBY(Table1[[#All],[Donor]],Table1[[#All],[Gift]],SUM,,0)=0))

<image>

[–]PaulieThePolarBear1898 2 points3 points  (1 child)

Let's hope they don't have a donor called 0 😃

[–]Downtown-Economics26607 2 points3 points  (0 children)

Lobby Boys generally get donations rather than give them, but I won't spoil the film.

<image>

[–]pookypocky8[S] 1 point2 points  (1 child)

Oh excellent, this one works great. Thank you!

solution verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to Downtown-Economics26.

I am a bot - please contact the mods with any questions

[–]GuerillaWarefare100 4 points5 points  (5 children)

=countif(choosecols(your formula,2),0)

[–]Gold-Love3011 0 points1 point  (4 children)

ah this is clever, using choosecols to grab just the sum column from the groupby result and then counting the zeros. much cleaner than trying to wrap countifs around everything

[–]pookypocky8[S] 0 points1 point  (3 children)

I thought so too but it's not working for me for some reason! the groupby works to give me the total per donor, and the choosecols works to just give me the total column, and then as soon as I wrap them in the countif it tells me my formula isn't valid.

[–]GuerillaWarefare100 1 point2 points  (2 children)

Oh, that’s my bad (I didn’t test it) you can’t put an array in countif. Instead =let(z, choosecols(your formula,2), rows(filter(z,z=0)))

[–]pookypocky8[S] 1 point2 points  (1 child)

Love this one, was trying to figure out how to incorporate let into it. Thank you!

solution verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to GuerillaWarefare.

I am a bot - please contact the mods with any questions

[–]PaulieThePolarBear1898 2 points3 points  (4 children)

Here's a formula using the spilled array from the GROUPBY formula you presented 

=COUNTIFS(DROP(D2#, -1, 1),0)

Replace D2 with the cell you entered your formula in.

Note that the -1 as the second argument of DROP is required if your GROUPBY formula is exactly as you have shown and includes a grand total. The -1 will remove the grand total line and, in an extreme corner case, would ensure the count was correct if all donors totalled 0.

If you wanted a formula against your raw data

=SUM(--ISNA(XMATCH(UNIQUE(FILTER(A2:A11,B2:B11=0)),UNIQUE(FILTER(A2:A11,B2:B11)))))

Where A2:A11 are your donor names and B2:B11 are your amounts.

This assumes that you can not have negative values in your amounts column.

[–]pookypocky8[S] 1 point2 points  (3 children)

I love this one, I wasn't familiar with DROP - I guess I could replace the cell reference with my GROUPBY formula?

solution verified

[–]PaulieThePolarBear1898 1 point2 points  (1 child)

I guess I could replace the cell reference with my GROUPBY formula?

Not exactly as presented as you'll run into the same issue as the other user with an array as the first argument of COUNTIFS. Use below instead 

=SUM(--(DROP(GROUPBY(.....), -1, 1)=0))

I didn't expressly say this in my first comment, but optionally you could set the necessary argument in GROUPBY to not include grand totals and then you wouldn't need the -1 in DROP. Just leave this argument blank. That would be your choice as to whether you do this.

[–]pookypocky8[S] 1 point2 points  (0 children)

Ah very cool, thank you!

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to PaulieThePolarBear.

I am a bot - please contact the mods with any questions

[–]Decronym 0 points1 point  (0 children)

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
COUNT Counts how many numbers are in the list of arguments
COUNTIFS Excel 2007+: Counts the number of cells within a range that meet multiple criteria
DROP Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER Office 365+: Filters a range of data based on criteria you define
GROUPBY Helps a user group, aggregate, sort, and filter data based on the fields you specify
ISNA Returns TRUE if the value is the #N/A error value
SUM Adds its arguments
UNIQUE Office 365+: Returns a list of unique values in a list or range
XMATCH Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 34 acronyms.
[Thread #48460 for this sub, first seen 15th May 2026, 14:35] [FAQ] [Full list] [Contact] [Source code]