Counting with relationships

Hey. I have a question which I hope someone here can answer. I need a formula which analyzes every cell of a whole sheet and counts for a certain word(WORD1), but only when the cell above this certain word(WORD1) is another specific word (WORD2). For this I think that there would need to be a relationship set up but I don't know how to do this unfortunately. If anyone has any ideas, please help me out. Thanks

Here's a way:

=SUM( (Sheet1!A1:IU99 = "WORD2") * (Sheet1!A2:IU100 = "WORD1") )

It uses one less than all the columns to keep Excel from converting the range to rows, and only 100 rows because I can't stand making Excel try to do all of them.

It's an array formula, and so has to be confirmed with Ctrl+Shift+Enter.

A VBA solution would be better.

Hey. Thanks for the replies but I found out the formula that i was looking for:

=SUMPRODUCT(--(Sheet1!$A$2:$F$1500="WORD1");--(Sheet1!$A$1:$F$1499="WORD2"))