Forum Discussion

Matt Walje's avatar
Matt Walje
Copper Contributor
Nov 02, 2017

Counting words in one column based on dates in another column.

Hi Everyone,   I am looking for help coming up with a formula to do the following...   In the A column I have a clients status which is either "Completed", "AMA", or "Admin' In the B column I ha...
Formulas & Functions
Need Help
Deleted's avatar
Deleted
Nov 03, 2017
Hi Segey and Matt
why not use countifs?

Yossi
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 03, 2017

Hi Yossi,

 

For such kind of tasks formulas (COUNTIFS, SUMPRODUCT, whatever) is less flexible and more time consuming solution compare to PivotTables.

 

With formulas you have to build manually resulting table, add formulas to it, test them to be sure they work correctly. And repeat the same if one day you add/change the status, decide to calculate averages not only counts, etc.

 

PivotTable, especially in combination with Table, gives the same in couple of clicks. With adding new data you shall expand resulting table with formulas manually, with PivotTable just refresh it.

  • Matt Walje's avatar
    Matt Walje
    Copper Contributor
    Nov 03, 2017

    Sergei and Yossi,

     

    Thanks for both of your inputs on this.  I started messing around with pivot tables and I do see that they will be easier to use in the future once I get more familiar with them.  In the meantime I was able to use the following formula to achieve my goal.

     

    =COUNTIFS($A$2:$A$480,">=1/1/2016",$A$2:$A$480,"<=3/31/2016",$B$2:$B$480,"*Admin*") 

     

    Then I would simply change dates, or the word to get what I was looking for for that particular part of the table I was building.

     

    Thanks again to you both. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 05, 2017

      Hi Matt,

       

      When perhaps easier and more reliable will be if you define start and end dates of you periods in the sheet cells (you may use

       

      =EDATE(<date>,3)

      not to enter all dates manually)

       

      and for such data structure

      the formula in F3 will be

       

      =COUNTIFS($A$2:$A$600,">="&$D3,$A$2:$A$600,"<="&$E3,$B$2:$B$600,F$2)

      you may frag it down and when entire column to the right.

       

      The alternative (in L3) is

       

      =SUMPRODUCT(($A$2:$A$600>=$J3)*($A$2:$A$600<=$K3)*(ISNUMBER(SEARCH(L$2,$B$2:$B$600))))

      With this it's not necessary to correct the formula for each period/status. However, if you range with data will be out of one defined in formula you have to correct the formulas again.

       

       

      From that point of view (and not only) better to convert you range into the table -stay on any cell within it and press Ctrl+T.

      It looks like

      and above formulas could be transferred to

       

      =COUNTIFS(Table1[Date],">="&$D3,Table1[Date],"<="&$E3,Table1[Status],F$2)

      and

       

      =SUMPRODUCT((Table1[Date]>=$J3)*(Table1[Date]<=$K3)*(ISNUMBER(SEARCH(L$2,Table1[Status]))))

      sure instead of adding "*" in columns names you may use

      , "*" & F$2 & "*"

      in first formulas.

      Sample is attached

       

       

       

       

Resources