SUMPRODUCT Formula to Count Row of data Below Matched Criteria
 

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

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

Are you sure you're using the correct terminology?

You're saying "Count" the *next* row, but the count doesn't change with the
row.
In your example, 12, 20, and 40 are 3 rows.
13, 21, and 41 are *still* 3 rows.
Are you really talking about *adding* (totaling, summing) the data on the
next row ???

And also, which Column are you interested in?
Column A or Column B or Both?

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"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

Hi Dave,

Thanks for reply. Further assistance very much appreciated.

Apologies for my original muddled explanation. I think you're on the right
track but hindered by my lack of info and clarity. I'll try to explain
what I'm trying to achieve.

3 Columns - A, B and C with data. The Data to be counted is text in Column
C.

1) I need to first match two conditions for column A and B - criteria in F1
and G1
2) When conditions met for columns A and B on say Row 12, Count the data in
Column C Row 13.

I need the Count to start from the Row Below the matched Criteria Row and
for it to Count the data in Column C and continue matching Criteria in
Columns A and B and Counting through my worksheet range to finally produce
a Summed Count of data in column C.

Example:
ROW 12 Data in Row 12 column A and B matches criteria - Count data for ROW
13 in Column C
ROW 20 Data in Row 20 column A and B matches criteria - Count data for ROW
21 in Column C
ROW 40 Data in Row 40 column A and B matches criteria - Count data for ROW
41 in Column C etc.,

So, when matched Criteria found in columns A and B I need an ongoing Count
of data for Column C the Next Row(Row Below matched Criteria) until it gets
to the end of my worksheet range to produce a Summed Count of data found in
column C (always counting Column C Row below matched Criteria) based on the
Criteria being previously matched in Columns A and B.

I hope this is clearer.

Any further assistance very much appreciated.

Regards,
Sam

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

Hi RagDyer,

Thanks for reply. Assistance very much appreciated.

Apologies for my original muddled explanation. I'll try to explain what I'm
trying to achieve.

3 Columns - A, B and C with data. The Data to be counted is text in Column
C.

1) I need to first match two conditions for column A and B - criteria in F1
and G1
2) When conditions met for columns A and B on say Row 12, Count the data in
Column C Row 13.

I need the Count to start from the Row Below the matched Criteria Row and
for it to Count the data in Column C and continue matching Criteria in
Columns A and B and Counting through my worksheet range to finally produce
a Summed Count of data in column C.

Example:
ROW 12 Data in Row 12 column A and B matches criteria - Count data for ROW
13 in Column C
ROW 20 Data in Row 20 column A and B matches criteria - Count data for ROW
21 in Column C
ROW 40 Data in Row 40 column A and B matches criteria - Count data for ROW
41 in Column C etc.,

So, when matched Criteria found in columns A and B I need an ongoing Count
of data for Column C the Next Row(Row Below matched Criteria) until it gets
to the end of my worksheet range to produce a Summed Count of data found in
column C (always counting Column C Row below matched Criteria) based on the
Criteria being previously matched in Columns A and B.

I hope this is clearer.

Any further assistance very much appreciated.

Regards,
Sam

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

Ahh ok! You want to do some counting of column C, but actually look in the
row beneath the matching row. What is unclear now is what you want to
count.. you refer to column Cs content as "text" and have not provided any
criteria on what should be counted. What is in column C and what should be
counted?

Not that I'll neccessarily be able to do it once I know what you want to
count, but it is an important thing to know.




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

Thanks for reply. Further assistance very much appreciated.

Apologies for my original muddled explanation. I think you're on the right
track but hindered by my lack of info and clarity. I'll try to explain
what I'm trying to achieve.

3 Columns - A, B and C with data. The Data to be counted is text in Column
C.

1) I need to first match two conditions for column A and B - criteria in
F1
and G1
2) When conditions met for columns A and B on say Row 12, Count the data
in
Column C Row 13.

I need the Count to start from the Row Below the matched Criteria Row and
for it to Count the data in Column C and continue matching Criteria in
Columns A and B and Counting through my worksheet range to finally produce
a Summed Count of data in column C.

Example:
ROW 12 Data in Row 12 column A and B matches criteria - Count data for ROW
13 in Column C
ROW 20 Data in Row 20 column A and B matches criteria - Count data for ROW
21 in Column C
ROW 40 Data in Row 40 column A and B matches criteria - Count data for ROW
41 in Column C etc.,

So, when matched Criteria found in columns A and B I need an ongoing
Count
of data for Column C the Next Row(Row Below matched Criteria) until it
gets
to the end of my worksheet range to produce a Summed Count of data found
in
column C (always counting Column C Row below matched Criteria) based on
the
Criteria being previously matched in Columns A and B.

I hope this is clearer.

Any further assistance very much appreciated.

Regards,
Sam

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

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

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

Hi Dave,

Thank you very much for all your help and taking the time to assist. The
formula is working fine.

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

Regards,
Sam

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