Formula for counting 2 different critera in multiple columns

Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and i cant figure out how to fix it
for example if A2 says 'FRANCE' and D1 'RED'

Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)

Would really appreciate any help with this its driving me crackers!

Hi Lucy

The above isn't very clear - are you saying you just want to count how many times the words FRANCE or RED appear in columns A and D respectively?

Hi,

My data that I want to search is over a number of columns (10, with 23,000 rows) and i want to search 5 of the columns for RED and the other 5 columns for FRANCE and therefore have a count of the number of rows have RED and FRANCE in.

Hope thats a little clearer :-)

Sorry, yes, to be fair my reading skills were worse than your explaining skills!

If you use SUMPRODUCT you can at least use one of the five column arrays, but then you'd still need to use this five times, as in:

=SUMPRODUCT(--('Datasheet'!BB:BE=A1)*--('datasheet'!BE:BE=D1))+SUMPRODUCT(--('Datasheet'!BB:BE=A1)*--('datasheet'!BF:BF=D1))+SUMPRODUCT(--('Datasheet'!BB:BG=A1)*--('datasheet'!BG:BG=D1))+SUMPRODUCT(--('Datasheet'!BB:BE=A1)*--('datasheet'!BH:BH=D1))

Also, SUMPRODUCT will kill the sheet if you use full column references, so you'd need to use 'Datasheet'!BB1:BE23000, etc. to make it useable.

I'm certain there'll be a more eloquent solution, but it's a bit beyond me this evening!