how do I count the occurrences of multiple conditions

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?

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

Regards

Dav

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:

> 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?

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?

"Dav" wrote:

>
> You will ahve to give a little more information and an example. What
> formula?
> What does the formula refer to, what are the values in the cells
>
> Regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=562096
>
>

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:

> 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?
>
> "Dav" wrote:
>
> >
> > You will ahve to give a little more information and an example. What
> > formula?
> > What does the formula refer to, what are the values in the cells
> >
> > Regards
> >
> > Dav
> >
> >
> > --
> > Dav
> > ------------------------------------------------------------------------
> > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> > View this thread: http://www.excelforum.com/showthread...hreadid=562096
> >
> >

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?

"Marcelo" wrote:

> 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:
>
> > 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?
> >
> > "Dav" wrote:
> >
> > >
> > > You will ahve to give a little more information and an example. What
> > > formula?
> > > What does the formula refer to, what are the values in the cells
> > >
> > > Regards
> > >
> > > Dav
> > >
> > >
> > > --
> > > Dav
> > > ------------------------------------------------------------------------
> > > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> > > View this thread: http://www.excelforum.com/showthread...hreadid=562096
> > >
> > >

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:

> 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?
>
> "Marcelo" wrote:
>
> > 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:
> >
> > > 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?
> > >
> > > "Dav" wrote:
> > >
> > > >
> > > > You will ahve to give a little more information and an example. What
> > > > formula?
> > > > What does the formula refer to, what are the values in the cells
> > > >
> > > > Regards
> > > >
> > > > Dav
> > > >
> > > >
> > > > --
> > > > Dav
> > > > ------------------------------------------------------------------------
> > > > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=562096
> > > >
> > > >

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?

"Marcelo" wrote:

> 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:
>
> > 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?
> >
> > "Marcelo" wrote:
> >
> > > 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:
> > >
> > > > 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?
> > > >
> > > > "Dav" wrote:
> > > >
> > > > >
> > > > > You will ahve to give a little more information and an example. What
> > > > > formula?
> > > > > What does the formula refer to, what are the values in the cells
> > > > >
> > > > > Regards
> > > > >
> > > > > Dav
> > > > >
> > > > >
> > > > > --
> > > > > Dav
> > > > > ------------------------------------------------------------------------
> > > > > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> > > > > View this thread: http://www.excelforum.com/showthread...hreadid=562096
> > > > >
> > > > >

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

hth
regards from Brazil
Marcelo

"Debi" escreveu:

> 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?
>
> "Marcelo" wrote:
>
> > 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:
> >
> > > 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?
> > >
> > > "Marcelo" wrote:
> > >
> > > > 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:
> > > >
> > > > > 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?
> > > > >
> > > > > "Dav" wrote:
> > > > >
> > > > > >
> > > > > > You will ahve to give a little more information and an example. What
> > > > > > formula?
> > > > > > What does the formula refer to, what are the values in the cells
> > > > > >
> > > > > > Regards
> > > > > >
> > > > > > Dav
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Dav
> > > > > > ------------------------------------------------------------------------
> > > > > > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> > > > > > View this thread: http://www.excelforum.com/showthread...hreadid=562096
> > > > > >
> > > > > >