asked on 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 |
I believe Subodh answered first as it is the first answer after the question.
It has an 11h and Bryundt has 10h so I am guessing this is a time zone issue.
Byundt has a huge knowledge base where I am still learning and explains items in a way that makes it easy for me to understand.
Open in new window
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.