Multiple count function

%3CLINGO-SUB%20id%3D%22lingo-sub-3003054%22%20slang%3D%22en-US%22%3EMultiple%20count%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3003054%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20attempting%20to%20get%20a%20COUNTA%20for%20any%20data%20entered%20in%20a%20range%20if%20a%20separate%20range%20includes%20a%20specific%20word.%20Specifically%2C%20I%20am%20trying%20to%20count%20the%20number%20of%20providers%20in%20a%20given%20range%20(COUNTA(D16%3AD3000)%2C%20but%20only%20if%20the%20verbiage%20in%20a%20separate%20range%20is%20met%20(COUNTIF(C%3AC%2C%22October%22).%3C%2FP%3E%3CP%3ELet's%20say%20that%20there%20are%20three%20lines%20with%20C%20being%20October%2C%20and%20three%20lines%20with%20C%20being%20November.%20D%20has%20four%20providers%20listed%2C%20two%20sharing%20the%20October%20line%2C%20and%20two%20sharing%20the%20November%20line.%20I%20want%20the%20formatted%20cell%20to%20count%20the%20number%20of%20lines%20that%20have%20both%20%22October%22%20and%20a%20provider%20listed.%20In%20this%20instance%2C%202.%3C%2FP%3E%3CP%3E%3DCOUNTIFS(COUNTA(D16%3AD3000)%2CCOUNTIF(C%3AC.%22October%22))%20is%20my%20best%20guess%2C%20but%20that%20is%20only%20generating%20an%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3003054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3003133%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20count%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3003133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228915%22%20target%3D%22_blank%22%3E%40Ian_Pegg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(C1%3AC6%2C%22October%22%2CD1%3AD6%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22)%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((D1%3AD6%26lt%3B%26gt%3B%22%22)*(C1%3AC6%3D%22October%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20these%20formulas%20for%20your%20task.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3003136%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20count%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3003136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228915%22%20target%3D%22_blank%22%3E%40Ian_Pegg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20ranges%20should%20be%20the%20same%20size%2C%20and%20you%20cannot%20use%20COUNTA%20or%20COUNTIF%20inside%20COUNTIFS%20that%20way.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(D16%3AD3000%2C%22%26lt%3B%26gt%3B%22%2CC16%3AC3000%2C%22October%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm attempting to get a COUNTA for any data entered in a range if a separate range includes a specific word. Specifically, I am trying to count the number of providers in a given range (COUNTA(D16:D3000), but only if the verbiage in a separate range is met (COUNTIF(C:C,"October").

Let's say that there are three lines with C being October, and three lines with C being November. D has four providers listed, two sharing the October line, and two sharing the November line. I want the formatted cell to count the number of lines that have both "October" and a provider listed. In this instance, 2.

=COUNTIFS(COUNTA(D16:D3000),COUNTIF(C:C."October")) is my best guess, but that is only generating an error.

2 Replies

@Ian_Pegg 

=COUNTIFS(C1:C6,"October",D1:D6,"<>"&"")

=SUMPRODUCT((D1:D6<>"")*(C1:C6="October"))

 

I suggest these formulas for your task.

@Ian_Pegg 

The ranges should be the same size, and you cannot use COUNTA or COUNTIF inside COUNTIFS that way.

 

=COUNTIFS(D16:D3000,"<>",C16:C3000,"October")