SOLVED

Formula to pull out data according to the dates and topic

%3CLINGO-SUB%20id%3D%22lingo-sub-1245952%22%20slang%3D%22en-US%22%3EFormula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202563-03-23%20at%2014.44.54.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178809iECFE18C436A38BBE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202563-03-23%20at%2014.44.54.png%22%20alt%3D%22Screen%20Shot%202563-03-23%20at%2014.44.54.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20a%20formula%20to%20put%20into%20F2%2C%20F3%2C%20J2%2C%20J3.%20The%20formula%20which%20could%20pull%20out%20data%20from%20my%20other%20spreadsheet%20seen%20below%20according%20to%20the%20dates%20I%20put%20into%20B2%20and%20B3.%20Note%20that%20the%20count%20should%20matched%20the%20inserted%20dates%20and%20also%20the%20topic%20in%20front%20of%20it%20e.g.%20Answered%2C%20Unanswered%2C%20Unanswered%20outbound%20and%20Abandoned%20call.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20%26gt%3B%26lt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202563-03-23%20at%2014.45.22.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178812iCDFCBE7B964FBDB1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202563-03-23%20at%2014.45.22.png%22%20alt%3D%22Screen%20Shot%202563-03-23%20at%2014.45.22.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1245952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246052%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20SUMPRODUCT%20in%20the%20following%20way.%20For%20%22Answered%22%20in%20F2%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT('2020%20All%20Protollcall%20data'!C2%3AC500*('2020%20All%20Protollcall%20data'!A2%3AA500%26lt%3B%3D%24B%243)*('2020%20All%20Protollcall%20data'!A2%3AA500%26gt%3B%3D%24B%242))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B.........%20where%20you%20need%20to%20adjust%20the%20ranges%20C2%3AC500%20and%20A2%3AA500%20to%20ones%20that%20match%20the%20data%20sheet.%20For%20%22Unanswered%22%20you%20need%20to%20replace%20the%20first%20range%20by%20D2%3AD500%2C%20and%20similarly%20for%20the%20two%20other%20categories%20(col%20E%20and%20F%20in%20your%20data%20sheet)%20you%20want%20to%20count.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246062%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20814px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178829iC71CB4F7E76B1414%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIFS(%0A%20%20%20%20INDEX('2020%20All%20Protollcall%20data'!%24A%242%3A%24H%24360%2C0%2CMATCH(%24E2%2C'2020%20All%20Protollcall%20data'!%24A%241%3A%24H%241%2C0))%2C%0A%20%20%20%20'2020%20All%20Protollcall%20data'!%24A%242%3A%24A%24360%2C%22%26gt%3B%3D%22%26amp%3B%24B%242%2C%0A%20%20%20%20'2020%20All%20Protollcall%20data'!%24A%242%3A%24A%24360%2C%22%26lt%3B%3D%22%26amp%3B%24B%243%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246063%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%2C%20sorry%2C%20didn't%20see%20you%20already%20answered%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246078%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20the%20source%20data%20into%20a%20data%20set%20list%20and%20add%20an%20additional%20column%20%22calender%20week%22.%3C%2FP%3E%3CP%3EThen%20insert%20a%20pivot%20table%20and%20shape%20it%20to%20fit%20your%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246079%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BNo%20problem!%20Happens%20to%20me%20frequently%20that%20I'm%20just%20after%20someone%20else.%20Anyhow%2C%20your%20solution%20is%20different%2C%20so%20it%20just%20adds%20another%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246123%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20again.%20You%20are%20very%20kind%20%26gt%3B%26lt%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246127%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see.%20There're%20many%20formula%20for%20this%20solution.%20Thank%20you%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3Ep.s.%20How%20is%20'sumifs'%20different%20from%20'sumproduct'%20formula%20above%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246135%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20pull%20out%20data%20according%20to%20the%20dates%20and%20topic%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20have%20the%20same%20logic%2C%20SUMIFS%20is%20usually%20better%20from%20performance%20point%20of%20view%2C%20I%20don't%20think%20that's%20critical%20in%20your%20case.%20Take%20one%20with%20which%20you%20are%20more%20familiar.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Screen Shot 2563-03-23 at 14.44.54.png

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

Screen Shot 2563-03-23 at 14.45.22.png

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

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

 

Highlighted

@little2fern 

Formula could be like

image.png

=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

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

Highlighted

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

 

Highlighted

@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

@Riny_van_Eekelen 

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

Highlighted

@Sergei Baklan 

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

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

 

 

Highlighted

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