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 to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,576
Members
410,852
Latest member
WernerS
Top