formula to count how many times a word apears in a range covering multi columns and rows but also countif only column A has the right name

[tonywatsonhelp]

Hi everyone,
need a formula for this one

I have a large range of data in columns AU:HI
many of the cells are empty, but what i'm trying to count is always only data in a cell

so what i need is a formula to do these two things

count the number of time the word "sold" comes up in the range above,
but it can only count rows that have the word "Drawn" in Column A

is this posible?

thanks

Tony

 

[Toadstool]

Hi Tony,

I ran out of resources when I tried checking every row but if you can limit the rows this should work.

TontWatsonHelp.xlsx
	A																																													AT	AU	AV	AW																																																																																																																																																																							HH	HI	HJ	HK
1																																															X	X	X																																																																																																																																																																							X	X		Result
2	Drawn																																																																																																																																																																																																																							sold			3
3																																															sold																																																																																																																																																																										sold
4																																															sold
5
6	Drawn																																														sold		sold
Sheet1

Cell Formulas
Range	Formula
HK2	HK2	=SUMPRODUCT(--(A1:A9999="Drawn")*(AU1:HI9999="Sold"))

 

[tonywatsonhelp]

Thank you toadstool this looks great
Thanks a lot
Tony