Better formula

[jimblimm]

Is there any way to make this formula shorter

=countifs('main data'!$bb$4:$bb$8500,0)+countifs('main data'!$bc$4:$bc$85001,0)+countifs('main data'!$bd$4:$bd$8500,0)+countifs('main data'!$bd$4:$bd$8500,0)

 

[cyrilbrd]

why not:
=SUMPRODUCT(('main data'!$bb$4:$bb$8500=0) + (('main data'!$bc$4:$bc$8500=0) + ('main data'!$bd$4:$bd$8500=0) > 0))
or only =COUNTIF('main data'!$bb$4:$bd$8500,0)

 

[T. Valko]

Is there any way to make this formula shorter

=countifs('main data'!$bb$4:$bb$8500,0)+countifs('main data'!$bc$4:$bc$85001,0)+countifs('main data'!$bd$4:$bd$8500,0)+countifs('main data'!$bd$4:$bd$8500,0)

You're double counting column BD.

This is essentially what you're formula is doing:

=COUNTIF('main data'!$BB$4:$Bd$8500,0)

This is what I think you're wanting to do:

=COUNTIFS('main data'!$BB$4:$BB$8500,0,'main data'!$BC$4:$BC$8500,0,'main data'!$BD$4:$BD$8500,0,'main data'!$BE$4:$BE$8500,0)