Sum from multiple columns with criteria

[helpme10]

Hey all,

I inherited a workbook that is a bit disorganized, but we must still use it for now for the customers' sake. Rather than unique rows for each data point, it collects data horizontally in three groups of columns (so one unique ID or "transaction" produces three data points). This makes a lookup-type sum quite lengthy, as in SUMIFS()+SUMIFS()+SUMIFS()...

Is there a more efficient way to find these values in multiple columns, with multiple criteria, and sum them? I have a workbook to share in order to illustrate the point, but it seems I can't attach files here. I'll try to simplify it here:

Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Genus1[/TD]
[TD]Location1[/TD]
[TD]Ratio1[/TD]
[TD]Genus2[/TD]
[TD]Location2[/TD]
[TD]Ratio2[/TD]
[TD]Genus3[/TD]
[TD]Location3[/TD]
[TD]Ratio3[/TD]
[/TR]
[TR]
[TD]7877[/TD]
[TD]****[/TD]
[TD]Eurasia[/TD]
[TD]0.06[/TD]
[TD]Papio[/TD]
[TD]Asia[/TD]
[TD]0.06[/TD]
[TD]Papio[/TD]
[TD]Africa[/TD]
[TD]0.09[/TD]
[/TR]
[TR]
[TD]7816[/TD]
[TD]Canis[/TD]
[TD]Antarctica[/TD]
[TD]0.59[/TD]
[TD]Papio[/TD]
[TD]S. America[/TD]
[TD]0.74[/TD]
[TD]Papio[/TD]
[TD]S. America[/TD]
[TD]0.41[/TD]
[/TR]
[TR]
[TD]6430[/TD]
[TD]Felis[/TD]
[TD]Antartica[/TD]
[TD]0.6[/TD]
[TD]Papio[/TD]
[TD]Asia[/TD]
[TD]0.37[/TD]
[TD]Felis[/TD]
[TD]Europe[/TD]
[TD]0.85[/TD]
[/TR]
[TR]
[TD]794[/TD]
[TD]Canis[/TD]
[TD]Asia[/TD]
[TD]0.73[/TD]
[TD]Canis[/TD]
[TD]N. America[/TD]
[TD]0.49[/TD]
[TD]Canis[/TD]
[TD]S. America[/TD]
[TD]0.86[/TD]
[/TR]
[TR]
[TD]9571[/TD]
[TD]Canis[/TD]
[TD]Asia[/TD]
[TD]0.99[/TD]
[TD]Orycteropus[/TD]
[TD]Africa[/TD]
[TD]0.24[/TD]
[TD]Papio[/TD]
[TD]Asia[/TD]
[TD]0.31[/TD]
[/TR]
</tbody>[/TABLE]
Query
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Genus[/TD]
[TD]Location[/TD]
[TD]Sum of %[/TD]
[/TR]
[TR]
[TD]Felis[/TD]
[TD]Antarctica[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]Canis[/TD]
[TD]Asia[/TD]
[TD]1.72[/TD]
[/TR]
[TR]
[TD]Papio[/TD]
[TD]Asia[/TD]
[TD]0.74[/TD]
[/TR]
</tbody>[/TABLE]

The formula for 'Sum of %' is basically

=SUMIFS(Data!D:D,Data!B:B,A2,Data!C:C,B2)+SUMIFS(Data!G:G,Data!E:E,A2,Data!F:F,B2)+SUMIFS(Data!J:J,Data!H:H,A2,Data!I:I,B2)

I'm not great with OFFSET() or SUMPRODUCT() yet, but I feel the answer lies somewhere between the two.

Thanks!

 

[Eric W]

Re: Simplification: Sum from multiple columns with criteria

Try:

=SUM(IF((Data!$B$2:$H$6=A2)*(Data!$C$2:$I$6=B2),Data!$D$2:$J$6))
with Control+Shift+Enter

note that the ranges are offset from each other.


Edit: I noticed that you are asking about efficiency. The formula I provided is shorter, and perhaps easier to understand, but I don't know if it is more efficient. It's guaranteed to look at many, many cells that we know won't be a match. That's one reason it needs the row references on the ranges instead of a whole column reference. If you're looking for "efficiency", or speed of execution, I think it's likely that your existing formula, long as it is, will calculate quicker. (SUMIFS can use whole column references without loss of efficiency.) You may want to run some tests to see which of the formulas, if either, slows it down more.

Good luck!

 

[helpme10]

Re: Simplification: Sum from multiple columns with criteria

Thanks for the formula Eric! But your hunch was correct, Excel ran out of resources while attempting to calculate an entire column of that array formula. For a single row it worked fine, but I'll need them for the entire column.