Fun with COUNT and AND functions.
 

Okay... i'm trying to count the amount of times 2 different values appear in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

Try one of these:

=SUMPRODUCT((B10:B100="B")*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100="B"),--(C10:C100="toronto"))

Adjust range references to suit your situation
(SUMPRODUCT doesn't work on entire columns)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

Try this:

=SUMPRODUCT(--(B1:B20=4),--(C1:C20="toronto"))

Adjust your range as needed.

HTH,
Paul

--

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

Is there any way to add wildcard values to this formula? it may be maditory
for what i need to do.

"Ron Coderre" wrote:

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

also, it may be linked to a different sheet within the same file.

"Ron Coderre" wrote:

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

Perhaps this:
=SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100)))

If you're referencing another sheet...try this version:
=SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet
01'!C10:C100)))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Johosh" wrote in message
...
Is there any way to add wildcard values to this formula? it may be
maditory
for what i need to do.

"Ron Coderre" wrote:

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values
appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

That is EXACTLY what i need!

Thanks a lot!

"Ron Coderre" wrote:

Perhaps this:
=SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100)))

If you're referencing another sheet...try this version:
=SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet
01'!C10:C100)))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Johosh" wrote in message
...
Is there any way to add wildcard values to this formula? it may be
maditory
for what i need to do.

"Ron Coderre" wrote:

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values
appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?

Fun with COUNT and AND functions.
 

I'm glad I could help!....thanks for letting me know.

***********
Regards,
Ron

XL2003, WinXP


"Johosh" wrote:

That is EXACTLY what i need!

Thanks a lot!

"Ron Coderre" wrote:

Perhaps this:
=SUMPRODUCT((B10:B100=4)*ISNUMBER(SEARCH("toronto" ,C10:C100)))

If you're referencing another sheet...try this version:
=SUMPRODUCT(('Sheet 01'!B10:B100=4)*ISNUMBER(SEARCH("toronto",'Sheet
01'!C10:C100)))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"Johosh" wrote in message
...
Is there any way to add wildcard values to this formula? it may be
maditory
for what i need to do.

"Ron Coderre" wrote:

Darn...I typed B's instead of 4's:

Here're the corrected versions:

=SUMPRODUCT((B10:B100=4)*(C10:C100="toronto"))
or
=SUMPRODUCT(--(B10:B100=4),--(C10:C100="toronto"))

Again...Adjust range references to suit your situation
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Johosh" wrote in message
...
Okay... i'm trying to count the amount of times 2 different values
appear
in
a row.
ie basically, how many rows the number '4' occurs in column 'b' and
'toronto' occurs is column 'c'.
it can only count rows where both cases are true.
I just can't make it work.

lil help?