SOLVED

Need help creating complex count formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1581395%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20creating%20complex%20count%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581395%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%3EThe%20formula%20in%20C2%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS('2019%20All%20Tickets'!%24C%242%3A%24C%246472%2C%22%26gt%3B%3D%22%26amp%3B%24L%243%2C'2019%20All%20Tickets'!%24C%242%3A%24C%246472%2C%22%26lt%3B%3D%22%26amp%3B%24L%244%2C'2019%20All%20Tickets'!%24D%242%3A%24D%246472%2CB2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20D2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS('2019%20All%20Tickets'!%24C%242%3A%24C%246472%2C%22%26gt%3B%3D%22%26amp%3B%24L%243%2C'2019%20All%20Tickets'!%24C%242%3A%24C%246472%2C%22%26lt%3B%3D%22%26amp%3B%24L%244%2C'2019%20All%20Tickets'!%24D%242%3A%24D%246472%2C%24B2%2C'2019%20All%20Tickets'!%24E%242%3A%24E%246472%2C'Ticket%20Issue'!D%241)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20H2%2C%20then%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581076%22%20slang%3D%22en-US%22%3ENeed%20help%20creating%20complex%20count%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581076%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202563-08-11%20at%2019.16.15.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%2F211757i0B83C3356415E253%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202563-08-11%20at%2019.16.15.png%22%20alt%3D%22Screen%20Shot%202563-08-11%20at%2019.16.15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20creating%20%3CU%3Eformula%20for%20cell%20D2%3AH2%20in%20spreadsheet%20Ticket%20Issue%3C%2FU%3E.%3C%2FP%3E%3CP%3EThe%20formula%20I%20want%20will%20count%20how%20many%20tickets%20within%3A%3C%2FP%3E%3CP%3E1.%20specified%20time%20(%3CSPAN%3E'Ticket%20Issue'!%3C%2FSPAN%3EL3%3AL4)%3C%2FP%3E%3CP%3E2.%20specified%20ticket%20channel%20(%3CSPAN%3E'2019%20All%20Tickets'!E2%3AE)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ehave%20the%20defined%20ticket%20issue%20as%20stated%20in%26nbsp%3B%3CSPAN%3E'Ticket%20Issue'!%3C%2FSPAN%3EB2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSTRIKE%3EPlease%20follow%20this%20link%20to%20view%20the%20spreadsheet%20file%20%26gt%3B%26gt%3B%26nbsp%3B%5B%26nbsp%3Bexpire%20%5D%26nbsp%3B%3C%2FSTRIKE%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%2C%3C%2FP%3E%3CP%3EFern%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1581076%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
Contributor

Hello,

 

Screen Shot 2563-08-11 at 19.16.15.png

 

I need help creating formula for cell D2:H2 in spreadsheet Ticket Issue.

The formula I want will count how many tickets within:

1. specified time ('Ticket Issue'!L3:L4)

2. specified ticket channel ('2019 All Tickets'!E2:E)

have the defined ticket issue as stated in 'Ticket Issue'!B2.

 

Please follow this link to view the spreadsheet file >> [ expire ] 

 

Many thanks in advance,

Fern

 

1 Reply
Best Response confirmed by little2fern (Contributor)
Solution

@little2fern 

The formula in C2 should be

 

=COUNTIFS('2019 All Tickets'!$C$2:$C$6472,">="&$L$3,'2019 All Tickets'!$C$2:$C$6472,"<="&$L$4,'2019 All Tickets'!$D$2:$D$6472,B2)

 

Enter the following formula in D2:

 

=COUNTIFS('2019 All Tickets'!$C$2:$C$6472,">="&$L$3,'2019 All Tickets'!$C$2:$C$6472,"<="&$L$4,'2019 All Tickets'!$D$2:$D$6472,$B2,'2019 All Tickets'!$E$2:$E$6472,'Ticket Issue'!D$1)

 

Fill to the right to H2, then down.