I am attempting to count the number of times a specific value range occurs in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

Here's an array formula that will help you count unique instances

SUM(1/COUNTIF(data_range,data_range))

CTRL+SHIFT+ENTER to activate the array
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"BASCRUMMY" wrote:

I am attempting to count the number of times a specific value range occurs in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&lastrow))-1,0,1,3),50)=3))

--
__________________________________
HTH

Bob

"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range occurs
in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

I think this is going to work, but I think I am caught up on what to put in
for lastrow. Also should I put my whole data range in where A1 is?

"Bob Phillips" wrote:

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&lastrow))-1,0,1,3),50)=3))

--
__________________________________
HTH

Bob

"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range occurs
in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

I am caught up on what to put in for lastrow.

"lastrow" should be the count of total rows in your range. If your range of
data was from A1:C10 then lastrow = 10. However, you'd need to change the
syntax from:

ROW(INDIRECT("1:"&lastrow))

To:

ROW(INDIRECT("1:10"))

Probably easier if you replace lastrow with ROWS(rng), where rng is your
actual range. Like this:

ROW(INDIRECT("1:"&ROWS(A1:C10)))

Also should I put my whole data range in where A1 is?

No. Use whatever is the top left cell in your range. If your data is in the
range K19:M27 the top left cell in the range is K19 then you'd use K19.

This is why you should always tell use where your real data is located. It
seems most people use "fake" ranges when they post a question and in some
circumstances the location of the data can matter in how a formula is
crafted for the best result.


--
Biff
Microsoft Excel MVP


"BASCRUMMY" wrote in message
...
I think this is going to work, but I think I am caught up on what to put in
for lastrow. Also should I put my whole data range in where A1 is?

"Bob Phillips" wrote:

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&lastrow))-1,0,1,3),50)=3))

--
__________________________________
HTH

Bob

"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range
occurs
in
a row throughout a large spreadsheet. For example, I have three values
in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of
rows
that contain 50,50,50 within a large area.

Here's another one:

=SUMPRODUCT(--(MMULT(--(A1:C100=50),{1;1;1})=3))

Note that using this the range is limited to no more that 5460 rows

--
Biff
Microsoft Excel MVP


"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range occurs
in
a row throughout a large spreadsheet. For example, I have three values in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

Sometimes we overlook the easiest solution!

=SUMPRODUCT(--(A1:A100=50),--(B1:B100=50),--(C1:C100=50))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Here's another one:

=SUMPRODUCT(--(MMULT(--(A1:C100=50),{1;1;1})=3))

Note that using this the range is limited to no more that 5460 rows

--
Biff
Microsoft Excel MVP


"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range occurs
in
a row throughout a large spreadsheet. For example, I have three values
in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

If you have a minute -
Can you give a quick explanation of how this works? What does the " -- "
mean in a formula? I don't remember seeing it.
thanks for your time
dp

"T. Valko" wrote:

Sometimes we overlook the easiest solution!

=SUMPRODUCT(--(A1:A100=50),--(B1:B100=50),--(C1:C100=50))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Here's another one:

=SUMPRODUCT(--(MMULT(--(A1:C100=50),{1;1;1})=3))

Note that using this the range is limited to no more that 5460 rows

--
Biff
Microsoft Excel MVP


"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range occurs
in
a row throughout a large spreadsheet. For example, I have three values
in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of rows
that contain 50,50,50 within a large area.

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"dp" wrote in message
...
If you have a minute -
Can you give a quick explanation of how this works? What does the " -- "
mean in a formula? I don't remember seeing it.
thanks for your time
dp

"T. Valko" wrote:

Sometimes we overlook the easiest solution!

=SUMPRODUCT(--(A1:A100=50),--(B1:B100=50),--(C1:C100=50))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Here's another one:

=SUMPRODUCT(--(MMULT(--(A1:C100=50),{1;1;1})=3))

Note that using this the range is limited to no more that 5460 rows

--
Biff
Microsoft Excel MVP


"BASCRUMMY" wrote in message
...
I am attempting to count the number of times a specific value range
occurs
in
a row throughout a large spreadsheet. For example, I have three
values
in
multipe rows:
A1=10, B1=20, C1=30,
A2=20, B2=10, C2=30.
A3=50, B3=50, C3=50
I want to create a formula that will allow me to count the number of
rows
that contain 50,50,50 within a large area.