COUNTIF with an array formula referencing dynamic cells

Question

I'm trying to use a COUNTIF with a dynamic array as the criteria.

I have changing names in cells D1, E1, and F1, so when I update the values in those cells the COUNTIF returns a different sum based on the what names are used as the criteria.

=COUNTIF(Col!A:A, "Test", Col!B:B, {D1, E1, F1})

I'm using an array because sometimes the values in D1, E1, or F1, maybe blank, so I need COUNTIF to only count the values which are switching in and out of the array evaluating B:B.

Example data with answers below:

ColA ColB

Test Rob
Foo  Jim
Test Ted
Bar  Rob
Test Rob
Test Jim
Foo  Ted
Test Jim
Test Ted


=COUNTIF(Col!A:A, "Test", Col!B:B, {"Rob", "Jim", "Ted"})
Answer: 6

=COUNTIF(Col!A:A, "Test", Col!B:B, {"", "", "Ted"})
Answer: 2

=COUNTIF(Col!A:A, "Test", Col!B:B, {"", "Jim", "Ted"})
Answer: 4

I've looked here, but I'm not writing this with VBA code

Excel COUNT() vs COUNTIF() with arrays

And I've tried this method, but it won't let me put cell references into the array without popping an error. I can put in strings, but I need my array to have values pop in and out.

Array as criteria in Excels COUNTIFS function, mixing AND and OR

Suggestions?

Answer 1

Wrap it in SUM() and enter it with Ctrl-Shift-Enter:

=SUM(COUNTIFS(Col!A:A, "Test", Col!B:B, D1:F1))

The array needs to be contiguous and one row or column.