Changing a sheet formula to a sheet summary formula

SCavaiani

I am working on a donor management tool and am looking to count the number of donors previously identified as "lapsed donors" who are no longer in lapsed status.

I have created this formula within a column in SmartSheet. It correctly calculates the status of Lapsed Donor change (+1 for new lapse, 0 for no change, -1 for no longer lapsed).

=IF([Donor Category]@row = "Lapsed Donor", 1, 0) - COUNTIFS(INDEX(COLLECT({Donation Date}, {NCHF Donors}, =Donor@row), [Number of Donations]@row - 1), <TODAY(-731), [Donor Category]@row, <>"Lapsed Donor")

Instead of a column, I want to create a sheet summary formula that tallies these numbers. I know I could just create a column sum formula in the summary, but I don't really want the column in the sheet and would rather the full equation be in the sheet summary formula. I'm struggling with the change in formatting when changing something to an "in-sheet" formula to a sheet summary formula. Here's what I have so far:

=COUNTIF([Donor Category]:[Donor Category], ="Lapsed Donor") -COUNTIFS(INDEX(COLLECT({Donation Date}, {NCHF Donors}, =Donor@row), [Number of Donations]:[Number of Donations] - 1), <TODAY(-731), [Donor Category]:[Donor Category], <>"Lapsed Donor")

The first part of the formula works (the first "countif"). All cross-sheet references have been defined as ranges. I'm currently getting an unparseable error. Thanks!

Genevieve P.

Hi @SCavaiani

Since this formula will no longer be placed in a row, we can't use the @row reference:

INDEX(COLLECT({Donation Date}, {NCHF Donors}, =Donor@row),

If you're looking for an overall summary, you could count how many Lapsed Donors are in your current sheet and compare that to how many Lapsed Donors you have in your second sheet:

=COUNTIF([Donor Category]:[Donor Category], ="Lapsed Donor") - COUNTIF({Donor Category}, ="Lapsed Donor")

However based on your description it sounds like you are looking to compare the change per donor, not in general. In this instance having a formula in the row that can compare each donor name (with your Index(Collect formula) is exactly what I would suggest... then yes, a sum of that column in your summary. You can hide the column in your sheet if you don't want to see it and the summary will still calculate.

Let me know if that makes sense!

Cheers,

Genevieve