Summing the results of a lookup when the function arguments contain functions

1
votes

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(First, " ", Last),FullNames,Sales/Month))

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:

=SUM(SUMIF(First,FirstNameSubSet,Sales))

When I don't do any array concatenation or division in the formula.

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.

2
excelexcel-formulaarray-formulas

2 Answers

0
votes

Do you want the result to be all in one cell, or in one cell per row? If not, I am unsure why you are adding down a column (A1:A3).

As another note, if you are only avoiding computational cells/columns for appearance, you could put them on a separate sheet that is hidden.

I would've added this as a comment, but don't have enough reputation.

0
votes

It would be a lot simpler if the full name were also in two separate cells. You could use a simple SUMIFS. But for the problem you present, if I understand it, given the following:

  • First is the named range containing the first names
  • Last is the named range containing the last names
  • Sales is the named range containing the Sales amounts
  • Month is the named range containing the number of months.
  • Fullnames is the named range containing the full name (combined first and last name)

Then the following array-entered formula (formula entered by holding down ctrl-shift while hitting enter) should produced the sales per month

=SUM(SUMIFS(Sales,First,LEFT(FullNames,FIND(" ",FullNames)-1),Last,MID(FullNames,FIND(" ",FullNames)+1,99)))
/SUM(SUMIFS(Months,First,LEFT(FullNames,FIND(" ",FullNames)-1),Last,MID(FullNames,FIND(" ",FullNames)+1,99)))

If you need the name comparisons to be case insensitive then change FIND to SEARCH in the formula.