Formula to sum two cell values

S

smiths4

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
 
M

Miguel Zapico

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
 
M

Marcelo

Hi

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

hth
regards from Brazil
Marcelo

"smiths4" escreveu:
 
S

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.
 
M

Marcelo

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

hth
regards from Brazil
Marcelo

"smiths4" escreveu:
 
H

helhadad

And as a result I need the formula to tell me how many doctors have a "1"
rating.
Click to expand...
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
 
S

smiths4

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

Ardus Petus

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
 
M

Marcelo

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

regards
Marcelo

"smiths4" escreveu:
 
S

smiths4

Thank you all for your input.

[email protected] said:
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
Click to expand...
 
Top