Mar 23 2020 01:23 AM
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 ><
Mar 23 2020 02:08 AM
SolutionUse 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.
Mar 23 2020 02:16 AM
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
)
Mar 23 2020 02:16 AM
@Riny_van_Eekelen , sorry, didn't see you already answered
Mar 23 2020 02:26 AM
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.
Mar 23 2020 02:27 AM - edited Mar 23 2020 02:28 AM
@Sergei Baklan No problem! Happens to me frequently that I'm just after someone else. Anyhow, your solution is different, so it just adds another option.
Mar 23 2020 02:57 AM
Thank you so much again. You are very kind ><
Mar 23 2020 03:00 AM - edited Mar 23 2020 03:01 AM
I see. There're many formula for this solution. Thank you :D
p.s. How is 'sumifs' different from 'sumproduct' formula above?
Mar 23 2020 03:09 AM
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.
Mar 23 2020 02:08 AM
SolutionUse 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.