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

Formula Challenge 020 - Conditional COUNT

shrivallabha

shrivallabha

Excel Ninja
I think this is easier one but saw this as requirement on some other forum. The person had requested a VBA solution as probably he thought VBA was easier [only?] route.

Here is the data:
Code: 
Heading Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 CC
    H1      12      0      0     0      0     -5     1      0    8
    H2        0      0     -7   12    13    15   16      4    6
    H3        1      0      0     0      0      0     0      0    8
    H4        0      1      0     1      0      1     0      1    7

There are 8 columns and correlate to some items which have numeric value under each column. The last column with name "CC" is the result column which shows expected result for the particular set of data.

Assumptions:
There are no blanks and Text entries in the data field.
The numerical values are whole numbers.
Your formula may take these assumptions into account. Or you could work out some fantastic solution which works without these assumptions.

Challenge:
Your challenge is to find first non-zero occurrence and then give count for all columns ahead even if they have 0 or any other numeric value.
Note: the formula should work any where in the sheet.

I have not used named ranges but you can use them. I am interested in seeing your formula and thinking more than anything else. And try to work out array and non-array approaches. So far, I have figured out 2-3 approaches which work so I do have a solution with me.
 
Hi Shrivallabha,
There are many ways to solve this... so to kick things off, here is one approach:
=COLUMNS(C3:J3)-IFERROR(MATCH(TRUE,(C3:J3<>0),0)-1,8)

entered with Ctrl + Shift + Enter

Regards,
Sajan.
 
Hello Shri,

How about,

=IFERROR(9-MATCH(0,1/B2:I2*0,),)

For compatible with all versions,

=LOOKUP(9,IF({1,0},0,9-MATCH(0,1/B2:I2*0,)))

Both are Array.
 
Keeping in line with Haseeb's hard-coded '9' columns, I'd suggest using this

=9-MATCH(1,IF(B2:I2,1,),)

However, to keep it flexible, I'll go with

=COLUMNS(B2:J2)-MATCH(1,IF(B2:I2,1,),0)

similar to Sajan's approach

both CSE, and the assumption being there's at least 1 non-zero value in each row (in line with the original challenge)
 
Thank you for your replies guys. They are awesome approaches.

I am seeing the usual suspects here barring few. We all have grown fond with CSE and they are the ones which come to us very quickly. Here's one non-CSE idea which seems to work.

=COLUMNS(B2:I2)-(LEN(SUBSTITUTE(CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2),"-",""))-LEN(SUBSTITUTE(CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2),"-","")+0))

Sam: No worries man, you can post your formula with some additional assumptions. Remember, this is not a practical issue that we are dealing with. We are playing with the formulas.
 
Hi, shrivallabha!
Another approach with array formula:
=9-COINCIDIR(1;ABS(SIGNO(B2:I2));0) -----> in english: =9-MATCH(1,ABS(SIGN(B2:I2)),0)
Regards!
 
Good one, Faseeh.

Re-looking at the challenge is refreshing. Here's one more idea which works without CSE:
=9-LOOKUP(9,MATCH(1,(B2:I2<>0)*1,0))
 
You must log in or register to reply here.
Back
Top