In the sumif function, can the range value be set to multiple columns?

WDellapenna

I have one spreadsheet where I have parts listed for various assemblies. Each row in a different assembly and there are a variety of parts listed for each along that row. Each separate assembly (row) may use some of the same parts from other assemblies, or may not. In the fist column of the row is the total quantity of that assembly

In another spreadsheet, I want to make a bill of material of all the parts needed for all assemblies. I am doing this by using the sumif function to add the quantity if the referenced part appears in the parts list for that assembly. However, the individual part may appear in any of the several columns.

I want to make the range variable in the sumif function reference multiple columns but when I try to set it to multiple columns I get an error.

Paul Newcome

I would suggest a helper column in the source sheet. It would be a dropdown column with multiple selections possible. Then you would use this formula:

=JOIN(COLLECT([1st Part Column]@row:[Last Part Column]@row, [1st Part Column]@row:[Last Part Column]@row, @cell <> ""), CHAR(10))

Then your SUMIFS in the second sheet would end up being something along the lines of

=SUMIFS({QTY Column}, {Helper Dropdown Column}, HAS(@cell, [Part Name]@row))

Paul Newcome

Are you able to provide some screenshots for context?

WDellapenna

This is the spreadsheet with my assemblies. Each row is a different assembly, and you can see the quantities in the first column. The parts lists start with the third column (Bracket Type) and continue for several columns off screen.

Here's a screenshot of a separate spreadsheet where I'm trying to create a complete Bill of Material to build all assemblies. You can see the sumif formula I have now. I'm trying to sum the quantity column of the first spreadsheet if the part appears in that assembly's part list.

Kerry St. Thomas

What are you counting? If you are counting the # of parts - the instance each part appears in your set of columns - you can just do SUMIF(first column formula)+SUMIF(second column formula) and so on. If you're counting ROWS - the number assemblies that such-and-such part might be in, regardless of whether said part is in column A or column B or column C (or any variation thereof)… my first thought is you might wish to use a helper column to flatten all those columns into one. Then that way you can use COUNT(COLLECT(CONTAINS(…))) as your framework for the formula.

Good luck!

Paul Newcome

I would suggest a helper column in the source sheet. It would be a dropdown column with multiple selections possible. Then you would use this formula:

=JOIN(COLLECT([1st Part Column]@row:[Last Part Column]@row, [1st Part Column]@row:[Last Part Column]@row, @cell <> ""), CHAR(10))

Then your SUMIFS in the second sheet would end up being something along the lines of

=SUMIFS({QTY Column}, {Helper Dropdown Column}, HAS(@cell, [Part Name]@row))

WDellapenna

Paul,

This is very helpful, thanks.

Can you help me understand the purpose of the collect() function in the formula you suggested? Would I not be able to achieve what I want with:

=JOIN([1st Part Column]@row:[Last Part Column]@row, CHAR(10))

Paul Newcome

The COLLECT function filters out blanks. Technically, the multi-select dropdown would filter that out for you, but I still use the COLLECT function because any other situation would have repeating delimiters in place of blank cells. It is more of a "best practice" in this particular instance as opposed to being necessary.

WDellapenna

This is all very helpful. I've gotten my formulae working now, but I have 1 further problem. Sometimes a single part might show up multiple times within a single assembly. In those cases, it'll show up twice (or more) in my new collected helper column. However, the sumifs function is only counting it once because it's only checking if the part appears at all, not how many times it appears.

Any suggestions how to sum for each time that part shows on a single row?

Paul Newcome

How many different columns are you summarizing?

WDellapenna

Right now there are 11 columns, but that could expand to as much as 20. The same part might appear in as many as 3 columns, in which case I'd want to count it 3 times.

It looks to me like the JOIN() function is only including a single instance of a part if it appears multiple times.