Looking for a formula that would measure across ranges. For example if I
wanted to caluculate how many time "US, BIG and Country" appeared together on
the same row, how would I do that.

A B C D
E
1 US BIG Country Country Town

2 CAN BIG Country City Village

3 AUS BIG City Country
Province

4 US BIG State Country Country

5 UK SMALL Province City Street

6 UK SMALL Country State Country

7 US SMALL City Country Town

Use
=SUMPRODUCT(--(A1:A7="US")*--(B1:B7="BIG")*--(C1:C7="Country"))
--

Best Regards,
FARAZ A. QURESHI


"ISAF Media Analysis" wrote:

Looking for a formula that would measure across ranges. For example if I
wanted to caluculate how many time "US, BIG and Country" appeared together on
the same row, how would I do that.

A B C D
E
1 US BIG Country Country Town

2 CAN BIG Country City Village

3 AUS BIG City Country
Province

4 US BIG State Country Country

5 UK SMALL Province City Street

6 UK SMALL Country State Country

7 US SMALL City Country Town

Something like this, indicatively:
=SUMPRODUCT((A2:A10="US")*(B2:B10="BIG")*(C2:C10=" Country"))

Mark all responses which help answer your query by clicking the YES button
below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"ISAF Media Analysis" wrote:
Looking for a formula that would measure across ranges. For example if I
wanted to caluculate how many time "US, BIG and Country" appeared together on
the same row, how would I do that.

A B C D
E
1 US BIG Country Country Town

2 CAN BIG Country City Village

3 AUS BIG City Country
Province

4 US BIG State Country Country

5 UK SMALL Province City Street

6 UK SMALL Country State Country

7 US SMALL City Country Town

Could you please explain to us why you've included the double unary minus in
that formula, Faraz?

In general the double unary minus is used to convert a boolean or text value
to an equivalent number without changing its value, but as you've got
multiplication operations in your formula which will already coerce the
booleans to numerics, I don't understand what value the double unary minus
operators will add.
I would have expected either
=SUMPRODUCT((A1:A7="US")*(B1:B7="BIG")*(C1:C7="Cou ntry"))
or
=SUMPRODUCT(--(A1:A7="US"),--(B1:B7="BIG"),--(C1:C7="Country"))
--
David Biddulph

FARAZ QURESHI wrote:
Use
=SUMPRODUCT(--(A1:A7="US")*--(B1:B7="BIG")*--(C1:C7="Country"))

Looking for a formula that would measure across ranges. For example
if I wanted to caluculate how many time "US, BIG and Country"
appeared together on the same row, how would I do that.

A B C D
E
1 US BIG Country Country
Town

2 CAN BIG Country City
Village

3 AUS BIG City Country
Province

4 US BIG State Country
Country

5 UK SMALL Province City
Street

6 UK SMALL Country State
Country

7 US SMALL City Country
Town

Better make a complete structure of a formula.

Tomorrow if any other text type condition arises with asterik works for AND
operation sort and unary signs helping out the same.

While as well to differentiate from the "+" sign, if required, for an "OR"
condition.

--
Best Regards,
FARAZ A. QURESHI


"David Biddulph" wrote:

Could you please explain to us why you've included the double unary minus in
that formula, Faraz?

In general the double unary minus is used to convert a boolean or text value
to an equivalent number without changing its value, but as you've got
multiplication operations in your formula which will already coerce the
booleans to numerics, I don't understand what value the double unary minus
operators will add.
I would have expected either
=SUMPRODUCT((A1:A7="US")*(B1:B7="BIG")*(C1:C7="Cou ntry"))
or
=SUMPRODUCT(--(A1:A7="US"),--(B1:B7="BIG"),--(C1:C7="Country"))
--
David Biddulph

FARAZ QURESHI wrote:
Use
=SUMPRODUCT(--(A1:A7="US")*--(B1:B7="BIG")*--(C1:C7="Country"))

Looking for a formula that would measure across ranges. For example
if I wanted to caluculate how many time "US, BIG and Country"
appeared together on the same row, how would I do that.

A B C D
E
1 US BIG Country Country
Town

2 CAN BIG Country City
Village

3 AUS BIG City Country
Province

4 US BIG State Country
Country

5 UK SMALL Province City
Street

6 UK SMALL Country State
Country

7 US SMALL City Country
Town

?
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
Better make a complete structure of a formula.

Tomorrow if any other text type condition arises with asterik works for
AND
operation sort and unary signs helping out the same.

While as well to differentiate from the "+" sign, if required, for an "OR"
condition.

--
Best Regards,
FARAZ A. QURESHI


"David Biddulph" wrote:

Could you please explain to us why you've included the double unary minus
in
that formula, Faraz?

In general the double unary minus is used to convert a boolean or text
value
to an equivalent number without changing its value, but as you've got
multiplication operations in your formula which will already coerce the
booleans to numerics, I don't understand what value the double unary
minus
operators will add.
I would have expected either
=SUMPRODUCT((A1:A7="US")*(B1:B7="BIG")*(C1:C7="Cou ntry"))
or
=SUMPRODUCT(--(A1:A7="US"),--(B1:B7="BIG"),--(C1:C7="Country"))
--
David Biddulph

FARAZ QURESHI wrote:
Use
=SUMPRODUCT(--(A1:A7="US")*--(B1:B7="BIG")*--(C1:C7="Country"))

Looking for a formula that would measure across ranges. For example
if I wanted to caluculate how many time "US, BIG and Country"
appeared together on the same row, how would I do that.

A B C D
E
1 US BIG Country Country
Town

2 CAN BIG Country City
Village

3 AUS BIG City Country
Province

4 US BIG State Country
Country

5 UK SMALL Province City
Street

6 UK SMALL Country State
Country

7 US SMALL City Country
Town

All right I just got your & Max's valuable point. U mean 2 say if * is used,
we don't need --? Thanx again. I thought it was necessary to have both.

--

Best Regards,
FARAZ A. QURESHI


"David Biddulph" wrote:

?
--
David Biddulph

"FARAZ QURESHI" wrote in message
...
Better make a complete structure of a formula.

Tomorrow if any other text type condition arises with asterik works for
AND
operation sort and unary signs helping out the same.

While as well to differentiate from the "+" sign, if required, for an "OR"
condition.

--
Best Regards,
FARAZ A. QURESHI


"David Biddulph" wrote:

Could you please explain to us why you've included the double unary minus
in
that formula, Faraz?

In general the double unary minus is used to convert a boolean or text
value
to an equivalent number without changing its value, but as you've got
multiplication operations in your formula which will already coerce the
booleans to numerics, I don't understand what value the double unary
minus
operators will add.
I would have expected either
=SUMPRODUCT((A1:A7="US")*(B1:B7="BIG")*(C1:C7="Cou ntry"))
or
=SUMPRODUCT(--(A1:A7="US"),--(B1:B7="BIG"),--(C1:C7="Country"))
--
David Biddulph

FARAZ QURESHI wrote:
Use
=SUMPRODUCT(--(A1:A7="US")*--(B1:B7="BIG")*--(C1:C7="Country"))

Looking for a formula that would measure across ranges. For example
if I wanted to caluculate how many time "US, BIG and Country"
appeared together on the same row, how would I do that.

A B C D
E
1 US BIG Country Country
Town

2 CAN BIG Country City
Village

3 AUS BIG City Country
Province

4 US BIG State Country
Country

5 UK SMALL Province City
Street

6 UK SMALL Country State
Country

7 US SMALL City Country
Town