COUNTIFS looking for non-blank values in a range

[JimJohnson]

Yes, I should be able to figure this out, but I'm getting fed up. This formula needs to do two things:

1.) Find a value exists or not in another range (easy)
2.) If above is true, determine how many non-blank cells there are in a range of columns for that particular value

For the sake of argument, assume that I'm looking for the value in column A and I'm looking for non-blanks in columns C, D and E.

Here is the best I could come up with:
=COUNTIFS($A:$A,value,$C:$C,"<>"&"")+COUNTIFS($A:$A,value,$D:$D,"<>"&"")+CCOUNTIFS($A:$A,value,$E:$E,"<>"&"")

It works, but in practice, I am counting non-blanks in a LOT of columns, which is going to be brutal on the calculation time in this worksheet.

Does anyone have a better way of doing this?

Thanks!

 

[barry houdini]

Hello Jim,

You can perhaps use SUMPRODUCT, e.g. with value in J1 try

=SUMPRODUCT((A2:A100=J1)*(C2:E100<>""))

 

[Comfy]

N/a

 

[JimJohnson]

Hi Barry, thanks.

The value that I'm initially searching for is a name, not a number. I apologize, I should've included that in the initial post. So I don't believe SUMPRODUCT will work for me.

 

[JimJohnson]

Actually, it appears that I'm a bit of an idiot and didn't grasp how SUMPRODUCT works.

However, it appears that I cannot use SUMPRODUCT for multiple columns here either.

=SUMPRODUCT(--(A:A="value"),--(C:E>="")) turns up an error.

I think that I might end up having to write a macro for this. It's something I wanted to avoid, but it might end up being the most painless way.

 

[barry houdini]

For multiple columns you need to use the syntax I suggested, multiplying rather than using --, i.e. for the whole column

=SUMPRODUCT((A:A="value")*(C:E<>""))

using the whole column will slow the function down somewhat, though, so I suggest restricting the ranges....

An array formula will also work, i.e.

=SUM(IF(A:A="value",IF(C:E<>"",1)))

confirmed with CTRL+SHIFT+ENTER

although the same caveat about whole columns applies

 

[JimJohnson]

It appears that you're right. I believe that works, but yes, that is way too resource-intensive to run. With the amount of data that I have, I don't think it's feasible.

Oh well, looks like I'm writing a macro to handle it. Thanks for the help, Barry.