The sumproduct formula below isn't useful here as far as I can see, but I'm
not sure what u want to do. What do you mean count the row of data? If you
want to do an actual COUNT function (which counts up all cells containing
numerical values in a range), 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).

there's probably a shorter way....

If not, you can return the value of some cells contents (in the row beneath
the match) by using an array formula (entered with control shift enter) like

=INDEX(A1:A10,1+MATCH(1,(B1:B10=$F$1)*(C1:C10=$G$1 ),0))

where A1:A10 is the range you want the value returned from (this returns the
content of A1:A10 at the next row from where the 2 matches are first found
in the range).






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

I have a table of data and need a COUNT of the NEXT Row when various
criteria is met for the Row above. I'm using the SUMPRODUCT criteria to
find the Rows that actually match the criteria, but I need the Count to
reflect the Row directly below matched criteria.

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

So, if data on Row 12 meets the above SUMPRODUCT Criteria, I would like
the
count to reflect the count of data on Row 13, Not Row 12.

ROW 12 matches criteria - Count data for ROW 13
ROW 20 matches criteria - Count data for ROW 21
ROW 40 matches criteria - Count data for ROW 41 etc.,

Help very much appreciated.

Regards,
Sam

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