Try this Sam. plug in your criteria cells where the =1, =2 and ="cat" are.
The offset part is what makes it count "cat" in the next row from where the
other match (of 1 and 2 in that array) is found.

=SUMPRODUCT((A1:A4=1)*(B1:B4=2)*(OFFSET(C1:C4,1,0) ="cat"))



"Sam via OfficeKB.com" wrote in message
...
Hi Dave,

I'll get there in the end with all the info.

Column "C" will contain various words for example, the word - "absent".
Text will be the Criteria to match.

The reason I originally tried to use SUMPRODUCT as part of the formula was
so I could change the Criteria on the fly so the formula could count
different text depending what was in H4 or I4 in the example below.

Example: =SUMPRODUCT(--($A$1:$A$500=H4),--($B$1:$B$500=I4))

However, as your original formula below shows it looks possible using the
MATCH Function with F1 and G1 containing the 2 lookup values(Criteria).

you can use an array formula (entered with control shift enter) like

=COUNT(INDIRECT(MATCH(1,(B1:B10=F1)*(C1:C10=G1),0) +1&":"&MATCH(1,B1:B10=F1)
*(C1:C10=G1),0)+1))
with F1 and G1 containing the 2 lookup values (criteria).

Hope you can help.

Regards,
Sam

--
Message posted via http://www.officekb.com