Array manipulation in Excel formula arguments

Question

my question is pretty specific, and I haven't been able to find a solution. Here's a sample dataset that illustrates the problem:

First Last Sales Months
Kevin Smith 500ドル 10
Joe Stevens 400ドル 6
Frank Doe 600ドル 4

I am looking for a solution that doesn't involve any computation columns or cells in the final result.

Now lets say I had this list

Kevin Smith
Frank Doe

I want to sum their sales/month in a separate cell.

I've tried:

=SUM(SUMIF(CONCATENATE(A1:A3, " ", B1:B3),A5:A6,C1:C3/D1:D3))

Data is stored in rows 1-3 where column A is first name, Column B is last name, Column C is sales, and column D is months. The full names are in A5 and A6.

When I apply the function I have tried both Enter and Ctrl+Shift+Enter

Strangely enough, this formula works when I don't do any array concatenation or division in the formula. That's really the crux of this question. Why can't I do the array manipulation in the formula?

=SUM(SUMIF(A1:A3,A8:A9,C1:C3))

Unfortunately, in my real life problem, I can't use this workaround.

I tried posting a picture, but even though I've been reading the answers on this site for a long time, I've never posted anything so I have no 'reputation.'

Thank you in advance for you help.

Answer 1

SUMIF doesn't allow you to apply any functions to the criteria or sum ranges (because that makes them arrays and SUMIF only allows ranges)....but you can use a SUMPRODUCT formula like this to avoid any extra cells/columns and also avoid "array entry"

=SUMPRODUCT(ISNUMBER(MATCH(A1:A3&" "&B1:B3,A5:A6,0))+0,C1:C3/D1:D3)

MATCH function matches the concatenated name columns against your names in A5:A6 and returns either a number (if there's a match) or an error - #N/A - if there isn't a match, so applying ISNUMBER function to that array gives an array of TRUE/FALSE values which +0 converts to 1/0 values. SUMPRODUCT then multiplies that array with the C/D division and sums the results, giving the required answer.

You can't have any text in the C1:C3 or D1:D3 ranges otherwise you'll get an error

• Kevin Presley: That's an awesome workaround, thanks, Barry. What if I want to have text in that column?
• barry houdini: Then I would revert to an "array formula" that will filter out any rows with text, e.g. =SUM(IF(ISNUMBER(MATCH(A1:A3&" "&B1:B3,A5:A6,0)*C1:C3/D1:D3),C1:C3/D1:D3)) - confirmed with CTRL+SHIFT+ENTER

Answer 2

Unfortunately, because of the way the formulas are implemented, you will need additional computational columns, unless you want to do some programming.

To use just a formula, you will need a column for the concatenated names and a column for the amounts to add together (C2/D2). Then you can use the array formula: =SUM(SUMIF(A1:A3,A8:A9,C1:C3))