Query with COUNTIFS across a range vs a column

Afternoon all,

Quick query:

spreadsheet to track agency staff expected throughout the week
overview.PNG

I tried to use COUNTIFS to count the amount of X's between B2:F14 where there was also the word 'red' in G2:G14.
When I enter the formula as

Please Login or Register  to view this content.

it returns a #VALUE error, however if I enter the formula as

Please Login or Register  to view this content.

it returns the value I expect of 29.

Why is this? Why can I not enter the formula using B2:F14?

Regards

Rich

The RANGES have to be same size in COUNTIFS : B14:G14 vs G2:G14 ( different) so your second formula works as all ranges are same size

Try

="red = "&SUMPRODUCT(((B2:F14="X")*(G2:G14="red")))

Thanks both, I've gone with the suggestion from John Topley but rep added for both, thanks for a quick response