# Count whole numbers in column A if the corresponding row in column B is not null

MS EXCEL Office Professional Plus 2016 countif function

Windows 10

I Want to count the number of jobs that are identified as whole numbers in column A

Column A looks like 1, 1.1, 1.2,1.3,1.4, 1.5,1.6,1.7,1.8,2, 2.1,2.2, 3,

The integer rows need to be counted if there is an address in column B

There are other items in column B, but I only wnat to count the project if it has an adress.

This could be done with something similar if I only wanted to count whole #s

and did not have the condition of column B has to be populated on  the row with the whole number

=SUMPRODUCT(--(MOD(A3:A1000,1)=0))

In the example below the answer = 2

In the A column called Line, 1 and 2 would be counted because there is an address in project location, but 3 would not be counted because there is not an address in the project location field

I inherited this project and am thinking of making the address its own column to simplify and then it could be a simple countif not null scenerio , but I intrigued by the logic to make this work.

 Line Project Location 1 SAMPLE ADDRESS: 123 Federal Ave, Baltimore, MD 21212 1.1 Invoice #1 1.2 Invoice #2 1.3 Invoice #3 1.4 Invoice #4 1.5 Change Order #1 1.6 Change Order #2 1.7 JOB TOTALS 2 SAMPLE ADDRESS: 45 Baldwin Ave, Baltimore, MD 21212 2.1 Invoice #1 2.2 2.3 2.4 2.5 Change Order #1 2.6 Change Order #2 1.7 JOB TOTALS 3
Microsoft ExcelMicrosoft Office

Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
byundt

``````=SUMPRODUCT((MOD(A3:A1000,1)=0)*(B3:B1000<>""))
``````
The * is an "And" operator on a row-wise basis in the SUMPRODUCT. By using it, you do not need to use the two minus signs before the first expression.
avgplusguy