Countif with numbers and words

[Joe]

I am trying to add the number of occurances of the word "yes" in one column,
and then adding the number of occurances of ID numbers in another. I will
try to give an example below:

A B C D
Doe John 12345 yes
Bob 12346
Ed 12347
Que Sue 23883 no
Mary 23884
Smith Joe 88464 yes

So basically what I want to do is wherever column D is yes, then I want it
to add up the number of ID numbers in column C and return a value (I am using
ID numbers as it is the best way to track). Thus based on the above, it
should return a value of 4.

I have been reviewing all the countif and sumproduct responses and trying
various things to no avail. Any suggestions?

 

[Jacob Skaria]

One work around

--Select Column D. Press F5. Select Special>check Blanks. OK. This will
select all blank cells in D. Now the active cell is D2 which is blank.
--Keeping the selection Press equal sign (=)
--Press upArrow. This will assign a formula to D2 =D1
--Press Ctrl key and keeping the Ctrl key pressed; press Enter

Now you can use formula =COUNTIF(d:d,"yes")

If this post helps click Yes

 

[Eduardo]

Hi,
to count the yes use

=SUMPRODUCT(--(A7:B10="Y")

I don't understand why you need to count the other column, it could be the
case that a "Y" has not an ID number associated, if always has a number
associated you can multiply the above count by 2

=SUMPRODUCT(--(A7:B10="Y")*2

 

[David Biddulph]

You may want an extra parenthesis at the end of each expression, and you
might lose the double unary minus from the second formula where you already
have the multiplication.

 

[Joe]

Sorry guys but none of those worked. I think I need to explain further as
the responses do not seem to understand what I am trying to track here.

The people in columns A and B are grouped together by last names. There may
be one person, there may be 5+. For each group of people there will be only
one occurance of "yes" or "no" in column D. Despite only one yes or no,
wherever there is an occurance of yes I need to multiply that yes by the
number of people in that group. Hence why below I should return a value of 4
because there are three people with the name Doe and one with Smith but all
have a yes.

My original attempts with the countif and sometimes if commands were to
create the following formula: If D is yes, then count the number of
occurances of an ID number in column C that is greater than 1.

This is a monthly report and this database will be passed on to others
eventually, so I want to make it so that these fields to self calculate each
month based on the data entered.

I hope this helps to clarify.