Excel help: Count rows with two non-blank columns

Factual Questions
1

I’m feeling rather stupid for not being able to figure this out, and would like to think I’ve done this sort of thing before, but my own wits, MS help, and Google are failing me.

I have >2200 rows of data, that look something like this:



      A          B        C               D            E
1 Jenkins    Salesman    HR
2 Smith      Salesman                   French       English
3 Doe                                   French
4 Wade       Salesman

I can use COUNTA to count the rows that have non-blank values in any given column, but I want to count the rows that have non-blank values in two given columns (e.g., B and C). SUMPRODUCT, and various other combinations of IF, COUNT, NOT, BLANK, EMPTY, COUNTIF, and COUNTIFS aren’t helping.
In my 2200-odd data rows, I know that I have 1500 non-blank cells in B and 1100 non-blank cells in C. I need the intersection of those values…

Am I doing something wrong? Can anyone propose a formula that would work?

2

There are probably more elegant ways but put =if(b1="",1,0) in ColE and -if(c1="",1,0) in ColF and in ColG add E+F.

Alternatively with a single =if(b1="",if(c1="",“Yes”,“No”),“No”) and filter/count for Yes

3

Far from elegant, but:

=SUM(IF(B1:B4<>"",IF(C1:C4<>"",1,0),0))

Done as an array formula (CTRL-SHIFT-ENTER - puts the brackets around it) will work.

4

How are you trying to use sumproduct that it isn’t performing?

5

Oh and let me just be the first to throw in - you could do this with a Pivot Table.

6

penultima thule, ah, yes, I see what you’re saying – I could do it for every row individually, and the count the total. I think that’s what I would have done if I couldn’t get the formula to work.

jonesj2205, depending on my syntax, I was getting 0, 1, or the actual product of the two sums. My fault and not the formula’s fault, to be sure, but…

Zakalwe, array formulas! Thank you! That’s what I was missing, and your suggestion worked perfectly. (I was getting a 0, even though the formula editor was giving me the correct answer… Ctrl+Shift+Enter. Thank you!)

7

This is true, but I’m actually not getting the data from a database. I have all the raw data in one sheet. (The data does reside in a database, too, but a different department controls access to that.)

8

No sweat. I use them all the time and love them. I’ve gotten a lot of Excel help from this place and glad I could it pay it back!