Once I drag it down if gives me #######

toony

New member
Joined
Mar 6, 2014
Messages
49
Reaction score
0
Points
0
Location
UK
Excel Version(s)
Excel 2016
I have the below formula and adding one more count ($C$1000:$C$10000,$C1808), which is basically to count the times that a duplicated value appears per day (so as not to repeat it if same value on a different day), but it only works on the first line. Once I drag it down if gives me #######. Could it be because of the dollar signs o because it is an arrow formula?


=IF($E1808="","",IF(AND($AQ1808>=1,$AJ1808=""),"1",IF(COUNTIF($AJ$1000:$AJ1808,$AJ1808)=1,COUNTIFS($AJ1808:$AJ$10000,$AJ1808,$AQ1808:$AQ$10000,">"&0),"")))


So, the whole formula ends up like below, and the red part is what I have added. The added counting is basically the date column (several rows each day):

=IF($E1808="","",IF(AND($AQ1808>=1,$AJ1808=""),"1",IF(COUNTIF($AJ$1000:$AJ1808,$AJ1808)=1,COUNTIFS($A$1808:$A$10000,$A1808,$AJ1808:$AJ$10000,$AJ1808,$AQ1808:$AQ$10000,">"&0),"")))
 
Maybe the column needs to be wider to accommodate the result.

Or maybe your Excel is up the swannee, that other thread formula is no problem for me.
 
Could even be because the result of the formula is a negative date o time (or, if not, how are your cells formatted).
 
yea, I guess my excel is going up the is up the swannee. t' from work an they rare work like the ones at home. Thank you
 
Hi Rollis,
I actually realised that the argument that I entered is actually dates on the countif threat that I want to add. How could I make it work keeping the date format? The date needs to be reflected in that cell as a reference
 
Sorry, spotted the problem but no idea how to avoid it. In the Options you could activate "Date system 1904" but that would also change all your cells with dates.
 
Back
Top