SUMIFS with Cell Reference for Multiple Criteria List

[mmuller]

I have a series of formulas to calulate values from a table, and there may be one or more criteria for a given criteria range. For instance, there are two criteria listed for the Species range and three for the Product range in the example below:

=SUM(SUMIFS(DataTable[Volume],DataTable[Species],{"Hardwood","Pine"},DataTable[Product],{"Poles*","Sawtimber*","Pulp*"},DataTable[Month],$B$3))

I would like to replace the Species and Product criteria lists each with a single cell reference where the lists are maintained (cells B10 and B11, respectively). In essence, the formula would look something like:

=SUM(SUMIFS(DataTable[Volume],DataTable[Species],B10,DataTable[Product],B11,DataTable[Month],$B$3))


Is it possible to get this to work? I am beginning to have my doubts, but I may have the syntax wrong... Here is my latest attempt that still fails:

Cell B10: "Hardwood","Pine"
Cell B11: "Poles*","Sawtimber*","Pulp*"

=SUM(SUMIFS(DataTable[Volume],DataTable[Species],"{"&B10&"}",DataTable[Product],"{"&B11&"}",DataTable[Month],$B$3))


Cheers!

 

[XOR LX]

Hi,

Why in a single cell, if you don't mind me asking? It's possible, but messy, since effectively you would have to perform string extractions on the cell value.

Why not just in separate cells, which is straightforward?

Regards

 

[Jonmo1]

Welcome to the board.

First:

=SUM(SUMIFS(DataTable[Volume],DataTable[Species],{"Hardwood","Pine"},DataTable[Product],{"Poles*","Sawtimber*","Pulp*"},DataTable[Month],$B$3))

That doesn't work quite the way you think it does.
You should look closely at the data and compare the math in your head vs the result of the formula.

If you have 2 sets of multiple criteria, this syntax doesn't work as you would think
You have to transpose 1 of the sets of criteria, useing ; instead of , in the {}
So it should be

=SUM(SUMIFS(DataTable[Volume],DataTable[Species],{"Hardwood";"Pine"},DataTable[Product],{"Poles*","Sawtimber*","Pulp*"},DataTable[Month],$B$3))


2nd: I don't think you can put that criteria string {"..",".."} into a single cell.
However, you can put each criteria into it's own cell, creating lists..
But again, 1 must be transposed.
So 1 range is say A1:A5 (a column) while the other is C2:E2 (a row)
Then you can use

=SUMPRODUCT(SUMIFS(rangetosum,criteria1range,A1:A5,criteria2range,C2:E2))


Hope that helps.

 

[platonic567]

Care to post a little sample data?

 

[mmuller]

Thanks Jonmo1. I fully agree on the use of the semicolon to get the correct value when there are two sets of multiple criteria.

The truth is that I got a little overzealous with my example, and on my actual worksheet there are no cases that require multiple sets of multiple criteria. In most cases it will simply be one species and one product OR multiple species or multiple products without the other criteria.

Apologies for any confusion!

 

[mmuller]

Care to post a little sample data?

I would love to, but the link to d/l the MrExcel HTML Maker is broken! The sample would look something like this with many more records, of course...

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Species[/TD]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]Hardwood[/TD]
[TD]Pulp-Thin[/TD]
[TD]5456[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Hardwood[/TD]
[TD]CNS[/TD]
[TD]253[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Pine[/TD]
[TD]Sawtimber[/TD]
[TD]8964[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[platonic567]

[TABLE="width: 230"]
<tbody>[TR]
[TD="width: 21, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"]A[/TD]
[TD="width: 74, bgcolor: transparent"]B[/TD]
[TD="width: 56, bgcolor: transparent"]C[/TD]
[TD="width: 48, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Species[/TD]
[TD="bgcolor: transparent"]Product[/TD]
[TD="bgcolor: transparent"]Volume[/TD]
[TD="bgcolor: transparent"]Month[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]Hardwood[/TD]
[TD="bgcolor: transparent"]Pulp-Thin[/TD]
[TD="bgcolor: transparent, align: right"]5456[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Dbe[/TD]
[TD="bgcolor: transparent"]CNS[/TD]
[TD="bgcolor: transparent, align: right"]253[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Pine[/TD]
[TD="bgcolor: transparent"]Sawtimber[/TD]
[TD="bgcolor: transparent, align: right"]8964[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Abc[/TD]
[TD="bgcolor: transparent"]Pulp-Thin[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Hardwood[/TD]
[TD="bgcolor: transparent"]CNS[/TD]
[TD="bgcolor: transparent, align: right"]658[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]Pine[/TD]
[TD="bgcolor: transparent"]Sawtimber[/TD]
[TD="bgcolor: transparent, align: right"]7453[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]Hardwood, Pine[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"]Pulp,Sawtimber[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

=SUMPRODUCT(--(ISNUMBER(SEARCH(LEFT(B2:B7,4),A10))),--(ISNUMBER(SEARCH(A2:A7,A9))),--(D2:D7=A8),C2:C7)