Forum Discussion
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 ><
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
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.
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 )I see. There're many formula for this solution. Thank you 😄
p.s. How is 'sumifs' different from 'sumproduct' formula above?
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.
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.
Thank you so much again. You are very kind ><
Riny_van_Eekelen , sorry, didn't see you already answered
SergeiBaklan No problem! Happens to me frequently that I'm just after someone else. Anyhow, your solution is different, so it just adds another option.
