countif and sum if if two condition are satisfied
 

I have the data as under
A B
S 2
S 3
F 4
F 0
M 5
M 6

I want to count no of cells in Column B which has correspoding F in Column A
and value more than 0, like here the result should be one. And can I make
total also by getting the result 4
Please help

countif and sum if if two condition are satisfied
 

=SUM(IF((A1:A10="F")*(B1:B100),1,0)) to count the number of cells

=SUM(IF((A1:A10="F")*(B1:B100),B1:B10,0)) to show the sum of such
cells

formulas have to be array-entered = CTRL+SHIFT+ENTER

countif and sum if if two condition are satisfied
 

=SUMPRODUCT(--(A2:A200="F"),--(B2:B2000))

with SUMPRODUCT, you cannot use whole columns you must use an explicit
range, unless you have Excel 2007

--
__________________________________
HTH

Bob

"zafar62" wrote in message
...
I have the data as under
A B
S 2
S 3
F 4
F 0
M 5
M 6

I want to count no of cells in Column B which has correspoding F in Column
A
and value more than 0, like here the result should be one. And can I make
total also by getting the result 4
Please help

countif and sum if if two condition are satisfied
 

Take a look at this site...when you can devote some time to it:
http://www.xldynamic.com/source/xld....T.html#classic

Regards,
Ryan---


Regards,
Ryan----


--
RyGuy


"Bob Phillips" wrote:


=SUMPRODUCT(--(A2:A200="F"),--(B2:B2000))

with SUMPRODUCT, you cannot use whole columns you must use an explicit
range, unless you have Excel 2007

--
__________________________________
HTH

Bob

"zafar62" wrote in message
...
I have the data as under
A B
S 2
S 3
F 4
F 0
M 5
M 6

I want to count no of cells in Column B which has correspoding F in Column
A
and value more than 0, like here the result should be one. And can I make
total also by getting the result 4
Please help