Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

You may use SUMPRODUCT for this, something like:
=SUMPRODUCT(--(A1:A6="Doctor"),--(B1:B6=1))
Change the ranges and values as appropiate.

Hope this helps

"smiths4" wrote:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

Hi

=sumproduct(--(A2:A7="Doctor")*(c2:c7=1))

hth
regards from Brazil
Marcelo

"smiths4" escreveu:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

Thank you for your help Miguel, but the sumproduct multiplies the cells I
want it to count... for example my result would be Doctors 3.

"Miguel Zapico" wrote:

You may use SUMPRODUCT for this, something like:
=SUMPRODUCT(--(A1:A6="Doctor"),--(B1:B6=1))
Change the ranges and values as appropiate.

Hope this helps

"smiths4" wrote:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

OK, but is not your first question
so to count =sumproduct(--(a2:a7="doctor"))

hth
regards from Brazil
Marcelo

"smiths4" escreveu:

Thank you for your help Miguel, but the sumproduct multiplies the cells I
want it to count... for example my result would be Doctors 3.

"Miguel Zapico" wrote:

You may use SUMPRODUCT for this, something like:
=SUMPRODUCT(--(A1:A6="Doctor"),--(B1:B6=1))
Change the ranges and values as appropiate.

Hope this helps

"smiths4" wrote:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

And as a result I need the formula to tell me how many doctors have a "1"
rating.
As I understand from your question you need to count those Doctors who
have gained "1". Unfortunately, the criteria required to be in 2
different columns.

You can go for this simple solution:
1-Insert a new column between these two columns (I do not know if you
use "B" or not")
2- Concatenat the first column content to the third column content by
using the "&" operator --------- it should look like "doctor1",
"doctor2",...etc
3-use the following formula in any cell :
Countif (Range, "doctor1") {here i search for doctors who has 1}
the formual will count the number of doctors who have "1" only.

4-you can select the inserted column and Hide it.

Hope it works with you and helps.

Hesham Elhadad

Thank you it worked perfectly. Just curious, in the formula what does the --
stand for?

"Marcelo" wrote:

Hi

=sumproduct(--(A2:A7="Doctor")*(c2:c7=1))

hth
regards from Brazil
Marcelo

"smiths4" escreveu:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

The -- is extraneous: it's supposed to convert boolean (or any) value into
numeric

You could write as well =sumproduct(--(A2:A7="Doctor")*(c2:c7=1))
since the * (multiply) sign forces both operands to be converted into
numeric

HTH
--
AP

"smiths4" a écrit dans le message de
news: ...
Thank you it worked perfectly. Just curious, in the formula what does
the --
stand for?

"Marcelo" wrote:

Hi

=sumproduct(--(A2:A7="Doctor")*(c2:c7=1))

hth
regards from Brazil
Marcelo

"smiths4" escreveu:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a
"1"
rating.

Please help.

Thanks
smiths4

the -- signal before is to prevent any errors as you are looking for text
data, tanks for the feedback

regards
Marcelo

"smiths4" escreveu:

Thank you it worked perfectly. Just curious, in the formula what does the --
stand for?

"Marcelo" wrote:

Hi

=sumproduct(--(A2:A7="Doctor")*(c2:c7=1))

hth
regards from Brazil
Marcelo

"smiths4" escreveu:

Good morning all,

I need to find a formula that can give me a total from two different
columns. example...

Column A is Column C is
Doctor 1
Lawyer 2
Lawyer 2
Doctor 1
Lawyer 2
Doctor 2

And as a result I need the formula to tell me how many doctors have a "1"
rating.

Please help.

Thanks
smiths4

Thank you all for your input.

" wrote:

And as a result I need the formula to tell me how many doctors have a "1"
rating.
As I understand from your question you need to count those Doctors who
have gained "1". Unfortunately, the criteria required to be in 2
different columns.

You can go for this simple solution:
1-Insert a new column between these two columns (I do not know if you
use "B" or not")
2- Concatenat the first column content to the third column content by
using the "&" operator --------- it should look like "doctor1",
"doctor2",...etc
3-use the following formula in any cell :
Countif (Range, "doctor1") {here i search for doctors who has 1}
the formual will count the number of doctors who have "1" only.

4-you can select the inserted column and Hide it.

Hope it works with you and helps.

Hesham Elhadad