SumIF Using Table/Named Range Instead of Single Cell Criteria

Question

I have 2 sheets in a workbook (Sheet1, Sheet2).

Sheet 2 contains a table (Named Table1) with 5 columns:

• Takeaways
• Household
• Clothing
• Fuel
• Groceries

On sheet one, I have 2 columns:

• Expense Name
• Expense Total

Now, what I am trying to do is:

1. Set the range for the Expense Name (Range 1)
2. Set the range for the Expense Total (Range 2)
3. Compare Range 1 with the respective column in the table and only add up the values for matches

For example, in Range 1 (B6:B16):

• BP
• Caltex
• McDonalds
• KFC

In Range 2 (C6:C16):

• 300
• 400
• 200
• 150

Now, all I want to do is add up the values for the Takeaways (McDonalds, KFC) and exclude anything that DOES NOT match the criteria.

So my sum total will be all occurrences of Takeaways - provided they are listed in my table - 350 in this case.

But I cannot seem to get the formula to work.

I used these sources:

https://exceljet.net/excel-functions/excel-sumifs-function

Selecting a Specific Column of a Named Range for the SUMIF Function

and ended up with this formula:

=SUMIF($B$6:$B$16;Table1[Takeaways];C6:C16)

This source:

https://excelchamps.com/blog/sumif-sumifs-or-logic/

and ended up with this formula:

=SUM(SUMIFS(C6:C16;B6:B16;Table1[Takeaways]))

Both formulae return 0.

BUT, with BOTH of them, if I change Table1[Takeaways] to "McDonalds", then it correctly identifies every occurrence of the word "McDonalds" in Range 1.

EDIT:

I have updated the formulae above to match the images below.

This is the table that contains the references:

This table contains the data:

Formula:

Cell C4 (Next to Takeaways): =SUMIF($B$6:B$16;Table1[Takeaways];C6:C16)

Cell C5 (Next to Fuel): =SUM(SUMIFS(C6:C16;B6:B16;Table1[Fuel]))

It appears that ONLY BP is being detected in the formula.

This is a an output table when I use the formulae with a single cell reference and not a table or used range:

Formula:

Cell F4 (Next to BP): =SUMIF($B$6:B$16;"BP";C6:C16)

Cell F5 (Next to Caltex): =SUM(SUMIFS(C6:C16;B6:B16;"Caltex"))

Cell F6 (Next to McDonalds): =SUMIF($B$6:B$16;"McDonalds";C6:C16)

Cell F7 (Next to KFC): =SUM(SUMIFS(C6:C16;B6:B16;"KFC"))

Answer 1

If I understand correctly what you're trying to achieve, I think your setup is not right conceptually.

It looks like you're trying to track expenses, and each expense (or payee) is allocated to a category ("Takeaways", "Household" etc.). From a relational-model point of view, your second table (which defines the category for each expense/payee) should only have two columns (or variables): Expense Name and Expense Category.

The table you set up ('Sheet 2') uses the categories (i.e., possible values) as different columns (i.e., variables). But there's only variable, namely the "Expense Category", and the categories themselves are the possible values.

If you set it up like that, the problem changes: you can add a dependent column to your first table that shows the category for each payee (or "Expense Name"), using a VLOOKUP() from the second table.

You can then sum the expenses for all payees matching that category.

Note: I've created the illustration using LibreOffice Calc, so there might be some small differences, but the logic is the same.

Answer 2

Without seeing the data in L and K I can't give you a full answer - but likely it's to do with the way you're pulling your Array

Try something similar to this

=SUMPRODUCT(SUMIFS($L$11:$L$43,$K$11:$K$43,CHOOSE({1,2},Takeaways,"anything else you wanted to sum")))

Remember SUMIFS is for multiple criteria, so if you're only calculating one, you'll need =SUMPRODUCT(SUMIF(

The way the above works is with vertical vectors only, but changing your named ranges so the table of 2 columns is 2 named ranges instead should be okay - unless it's part of your requirements

Table 2 would become expense_Name and expense_Total etc

Answer 3

I was about to close this as a duplicate of my own question here but there is a bit of a difference in using a named range I think. However the logic behind this follows more or less the same approach.

Working further on my partial solution below I derived the following formula:

=SUMPRODUCT(COUNTIF(Table1[Takeaways];Range1)*Range2)

The COUNTIF() part counts the number of occurrences of the cell value in your table. Therefore make sure there are no duplicates in your table. If the value is present in the table the result of COUNTIF() will be 0. This way we create a matrix of 1's and 0's. By multiplying and the use of SUMPRODUCT() we force excel to perform matrix calculations and return the correct result.

---

Partial solution

I used the following formula:

=SUMPRODUCT(ISNUMBER(MATCH(Range1;Table1[Takeaways]))*Range2)

The formula does the following:

• The MATCH()checks if the value in Range1 is present in your table and returns the position of the matching value in your table.
• The ISNUMBER() checks if a match is found by checking if the MATCH() fucntion returned a number
• Multiplying this with Range2 forces matrix calculation, using the SUMPRODUCT() function

EDIT:

This worked for a really limited sample. As soon as I added the fourth row to my data the formula stopped working as intended. See screenshot:

It took the first two values into the sum correctly, the fourth is not taken into account.