how to calculate if there is a condition

[imran khan]

i have to column named "PO" and "RO". i have to count the values of PO but condition is like :

PO RO
1 a
2 b
3 c

if RO has all values, count PO values (ans "3") and if

PO RO
1
2 b
3 c

if RO has any empty value, count PO value only for RO values has (ans 2)

 

[BBD]

Hi Imran,

You can use CountA function to do it which skips the empty cells while counting.

 

[imran khan]

HI BBD,
i need to count PO values based on RO values .. count only those PO values how has in front RO values .

 

[GFC]

PO between B2 and B4
RO between C2 and C4

=COUNT(B2:B4)-COUNTBLANK(C2:C4)

 

[imran khan]

hi, GFC,
perfect .. it works . .thanks

 

[Somendra Misra]

Hi Imran,

Amother formula can be
=COUNTIFS(K5:K7,"<>"&"",L5:L7,"<>"&"")

Considering PO in K5:K7 and RO in L5:L7.

Regards!

 

[GFC]

Another formula could also be
=SUMPRODUCT((B2:B4=B2:B4)*1;(C2:C4<>0)*1)

 

[GFC]

Maybe you want to transform your data as table esp.
(e.g. If you need to add new lines at the end of your list on a regular basis)
=COUNT(Table1[PO])-COUNTBLANK(Table1[RO])
=SUMPRODUCT((Table1[PO]=Table1[PO])*1;(Table1[RO]<>0)*1)

 

[bobhc]

We have an old saying "There is more than one way to skin a cat" this thread is showning "There is more than one answer to an Excel problem"

 

[imran khan]

dear somendra Misra / GFC,
thanks for such a beautiful work, appropriated your replies.. hope we will be get i touch again and again... and will learn alot from you guys ..