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

Extracting occasion occurred..

dawa,
You can upload files directly to the forum now. I can't access outside links, so that would help me. Otherwise, someone else may come along who can help you.
 
Hi Luke ,

Please check the file now. The requirement is explained inside the workbook.

Narayan
 

Attachments

  • Finding how many occasion of Sl and UL.xls
    33.5 KB · Views: 11
Formula in I14:
=SUMPRODUCT(((INDEX(D3:F33,,MATCH(I12,D1:F1,0))="SL")+(INDEX(D3:F33,,MATCH(I12,D1:F1,0))="UL"))*(((INDEX(D2:F32,,MATCH(I12,D1:F1,0))<>"SL")+(INDEX(D2:F32,,MATCH(I12,D1:F1,0))<>"UL"))=2))

Formula in I13, if you need it:
=HLOOKUP(I12,D1:F2,2,)
 
i have checked it out, the formula is awesome but its only extracting "SL". I need to combine both SL and UL. Any further help will be more appreciated.
 
dawa said:
i have checked it out, the formula is awesome but its only extracting "SL". I need to combine both SL and UL. Any further help will be more appreciated.
Click to expand...

Are you sure about that? When I plug the formula in to your workbook you posted, it's picking up the "UL" just fine.
 
na i have checked it out. its not extracting Ul please help. I have put the formula in cell I14.
 
dawa said:
na i have checked it out. its not extracting Ul please help. I have put the formula in cell I14.
Click to expand...
I'm not sure what I can do. I've uploaded your workbook with my formula in it. For 80454, there are 2 occasions, on Jan 6 and 15 Jan. If this is not correct, please upload a new workbook with formula showing the wrong answer.
 

Attachments

  • ExampleLeave.xls
    26.5 KB · Views: 4
Hi Luke ,

The problem is , as usual , with boundary conditions ; try putting SL or UL in row 33 , row 3 and so on.

Using a helper column , this can be solved quite simply , since taking only the first occurrence of repetitions is straightforward.

Narayan
 
Hi Narayan,
Thanks for the info. However, in the workbook I uploaded, if I change E3 or E33 to be "SL" (or "UL"), the counter increases. Granted, the ranges inside the formula should be adjusted if the data grows/shrinks beyond row 33.

Or have I just completely misunderstood? :(
 
i tried it out again but its only extracting SL, I need to include UL too..... 80539 has 3 occasion, 1-2jan-Sl(1st occasion), 6 jan-UL(2nd occasion), 13 jan-SL(3rd occasion).
 
Hi ,

When I put in Luke's formula , I get 3.

Do not look at the name , since you do not have a formula in I13 ; see what employee number is in I12 , and verify whether the value in I14 matches that employee.

Narayan
 
Dawa,
Could you upload a workbook with my formula in in, showing the incorrect value?
 
Please check the upload file.
 

Attachments

  • Finding how many occasion of Sl and UL.xls
    32.5 KB · Views: 6
Any chance that your calculation mode is set to Manual?

Check under Formula Ribbon | Calculation --> It should be Automatic otherwise you'll have asynchronous results.
 
As shrivallabha said, might want to check your calculation settings. In 2007, its under Formulas ribbon, Calculation group, Calculation Options -> Automatic. See image for what I saw when I open your file.CountFormula.png
 
Thanks Luke, always as usual very helpful. I too want to become an Excel Ninja. I know few formulas but not the one that you know. What is the meaning of <> in the above formula and could you please send me the details of how you made the above formula so I can exactly know how you create it.
 
The <> marking means "not equal".

As for how it works, the three functions are SUMPRODUCT, INDEX, and MATCH. I needed to first be able to pick which column to look at based on Emp No. This is done by giving the INDEX function the entire range, and then I need to provide a number saying which column within that range to look at. That is accomplished with the MATCH function. The MATCH takes the Emp No and looks at Row 1, and tells me where that column is.

So, now that I have the correct columns to look at, I build some arrays. These arrays are True/False arrays checking if the cell value is equal to SL or UL. In Boolean logic, adding the two arrays together when they have the same range works the same as an "Or" type function. The latter two arrays are offset by 1 row since we are looking for consecutive ranges.
The sumproduct multiplies all the arrays together, and whenever we have a True (aka, a 1) in the same "row" across all the arrays, it increments our SUM by 1, and then gives us the total count.

You may find it helpful to use the Formula Auditing tool, or select portions of the formula and press F9 to have XL calculate just that bit.
 
You must log in or register to reply here.
Back
Top