SOLVED

Highlighted
Contributor

# 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 ><

8 Replies
Highlighted
Best Response confirmed by little2fern (Contributor)
Solution

# Re: Formula to pull out data according to the dates and topic

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.

Highlighted

# Re: Formula to pull out data according to the dates and topic

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
)``````
Highlighted

# Re: Formula to pull out data according to the dates and topic

@Riny_van_Eekelen , sorry, didn't see you already answered

Highlighted

# Re: Formula to pull out data according to the dates and topic

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.

Highlighted

# Re: Formula to pull out data according to the dates and topic

@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.

Highlighted

# Re: Formula to pull out data according to the dates and topic

Thank you so much again. You are very kind ><

Highlighted

# Re: Formula to pull out data according to the dates and topic

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

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

Highlighted

# Re: Formula to pull out data according to the dates and topic

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.