Sumifs Array not calculating all criteria

[masood78]

Hi
I am trying to use sumfis formula to calculate total sales using OR condition. I am using dynamic ranges in the formula but I have also tried using normal range as well. Formula is calculating only one random value in the criteria range and not all of them.

Formula is here

{=SUM(SUMIFS('Sheet1'!$M:$M,'Sheet1'!$C:$C,SalesRepRange,'Sheet1'!$L:$L,RegionRange,'Sheet1'!$H:$H,MonthRange,'Sheet1'!$E:$E,$E$7))}

Thanks

 

[iam_the_3ggman]

Try deleting the SUMIFS crap and using array notation. I find it usually works better. {=sum((range1=criteria1)+(range2=criteria2)+(range3=criteria3)*(sumrange))}

* is AND
+ is OR

Easy way to remember the operators is that in totality x > 0 = TRUE. TRUE is defined as 1, FALSE is defined as 0. Hence, 0*0*1*1*1 = 0, evaluates false and will not return any data. 0+0+1+1+1 = 3, evaluates as true and will return data.