Count formula counts 1 when table is blank or only has 1 row

CascadeDiver

Board Regular
Joined
Apr 10, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
A strange situation that I cannot quite place. I have an array formula

{=COUNT(IF((Table1[Date]>=[@Month])*(Table1[Date]<=EOMONTH([@Month],0)),IF(Table1[[Person 1]:[Person 2]]=Table2[[#Headers],[Janet]],Table1[[D1AP]:[D2AP]])))}

If Table 1 is empty and has one row in the table the formula results in 1 across all columns in Table 2
If Table 1 has one row of data and only one table row the formula results in 1 across all columns in Table 2 except for the in the month row the data is pertaining to
If Table 1 is empty and has two rows in the table the formula results in "" across all columns in Table 2
If Table 1 has one row of data but has two rows in the table (Second one being blank) all formulas in Table 2 are correct. Showing "" when necessary and the count result where necessary
If Table 1 has two or more rows of data all formulas in Table 2 are correct. Showing "" when necessary and the count result where necessary
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,114,257
Messages
5,546,806
Members
410,759
Latest member
Bufnercash
Top