Alternative Method to Editing Criteria Range in SUMIFS() Function

[blueberrywaffle]

Hello,

In its basic form, I have a Text_Cell and then a separate Table consisting of numbers in Column1, text in Column2, and other columns of data which is why I am currently using a SUMIFS() function. What I want to do is sum the values of Column1 based off of multiple conditions in the following columns of data.

For one of these conditions, if the first three letters in the Text_Cell exactly match the first three letters in Column2 then I want it to output the value in Column1 into my sum equation. I have been using:

SUMIFS(Table[Column1], LEFT(Table[Column2],3), LEFT(Text_Cell,3)...other conditional clauses) but the SUMIFS function errors when I try to nest another function for its criteria range.

If I create a new column (lets call it Column3), perform a =LEFT(Table[Column2],3) and then use:

SUMIFS(Table[Column1], Table[Column3], LEFT(Text_Cell,3)...other conditional clauses) it works exactly how I want it to. However, I do not want to have to create additional tables for each referenced column as I am working with a number of sheet.

Is there an alternative method or better approach this issue?

Thanks so much for the help!!

 

[Marcelo Branco]

Welcome to Mr Excel

Instead of SUMIFS try SUMPRODUCT.

Something like this
=SUMPRODUCT(Table[Column1],--(LEFT(Table[Column2],3)=LEFT(Text_Cell,3)))

Hope this helps

M.
ps: take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Eric W]

Your problem is that SUMIFS does not allow a function in the range fields. That is, the definition of SUMIFS is SUMIFS(table,range1,condition1,range2,condition2, . . .) and you have LEFT(Table[Column2],3), in a range field. Only something like Column2 is allowed. As you found, making a helper column can work.

One other possibility is to use the SUMPRODUCT function.
=SUMPRODUCT(Table[Column1],(LEFT(Text_Cell,3)=LEFT(Table[Column2],3))+0)

The (LEFT(Text_Cell,3)=LEFT(Table[Column2],3)) creates an array of TRUEs and FALSEs depending on the condition, the +0 converts TRUEs to 1s and FALSEs to 0, then the SUMPRODUCT multiplies that table by the Table[Column1] and sums it. You can add as many conditions as you like, but the format of the conditions will be somewhat different from SUMIFS.