=sum(countif... array formula but only for non empty cells

[joshman108]

Functionally, this is working and doing what I need:
={SUM(COUNTIF(B:AO,B1:AO1))}

It's just really slow because I have 9000 rows and this is being dragged down. It would probably take hours to calculate. I'm assuming it's slow because it's taking empty cells into account as in general, only about 1/3 of all cells b1:ao1 (and on all other rows) actually have values. The rest are blank. What's the fastest way of accomplishing my formula without the major calculation time - which I'm assuming is because it's searching blank cells which I don't actually need to be searching, since they're blank?

I tried something like this:

=SUM(COUNTIF(B:AO,IF(B1:AO1="","",B1:AO1)))

but that doesn't work

 

[Eric W]

You could try:

=SUM(IF(B1:AO1="",0,COUNTIF(B:AO,B1:AO1)))
with Control+Shift+Enter,

but I don't know how much that will help.

 

[joshman108]

That formula returns correctly, but it still chuggs the same. darn