Forum Discussion

Bryan123's avatar
Bryan123
Brass Contributor
Oct 28, 2019

SUMIF or Vlookup - count/sum the number of times based on unique values?

I would like to create a formula that counts how many times a condition appears based dates.  For example in my example below, I want to the result to be 2 since Bob appears on separate dates 2   H...
excel
Formulas and Functions
  • Bryan123's avatar
    Bryan123
    Brass Contributor
    Oct 29, 2019

    Looks like the ranges have to be set which won't work for me as I'm always adding new items . Can A:A and B:B not work?

     

    Patrick2788 

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Oct 29, 2019

      Bryan123 

      Entire column references are not recommended in arrays. It will tax Excel.

       

      You can make the ranges longer than needed and it will work OK.

      • Bryan123's avatar
        Bryan123
        Brass Contributor
        Oct 29, 2019

        Patrick2788 

         

        I changed it to like A2:A2000&B2:B2000 and it didn't seem to calculate correctly. 

         

        =SUM(IF(FREQUENCY(MATCH(Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,0),MATCH(Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,Completed!$A$2:$A$2000&Completed!$J$2:$J$2000,0))>0,1))-COUNTIF(Completed!$A$2:$A$2000,"<>"&A2)

         

        Bob is listed at A2.  Bob is also listed 3 times on the Completed worksheet but of those 3 times, there are only 2 different dates.

         

        The results I'm getting is -1891

Resources