Counting Individuals NOT Occurrences with than one criteria

[Royegg]

I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as
follows:

A B C D E
F
1 Harm Red Fully Ach Abstinence Partly Ach Harm Red Fully Ach
2 Harm Red Fully Ach Harm Red Fully Ach
3 Harm Red Partly Ach
4 Abstinence Not Ach Harm Red Partly Ach
5 Abstinence Partly Ach
6 Harm Red Fully Ach Harm Red Partly Ach
7 Harm Red Fully Ach

Now I've been trying to use SUMPRODUCT and it only gives occurrences of say
'Harm Red' and 'Fully Ach'.

I need to be able to count individuals with at least one 'Harm Red' with a
'Fully Ach'. So in the example above there are 4 individuals (out of 7) with
at least one 'Harm Red' and 'Fully Ach' type of pairing even though there are
6 occurrences of this type of pairing.

I definitely need to be able to know how to arrive at the answer 4 through
Excel Worksheet Functions.

Could anybody help please!!

Thanking you in advance

All the best

Roy

 

[Eduardo]

Hi,
try

=SUMPRODUCT((A1:A7="Harm Red")*(B1:B7="Fully Ach"))

 

[Royegg]

Hi Eduardo,

Thanks for your reply.

I've tried SUMPRODUCT type formalas like this before but it comes up with
occurrences rather than the number of individuals. I did try your version
and added several other 'pairings' but unfortunately it only seemed throw up
occurrences.

All the best

Roy

 

[OssieMac]

Hi Roy,

I would create some additional columns; One for each of the criteria Harm
Red Fully Ach etc and use the names as column headers. In your example they
would be columns G to K with the column headers
Harm Red, Fully Ach, Partly Ach, Not Ach, Abstinence

In cell G2 insert =COUNTIF($A2:$F2,G$1)
Note absolute ($signs) on the columns for $A2:$F2 and absolute on the row
for G$1.
Copy the formula across to column K and down for the length of your data.

Now to count the Harm Red and Fully Ach across the row.
In Cell L2 insert =COUNTIFS(G2,">0",H2,">0")

Copy this formula down for the length of your data.
Sum column L for the total answer.

You can add additional columns like L for counts of other combinations.

 

[Otávio Alves Ribeiro]

Hi there.
Did you even give a try to my suggestion in your original post? If not,
please, see the link below:

http://www.microsoft.com/office/com...be2ed6-c4ff-4fe6-9cc2-17fb83f0c503&sloc=en-us

Regards,
Otávio

 

[Royegg]

Hi OssieMac,

Thank you for your help with this problem - it definitely works a treat!!

I should have thought to create new variables like you would in SPSS but the
beauty of Excel is that it all becomes automatically updated with the overlay
of new raw data - something which SPSS does not do as you always seem to have
re-run variables.

Thanks once again

All the best

Roy