Using OR in SUMIF in Excel

Things have moved on since the days of DSUM, we now have the likes of SUMIFS from Excel 2007 onwards. Can't even remember life before SUMIFS having never being a fan on DSUM.

But with all things in life everything moves on, everything is never good enough and now SUMIFS's shortcomings are beginning to pop up.

The other week the need came up for a handy way to integrate OR logic into the SUMIFS, saves on some extra column calculations and makes us all look a bit cleverer. It is possible via an array formula however it does have its limitations. The biggy being that the OR variables have to be hard coded in - not so good.

Any how, it is simple enough to code up, just put the OR parameters in curly brackets and surround the SUMIFS with a SUM and you are away:

= SUM ( SUMIFS ( $C$2:$C$16 , $B$2:$B$16 , {1,2,3,4} ) )

works with COUNTIFS also:

= SUM( COUNTIFS( $B$2:$B$16 , {1,2,3,4} ) )


Search Posts

Back to top