Countif Column & Row combination match

[nagesh.tvsr]

Hi,

Getting error while putting formula of Countif column & Row combination.

Pl. help to correct the formula.

Thanks,
Nagesh.

[Olly]

Try:

Please Login or Register  to view this content.

[shukla.ankur281190]

Try

B5=SUMPRODUCT(($F$2:$O$2=$A$3)*($E$3:$E$13=TRIM(RIGHT(SUBSTITUTE(B$4," ",REPT(" ",50)),50)))*($F$3:$O$13=$A5)) and drag

[nagesh.tvsr]

Both the formulas are working well. Thanks a lot.

[shukla.ankur281190]

Thanks for Feedback and happy to help you

[nagesh.tvsr]

Your formula working fine for the specific requirement. I gave part of the data for better understanding to put formula.

In fact, my file contains the scheduled shift data in different sheet. Also dates are stretched to full month (not up to 10th Jul) and also Category heading is not Category P.

I incorporated your formula and resending the file and not getting the output.

Pl. correct the formula.

thanks,
Nagesh.

[Olly]

Change to:

Please Login or Register  to view this content.

Do you see what I changed, to make your formula work?

[nagesh.tvsr]

Wonderful. Thanks a lot once again.

[nagesh.tvsr]

Hi Shukla & Olly,

Sorry to inform you that both of your formulas giving errors while incorporating in my file.

Since the file size is more, attached .ZIP file. and highlighted (in Yellow) the cells where formulas are required.

Kept separate sheets on your names.

Pl. help.

Thanks,
Nagesh.

[Olly]

You're not even trying to understand and amend these formulae to suit your workbook, are you? You can't simply paste a formula into a different structure of workbook, make NO changes to it, and complain it doesn't work...

Why not try to understand HOW my formula worked, then TRY modifying it to suit your actual workbook. THEN post if you are still struggling. Right now, you're not learning, you're just getting us to do your work for you...

You also need to make clearer your specification of what you are trying to achieve. I can't see where the "Strength | Present | Leave | LOP" column headers affect your outcome? And the "More than 3 days" requirement is unclear.

[nagesh.tvsr]

Hi Olly,

First of all I am very sorry to make to disappointed.

After copying the formula, I have changed the reference to the sheet (DATA) where my information is existing.

I must be doing something wrong.


My data is in "DATA" Sheet, which I would like to pull in to the report.

We have 3 categories of persons i.e. P=Permanent, C=Casual and Y=Temporary who work in 4 shifts i.e. A B C and G.

Strength is the Count of A / B / C / G (their scheduled shift) in Columns EF4:EJ1200 (in DATA sheet). (Note: There are letters A, and A. which are to be treated as A only)

Present is the count of persons who attended in their respective shift data in Columns G4:AJ1200 (in DATA sheet)

Leave data will be updated manually in Columns BX4:DB1200 (in DATA sheet) against each person on each day, which will be reflected in the columns G4:AJ1200 (in DATA sheet). (In the formula I put only L, but it should be AL) (AL=Authorized Leave)

More than 3 days: If a person is absent (shown as UL) for more than 3 days consecutively, we will call him ask for explanation for taking leave without intimation. (UL is Unauthorized Leave). We would like to see how many persons are absent for more than without intimation.

Sorry for wasting your time.

Will be grateful if I can get solution to my requirement.

Thanks,
Nagesh.

[Olly]

OK. Your data structure is horrible!! It's still not entirely clear how you're actually putting this all together...

But see the attached - does this do what you wanted?

[nagesh.tvsr]

Genius!!! Boss.

I never used match and Offset formulas. That is why I could not use properly in my file.

I will try to understand the logic of the formula.

In fact I am developing this format for my other dept. to make their work simple. They are illiterates on computer and doing the work manually.

Need to make few more reports with the same data. Hope you support me and spare little more time in future on this purpose.

Many thanks Olly.

Thanks,
Nagesh.

[Olly]

Glad we got there in the end!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to those who helped you. Thanks.

[nagesh.tvsr]

Hi Olly,

All I did only with your help.

I need little addition to the formula you provided. Earlier you gave solution to sum the data with A*. Now I would like to sum the data that contains either A or B or C or G.

Attached the document where the formula required.

Pl. help.

thanks,
Nagesh.

[Olly]

There are no A, B C or G values in range Data!G3:AJ1200.

I would again encourage you to consider a better data structure - your data is VERY confusing.

[nagesh.tvsr]

Hi Olly,

Pl. help on the above little modification on the formula. I stuck there.

Thanks,
Nagesh.

[Olly]

Did you read my reply?

Your data doesn't seem to match your requirement.

[nagesh.tvsr]

Hi Olly,

Since there is no data in INPUT sheet, Shift info. is not reflecting.

Now I put 3 days data in INPUT sheet, data reflected in the range Data!G3:AJ1200.

I too agree that my data is seems complex but with the knowledge I have I made that format to get the desired output.

Revised file attached. Pl. modify the formula.

Thanks,
Nagesh.