How do I convert this Countifs function to Sumproduct?

[Goose306]

Hello all;

Learned a lot from lurking on this forum, first time needing some help.

I have a countifs function I've been using for awhile that I use to count blanks between two columns. The value in these cells changes (they can be number or letters), so it doesn't look for anything specific, instead compares one column, finds the non-blanks, then finds the blanks in another comparison column.

=COUNTIFS($A1:$A1001, "<>"&"",$N1:$N1001,"")

The issue being I'm now trying to take this to where it will reside in another workbook and use a reference. I've since learned that the countifs won't work when referencing a closed external workbook and it needs to be converted to a sumproduct function. I've tried several different versions, but I keep getting #VALUE errors, so I don't think I'm quite getting my head around the function yet, and a cursory search finds a couple countif examples, but few countifs. Can anyone give me some guidance on how this formula needs to be changed to be written to a sumproduct?

If it makes any difference, I'm working in Office 2010.

Thanks!

 

[steve the fish]

Try this:

=SUMPRODUCT(--(A1:$A$1001<>""),--($N$1:$N$1001=""))

 

[Goose306]

Try this:

=SUMPRODUCT(--(A1:$A$1001<>""),--($N$1:$N$1001=""))

That seemed to do the trick, thanks!

I had gotten something similar to that, but didn't realize I needed the -- on the second as well.

 

[steve the fish]

That converts a FALSE or TRUE to 0 or 1 so the arrays can be multiplied together.