SUMIFS and >0 over adjacent columns

[thart21]

Couldn't find anything in a search, but probably just wrong keywords.

I have the below SUMIFS formula which works great.

=SUMIFS('Late Details'!$AR:$AR,'Late Details'!$C:$C,$A$3,'Late Details'!$J:$J,$A$1,'Late Details'!$N:$N,$A54,'Late Details'!$AO:$AO,">0")

The last criteria however that reads "Where Late Details, column AO is >0 needs to read
"where column AO through AQ are >0.

Tried to adjust formula to 'Late Details'!$AO:$AQ,">0") with no luck.

Easy fix? Thanks for any help.

Toni

 

[barry houdini]

Hello Toni,

with SUMIFS all the ranges need to be exactly the same size and shape ....

IF all 3 columns need to be greater than zero then just add 2 more ranges and criteria...

=SUMIFS('Late Details'!$AR:$AR,'Late Details'!$C:$C,$A$3,'Late Details'!$J:$J,$A$1,'Late Details'!$N:$N,$A54,'Late Details'!$AO:$AO,">0",'Late Details'!$AP:$AP,">0",'Late Details'!$AQ:$AQ,">0")

....or is it the sum of all three columns for each row that needs to be >0 ?

 

[thart21]

Hi Barry,

There just needs to be a value in one of the 3 columns. So yes, the sum of all 3 columns needs to be greater than 0.

Thanks!

 

[thart21]

I think I'm getting close, no errors in this, but it is not giving me results.

=IF(SUM($AO:$AU),">0")*SUMIFS('Late Details'!$AR:$AR,'Late Details'!$C:$C,$A$3,'Late Details'!$J:$J,$A$1,'Late Details'!$N:$N,$A60)

Any ideas on what it is missing would be appreciated!

Thanks,

Toni

 

[barry houdini]

I don't believe you can do that with SUMIFS, try using SUMPRODUCT, although you'd be best advised to restrict the ranges for efficiency reasons - something like this

=SUMPRODUCT('Late Details'!$AR$2:$AR$1000,('Late Details'!$C$2:$C$1000=$A$3)*('Late Details'!$J$2:$J$1000=$A$1)*('Late Details'!$N$2:$N$1000=$A54)*('Late Details'!$AO$2:$AO$1000+'Late Details'!$AP$2:$AP$1000+'Late Details'!$AQ$2:$AQ$1000>0))

 

[thart21]

Thanks Barry, it works! Although, since the # of rows in my data changes each week, is it just not possible to refer to the entire range $AR:$AR in SUMPRODUCT? I would like for my users to not have to update it but, since I used a range of AR2:AR25000 and it doesn't seem to be taking a performance hit so I might be ok. Thanks very much for you help with this. I'm an Access person thrown into the Excel world lately!

 

[barry houdini]

You can use the whole column in Excel 2007 and later versions but while SUMIFS will only look at the "used range" (and it's quicker anyway) SUMPRODUCT wil calculate for all rows specified so it's just a speed/efficiency issue.....