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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 02, 2017

Matt,

 

First two links I found with PivotTable basics

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576

 

http://www.excel-easy.com/data-analysis/pivot-tables.html,

 

google will give much more.

 

For your case

Better if you work with Excel Tables, if not the range works as well. Select one which is with your data,

in ribbon Insert->Pivot Table. 

PivotTable pane will appear, add Date to rows, Status to Columns and Values. Result looks like

 

After that is adjusting - how to group dates, how to design the table.

Anonymous's avatar
Anonymous
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