SOLVED

How to COUNT with condition: Cell to right is (also) NOT BLANK

%3CLINGO-SUB%20id%3D%22lingo-sub-1935881%22%20slang%3D%22en-US%22%3EHow%20to%20COUNT%20with%20condition%3A%20Cell%20to%20right%20is%20(also)%20NOT%20BLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935881%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EDo%20you%20know%20a%20formula%20to%20%3CU%3Ecount%3C%2FU%3E%20the%20number%20of%20cells%20%3CU%3Enot%20blank%3C%2FU%3E%20in%20a%20range%20(in%20example%20table%20A1%3AA5)%2C%20but%20with%20a%20%3CU%3Econdition%3C%2FU%3E%20that%20the%20cell%20to%20the%20right%26nbsp%3B(in%20example%20table%20B1%3AB5)%20is%20also%20%3CU%3Enot%20blank%3C%2FU%3E.%3C%2FP%3E%3CP%3EAnother%20way%20to%20explain%20is%20that%20I%20want%20to%20%3CU%3Ecount%20the%20%23%20of%20rows%3C%2FU%3E%20within%20a%20row%20range%20(in%20example%20table%201%3A5)%20where%20%3CU%3Etwo%20columns%3C%2FU%3E%20(in%20example%20table%20A%3AB)%20are%20%3CU%3Enot%20blank%3C%2FU%3E.%3C%2FP%3E%3CP%3EThe%20result%20to%20the%20problem%20based%20on%20the%20example%20would%20be%202%20(only%20%3CU%3EA1%20and%20A4%3C%2FU%3E%2F%2F%3CU%3Erow%201%20and%204%3C%2FU%3E%26nbsp%3Bfulfill%20the%20criteria).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXAMPLE%20TABLE%3A%3C%2FP%3E%3CTABLE%20width%3D%22120px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3EA%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%3CSTRONG%3E4%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3E5%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help.%20Thank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1935881%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935954%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20COUNT%20with%20condition%3A%20Cell%20to%20right%20is%20(also)%20NOT%20BLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883490%22%20target%3D%22_blank%22%3E%40ablixt%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(--(A1%3AA5%26lt%3B%26gt%3B%22%22)*--(B1%3AB5%26lt%3B%26gt%3B%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935959%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20COUNT%20with%20condition%3A%20Cell%20to%20right%20is%20(also)%20NOT%20BLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883490%22%20target%3D%22_blank%22%3E%40ablixt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20bit%20confused%20by%20request%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E...%20a%20formula%20to%26nbsp%3B%3C%2FSPAN%3E%3CU%3Ecount%3C%2FU%3E%3CSPAN%3E%26nbsp%3Bthe%20number%20of%20cells%26nbsp%3B%3C%2FSPAN%3E%3CU%3Enot%20blank%3C%2FU%3E%3CSPAN%3E%26nbsp%3Bin%20a%20range%20but%20with%20a%26nbsp%3B%3C%2FSPAN%3E%3CU%3Econdition%3C%2FU%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAND%3C%2FP%3E%3CP%3E%3CSPAN%3E...%20to%26nbsp%3B%3C%2FSPAN%3E%3CU%3Ecount%20the%20%23%20of%20rows%3C%2FU%3E%3CSPAN%3E%26nbsp%3Bwithin%20a%20row%20range%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eare%20two%20different%20things.%20If%20you%20want%20to%20simply%20count%20any%20cells%20that%20are%20not%20BLANK%2C%20you%20can%20use%20the%20COUNTA%20function%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-11-27%2008_24_59-Book1%20-%20Microsoft%20Excel.png%22%20style%3D%22width%3A%20339px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236342iF1DB29D973CA7895%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222020-11-27%2008_24_59-Book1%20-%20Microsoft%20Excel.png%22%20alt%3D%222020-11-27%2008_24_59-Book1%20-%20Microsoft%20Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

Do you know a formula to count the number of cells not blank in a range (in example table A1:A5), but with a condition that the cell to the right (in example table B1:B5) is also not blank.

Another way to explain is that I want to count the # of rows within a row range (in example table 1:5) where two columns (in example table A:B) are not blank.

The result to the problem based on the example would be 2 (only A1 and A4//row 1 and 4 fulfill the criteria).

 

EXAMPLE TABLE:

 

A

B

1

2

1

2

 

 

3

1

 

4

3

2

5

 

1

 

I hope you can help. Thank you in advance.

4 Replies
Best Response confirmed by ablixt (New Contributor)
Solution

@ablixt Try this:

=SUMPRODUCT(--(A1:A5<>"")*--(B1:B5<>""))

 

@ablixt 

A bit confused by request:

 

... a formula to count the number of cells not blank in a range but with a condition 

AND

... to count the # of rows within a row range 

 

are two different things. If you want to simply count any cells that are not BLANK, you can use the COUNTA function

2020-11-27 08_24_59-Book1 - Microsoft Excel.png

@Riny_van_Eekelen exactly what I was looking for. Thank you!
Thank you for taking your time to answer. I meant either/or. But I got the help I needed.