Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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),"")))

  2. #2
    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.

  3. #3
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    Could even be because the result of the formula is a negative date o time (or, if not, how are your cells formatted).

  4. #4
    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

  5. #5
    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

  6. #6
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Location
    Cordenons
    Posts
    31
    Articles
    0
    Excel Version
    2016 64bit
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •