how do I count the occurrences of multiple conditions

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

[Dav]

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

[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:

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

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

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

[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:

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

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

[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:

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

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

[Marcelo]

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