how do I count the occurrences of multiple conditions

D

Debi

In the help menu at the end of the formula there is 0,1 I do not know what
this is referencing could someone explain this to me please?
 
M

Marcelo

hi, to count the occurrences of multiple conditions, try to use sumproduct.

=sumproduct(--(a2:a100="condition 1")*(b2:b100="condition 2"))

hth
regards from Brazil
Marcelo

"Debi" escreveu:
 
D

Dav

You will ahve to give a little more information and an example. Wha
formula?
What does the formula refer to, what are the values in the cells

Regards

Da
 
D

Debi

I have data that I want to count how many rows there are that meet 2 seperate
criteria for example I would like to count how many rows contain a CB in
column b rows 4 through 309 and a 2 in column q rows 4 through rows 309. When
I looked in the help menu it give an example of
=sum(if(b5:b25,"northwind,if(c5:c25-"western",1,0))) first is this the
correct formula and second what does the 1,0 represent at the end of the
formula example given?
 
M

Marcelo

Hi Debi,

the 1,0 at the end of the formula means true,false, excel looks for the if
question and return 1 if it's true of 0 if it's false, eg

assuming that you have "apple" on cell a1, so =if(a1="apple",true,false)
where in your case true = 1 and false = 0 you can also use functions here.

to solve your problem I think the best way is use sumproduct

=sumproduct(--(B4:B309="CB")*(Q4:A309=2))

HTH
regards from Brazil
Marcelo

"Debi" escreveu:
 
D

Debi

Excellent that worked now if you could tell me how I would count the rows if
column B rows 4 throught 309 = CB and columns l through t rows 4 through 309
= 0 or = not blank or = blank.
The criteria of 0. blank, not blank need to be either combined or seperate
depending on the work sheet I am working in.
Can you help me out on this one?
 
M

Marcelo

Debi,

if my understand is correct try:

=sumproduct(--(B4:B309="CB")*(Q4:A309=2)*contif(l4:t309,"=0"))/sumproduct(--(B4:B309="CB")*(Q4:A309=2))

hth
regards from Brazil
Marcelo

"Debi" escreveu:
 
D

Debi

In your formula you reference Q4:A309 instead of Q4:Q309. Is this a typo or
should I use that range if so could you explain why? And if I do not wish to
sum the ones in column q that contain a 2 and just want to count those in
columns i through t can I just leave off that part of the formula?
 
M

Marcelo

typo mistake, the column q on the formula is just to divede the product

hth
regards from Brazil
Marcelo

"Debi" escreveu:
 
Top