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?
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!)
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.)