Frequncy Help, Please

[ISAF Media Analysis]

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

 

[FARAZ QURESHI]

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

 

[Max]

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

 

[David Biddulph]

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="Country"))
or
=SUMPRODUCT(--(A1:A7="US"),--(B1:B7="BIG"),--(C1:C7="Country"))

 

[FARAZ QURESHI]

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.

 

[David Biddulph]

?
--
David Biddulph

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.

 

[FARAZ QURESHI]

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.