Which function? - Frequencies for three set criteria

  • Thread starter Thread starter jamsco
  • Start date Start date
J

jamsco

I have to find the right Function / Formula to find the frequencies that
meet three criteria.

i.e:



Column A B C

Ethnicity Sex Age
Group

Row 1 0
True

0 1
False

1 0
True



That example showing that there are 2 people that are White (1) Female
(0) in the age group (True) 16-24.

I have tried some functions but can’t seem to get the formula to check
3 set criteria and give a frequency to calculate

And provide the answer for the example above as = 2. My data set is
larger but the same priciples apply.

I thought that 'COUNTIF' would allow this to count (frequency) for the
given 3 criteria, but I cannot seem to perfect this.

Any help would be great,

Jamsco.
 
You could use an array formula:

=SUM(IF((A1:A3=1)*(B1:B3=0)*(C1:C3=TRUE),1,0))

Remember to array-enter this formula. (Instead of hitting Enter after
typing in the formula, hit Ctrl-Shift-Enter.)

/i.
 
Yes. That will sum the number of instances in which all three conditions
are met.

If you take a closer look at the formla, you'll notice that you're just
summing 0s and 1s: 0 if the conditions are not met, 1 if they are.

/i.
 
I used the formula that you suggested,
but the aswer that I got for the frequency is incorrect. (by manually
counting the given criteria)

I do need to include three columns of data: E,J,K
all from rows 2:4000

e2:e4000 plus j2:j4000 plus k2:k4000

where I need to calculate frequency when 'e' =0, 'j' = 1, 'k' =True
when all criteria are met, the formula suggested appears to return a
low answer.

Any suggestion on developing this would be great.

J.
 
Did you array-enter the formula? (When you click on the cell, is the
formula enclosed in curly braces?)

For your specific question, you can use:

=SUM(IF((E2:E4000=0)*(J2:J4000=1)*(K2:K4000=TRUE),1,0))

or simply

=SUM((E2:E4000=0)*(J2:J4000=1)*(K2:K4000=TRUE)*1)

Are the values in K boolean? Or are they textual? If they're text, then
change your K2:K4000=TRUE condition above to K2:K4000="TRUE".

Apply the same principle to E and J. If the values are textual, then wrap
your conditions in quotation marks.

To accomodate all possibilities, use the following array formula:

=SUM((TEXT(E2:E4000,"@")="1")*(TEXT(J2:J4000,"@")="1")*(TEXT(K2:K4000,"@")="
TRUE")*1)

/i.
 
Back
Top