# Excel help: Count rows with two non-blank columns **URL:** \ **Category:** Factual Questions\ **Created:** [October 30, 2011, 3:40am UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252 "2011-10-30T03:40:00Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\ **Post date:** [October 30, 2011, 3:40am UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/1 "2011-10-30T03:40:00Z")
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: ```auto 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? ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [October 30, 2011, 11:19am UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/2 "2011-10-30T11:19:51Z")
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 ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [October 30, 2011, 12:27pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/3 "2011-10-30T12:27:52Z")
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. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [October 30, 2011, 1:13pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/4 "2011-10-30T13:13:52Z")
How are you trying to use sumproduct that it isn’t performing? ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [October 30, 2011, 3:20pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/5 "2011-10-30T15:20:08Z")
Oh and let me just be the first to throw in - you could do this with a Pivot Table. ---
**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\ **Post date:** [October 30, 2011, 3:21pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/6 "2011-10-30T15:21:35Z")
**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!) ---
**Author:** ![StephenG](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@StephenG](https://boards.straightdope.com/u/StephenG)\ **Post date:** [October 30, 2011, 3:23pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/7 "2011-10-30T15:23:22Z")
> [@jonesj2205](#): > > Oh and let me just be the first to throw in - you could do this with a Pivot Table. 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.) ---
**Author:** ![Zakalwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zakalwe/32/270_2.png) [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)\ **Post date:** [October 30, 2011, 11:21pm UTC](https://boards.straightdope.com/t/excel-help-count-rows-with-two-non-blank-columns/601252/8 "2011-10-30T23:21:23Z")
> [@StephenG](#): > > **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!) 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!