Search code examples
excel-formuladynamicconditional-statementsiterationlookup-tables

In Excel 365, can a row of payment data be processed to sum only the qualified payments in that row preferably without VBA or macros?

I'm using Windows 10 with the subscription version of Excel in MS 365.

Problem: I've summarized thousands of payables transactions using a pivot table to show the payments made to each of our vendors over the year. The table is keyed by the vendor ID/name and the payments made to each vendor are in a row following the vendor ID/name. Each vendor payment is classified by a payment code that determines whether a payment is "qualified" to be reported to the IRS. The column headings of the table are the payment codes. Each vendor may have multiple payments under different codes. Each payment code is set to be a qualifying code or not which is recorded in a separate lookup table. This lookup table may be updated frequently.

Desired result: I'm trying to develop a single formula that will process each row of payment data and sum only the payments that are in a column for a "qualified" code from the lookup table. Here is a screenshot of two tables containing example payments and payment codes and the resulting Total Payments and desired Total Qualified Payments. The actual data set I'm working with is about 900 rows by 56 columns.

Screenshot of Excel worksheet with example data sets and desired results.

I'm trying to develop a single formula without the use of VBA or macros to fill down the Total Qualified Payments column. Not sure this is possible. I'm trying to develop the formulas with lambda, scan, reduce, bycol, byrow, and let functions. All of these functions except let are totally new to me. I've gotten pieces of the puzzle working but nothing close to the final solution.

These formulas produce small incremental results. They may be pieces to the much larger puzzle.

=BYROW(ALL_DATA,LAMBDA(row,SUM(row)))

=LET(RESULT,SCAN(0,B3:BB3,LAMBDA(a,v,a+v)),INDEX(RESULT,1,COLUMNS(RESULT)))

=LET(RESULT,REDUCE(0,B3:BB3,LAMBDA(a,v,a+v)),RESULT)

The formula below was modified from a solution posted by David Leal in Nov 2022 for another problem that initially appeared similar to mine. This formula does not execute properly and I think it has a long way to go from this point to solve my problem.

=LET(set, B3:BB25, m, ROWS(set), seq, SEQUENCE(1,m,3),CUMULATE, LAMBDA(x, SCAN(0, x, LAMBDA(acc,item, acc+item))),REDUCE(0,seq, LAMBDA(acc,idx, IF(idx = 1,CUMULATE(INDEX(set,idx)),VSTACK(acc, CUMULATE(INDEX(set,idx)))))))

Solution

  • You can use this formula:

    =LET(qc,FILTER(PaymentCodes[Code],PaymentCodes[Qualified]="yes"),
     SUM(FILTER(PayablesTransaction[@[100]:[180]],
                ISNUMBER(MATCH(PayablesTransaction[[#Headers],[100]:[180]],TEXT(qc,"0"),0)))))

    It first filters the qualified codes.

    And takes these values as criteria for the header row --> returning the valid values per row.

    As the header row is text only, the codes have to be migrated to text as well to work as criteria.

    Within a table you can't use spill formulas!