Categorizing rows by their contents in Numbers

I see. As you of course have already discovered that "array formula" flavor of SUMPRODUCT doesn't work in Numbers.

A formula solution in Numbers would likely requires extra columns, one for each of your rules.

For example, for 5 rules you could do something like this:

The formula in B2, filled down:

=INDEX(C$1:G$1,MATCH(1,$C3:$G3,0))

The formula in C2, filled down and right.

=COUNTIFS($A3,C$2)

The rules are laid out in rows 1 and 2. The * before and after the values in row 2 are "wildcards" for COUNTIF, so that text can occur before and/or after in the cell in column A.

The COUNTIF returns 1 if it finds the value in row 2 in the corresponding cell in column A.

The INDEX MATCH combination is a lookup. The MATCH returns the position of the matching cell in the range. The INDEX retrieves the corresponding value from row 1.

Simply extend the range in the formulas as you add more rules.

If you end up having many more rules a script approach would work.

SG

What specific solutions have you found for Excel? How many categories do you have?

SG

The first thing I would suggest is a better match between the "Rule" column on the Rules table and the Description column on the Transactions table. I see NO match for Transaction #3.

The task of assigning categories to transactions would be simplified if you used separate columns to record the vender and the payment method.

Regards,

Barry