• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Immediate Count Required

Naresh Yadav

Active Member
Respected All,

I Need a formula that can allow me to get the count of Immediate Number Only
Here is the example for your reference
thanks for your help..

Size
Location AliasArticle noDESCCat
34​
36​
38​
40​
42​
44​
46​
Immediate Count
11 HAZRATGANJ LKUCJNM0970R3BS19FLNL-JUDE2 # MAROONCJ
1​
2​
0
AmbienceCJNM0970V2BS19FLNL-JUDE2 # OLIVECJ
3​
2​
2
ShimlaCJNM1170N2BA19FLNL-ST-STARDUS2 # NAVYCJ
2​
3​
1​
2​
3
AkolaCJNM1170N2BA19FLNL-ST-STARDUS2 # NAVYCJ
2​
3​
4​
3​
2​
4
AmbalCJNM1170N2BA19FLNL-ST-STARDUS2 # NAVYCJ
1​
2​
3​
4​
5​
5
amritsarCJNM1170N2BA19FLNL-ST-STARDUS2 # NAVYCJ
1​
2​
3​
4​
5​
6​
6
RajasthanCJNM1170N2BA19FLNL-ST-STARDUS2 # NAVYCJ
1​
2​
3​
4​
5​
6​
6

regards
Naresh
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

Just to explain the logic , we can revise the formulas as follows :

In the left-most column , we only look at the column itself and its right hand side neighbour. So the formula in the first cell becomes :

=IF(AND(E3 > 0, F3 > 0), 1, 0)

In every succeeding column , we look at the column itself , and either of its neighbours. So the formula becomes :

=IF(AND(F3 > 0, OR(E3 > 0, G3 > 0)), 1, 0)

Narayan
 

bosco_yip

Excel Ninja
64252

Array formula (CSE) in M3, copied down :

=SUM(0+TEXT(FREQUENCY(IF(E3:K3<>"",COLUMN($A$1:$G$1)),IF(E3:K3="",COLUMN($A$1:$G$1))),"[=1]\0;0"))

Regards
Bosco
 

Naresh Yadav

Active Member
Dear Sir,

I have gone thoroughly to check the data which is available with me and works the same the way I wanted.. there is no changes in the the result anywhere
thanks once again for sharing the second formula which is much easier to me to understand

regards
Naresh
 
Top