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