Forum Discussion

little2fern's avatar
little2fern
Brass Contributor
Mar 23, 2020
Solved

Formula to pull out data according to the dates and topic

Hello,

 

I want a formula to put into F2, F3, J2, J3. The formula which could pull out data from my other spreadsheet seen below according to the dates I put into B2 and B3. Note that the count should matched the inserted dates and also the topic in front of it e.g. Answered, Unanswered, Unanswered outbound and Abandoned call.

 

Thank you in advance ><

  • little2fern 

    Use SUMPRODUCT in the following way. For "Answered" in F2;

    =SUMPRODUCT('2020 All Protollcall data'!C2:C500*('2020 All Protollcall data'!A2:A500<=$B$3)*('2020 All Protollcall data'!A2:A500>=$B$2))

     ......... where you need to adjust the ranges C2:C500 and A2:A500 to ones that match the data sheet. For "Unanswered" you need to replace the first range by D2:D500, and similarly for the two other categories (col E and F in your data sheet) you want to count.

     

8 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    little2fern 

    Change the source data into a data set list and add an additional column "calender week".

    Then insert a pivot table and shape it to fit your needs.

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    little2fern 

    Formula could be like

    =SUMIFS(
        INDEX('2020 All Protollcall data'!$A$2:$H$360,0,MATCH($E2,'2020 All Protollcall data'!$A$1:$H$1,0)),
        '2020 All Protollcall data'!$A$2:$A$360,">="&$B$2,
        '2020 All Protollcall data'!$A$2:$A$360,"<="&$B$3
    )
    • little2fern's avatar
      little2fern
      Brass Contributor

      SergeiBaklan 

      I see. There're many formula for this solution. Thank you 😄

      p.s. How is 'sumifs' different from 'sumproduct' formula above?

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        little2fern 

        Both have the same logic, SUMIFS is usually better from performance point of view, I don't think that's critical in your case. Take one with which you are more familiar.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    little2fern 

    Use SUMPRODUCT in the following way. For "Answered" in F2;

    =SUMPRODUCT('2020 All Protollcall data'!C2:C500*('2020 All Protollcall data'!A2:A500<=$B$3)*('2020 All Protollcall data'!A2:A500>=$B$2))

     ......... where you need to adjust the ranges C2:C500 and A2:A500 to ones that match the data sheet. For "Unanswered" you need to replace the first range by D2:D500, and similarly for the two other categories (col E and F in your data sheet) you want to count.

     

Resources