Multiple count function

Copper Contributor

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