Share via

Formula to sum array of values for matching vlookup results

Anonymous
2023-12-27T18:48:09+00:00

Trying to summarize values based on category matches without using helper cells

Example:

| Charges to Sum | | Category Lookup | | Desired Output | | company | amount | | company | category | | category | sum | | --- | --- | --- | --- | --- | --- | --- | --- | | A | 100 | | A | Utility | | Utility | 110 | | B | 50 | | B | Credit Card | | Credit Card | 50 | | C | 200 | | C | Loan | | Loan | 275 | | D | 10 | | D | Utility | | | | | E | 75 | | E | Loan | | | |

I've been able to accomplish this using 'helper' cells (the extra vlookup column shown below) but would like to have a single formula to do the summarization instead, I've tried various combinations of XLOOKUP, SUMIF, SUMIFS, VLOOKUP, just can't seem to get the correct combination.

| Charges to Sum | | Category Lookup | | Desired Output | | company | vlookup | amount | | company | category | | category | sum | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | A | =vlookup(a3,$e$3:$f$7,2,false) | 100 | | A | Utility | | Utility | =sumif(b3:b7,"="&h3,c3:c7) | | B | | 50 | | B | Credit Card | | Credit Card | 50 | | C | | 200 | | C | Loan | | Loan | 275 | | D | | 10 | | D | Utility | | | | | E | | 75 | | E | Loan | | | |

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-12-27T19:49:03+00:00

    Since the company names in the Chares to Sum range and Category Lookup range are the same, in the same order, you might as well treat them as one range, You can then use a standard SUMIF or SUMIFS formula:

    .

    If that is not feasible, you can use a combination of SUM, XLOOKUP and FILTER:

    .

    Or a more traditional formula involving VLOOKUP:

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-27T21:56:13+00:00

    FILTER!!!!! That's what I was missing. For those who may review this thread in the future, I did need to make one small adjustment - added the ",0" to the XLOOKUP function for the 'If not found' parameter because I was getting an error. My end function looks like this:

    =SUM(XLOOKUP(FILTER($D$3:$D$7,$E3:$E7=G3),$A$3:$A$7,$B$3:$B$7**,0**))

    Thank you @Hansv!

    0 comments