Search code examples
arraysexcelsumifs

array based sumif in excel by formula

So I m trying to find some alternative to sumifs in excel where each condition needs to be checked in a 2D range instead of a 1D range.

For example, in the below table I want the sum of values in column V for rows where A12 ("IJ") is present in range A2:C8 (P), B12 ("NM") is present in the range D2:F8 (S) and C12 ("XX") is present in range G2:I8 (A)

enter image description here

I am trying to find a solution involving an array-based formula (without VBA). Like for example in the below-given formulas, SUMPRODUCT((B2:B8'=A12)*J2:J8) will give an array-based calculation as follows 

SUMPRODUCT({TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{22;79;45;67;43;72;52}) 
= SUMPRODUCT({22;0;45;0;0;72;0})
=139

It is easy when there is only one condition needs to be checked but like sumifs, I intend to check multiple conditions, but as soon as I add other conditions, the array becomes multidimensional and gives the wrong answer.

Example:

SUMPRODUCT((A2:C8=A12)*(D2:F8=B12)*J2:J8) breaks down to 

=SUMPRODUCT(
{FALSE,TRUE,FALSE;FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FALSE}*
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FALSE;FALSE,FALSE,FALSE}
*J2:J8)

in the background what is happening is (example for 3rd row)

SUMPRODUCT( ({FALSE, TRUE ,FALSE} * {TRUE,FALSE,FALSE}) * 45 )

= SUMPRODUCT({FALSE,FALSE,FALSE} *45 )

=0


SUMPRODUCT(({FALSE,TRUE ,FALSE} + {TRUE,FALSE,FALSE}) * 45 )

= SUMPRODUCT({TRUE,TRUE,FALSE} *45 )

= 90

#expected answer =45

Can someone help me understand where I am going wrong or what I am missing?

If there is any other way then suggestions are always welcome.

Please note this is a dummy data actual data is very big for each header (P,S,A) there are values in 10 columns respectively and the number of rows is also very large.

Solution

  • Try this...

    =SUMPRODUCT( ((A2:A8=A12)+(B2:B8=A12)+(C2:C8=A12)) * ((D2:D8=B12)+(E2:E8=B12)+(F2:F8=B12)) * ((G2:G8=C12)+(H2:H8=C12)+(I2:I8=C12)) * J2:J8 )

    For SUMPRODUCT to work, the shape of the Boolean array needs to match the shape of the array you wish to conditionally sum.

    J2:J8 is seven rows tall by one column wide.

    The above formula creates an array of 1s and 0s from your three criteria ranges and shapes it into seven rows tall by one column wide.

    At that point, SUMPRODUCT can do it's normal thing because the criteria array matches the dimension of the sum array J2:J8.