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

countif in criteria

Afarag

Member
Dears,

i have i column contain some data between this data a date found like:
Shifts per day 13/04/2014
that i want to extract this date in another column to insert pivot and countif "DO" in each date
 

Attachments

  • sheet.xlsx
    81 KB · Views: 7
Hi Afarag,

If I have understood your problem correctly, you can use below formula in A2 and copy down:

=IF(ISNUMBER(SEARCH("Shifts Per",B2)),RIGHT(B2,10)+0,"")

Regards,
 
@Audi

sorry for non clarifying,
according to your formula i face an error #VALUE!,
after extracting it in the range for (B: P) is founded word of "DO" that i want to count
say after extracting the date i have 5 days from 15/4/2014:20/4/2014 i ask to count "DO" in each day
 
@Afarag

I think there are two parts of your problem.
1. Extract Date
2. Count Do.

Now, 1. in which column you want to extract date and from which column you want to extract date.
2. Where you want to count?

Can you upload a sample of the report you want?

Regards,
 
In A3,

Use CSE Formula as..
=LOOKUP(99^99,SUBSTITUTE($B$2:B3,"Shifts per day ","")*1)

Drag downward.. to fill all the Dates.. to Create your Pivot..

EDIT: No CSE Required.. :)
 
Last edited:
Using @Debraj formula to fill column A with dates, use below method to generate your count of Do report.

1. Extract unique dates using advanced filter to another location.
2. Use below formula to count "DO"

=SUMPRODUCT((B4=Sheet1!$A$5:$A$2835)*("DO"=Sheet1!$C$5:$P$2835))

Extracted date on Sheet2 B4:B10.

Regards,
 
@Somendra Misra

i want to extract the date from column B to column A
and after extracting i want to make a pivot with the date to count "DO"
the simple file attached in the first post

@Debraj

unfortunately i face an error with your formula


Gratefully,
 
@Afarag

I don't know why you are facing problem with Deb formula. See the attached pic below extracted dat based on Deb formula and as explained in Comment #6.
Capture.JPG


Regards,
 
Hi Afarag

either change your system regional setting, default Date format to DD-MM-YYYY from mm-dd-yyyy...

or try this one..
=LOOKUP(99^99,REPLACE(MID(SUBSTITUTE($B$3:B3,"Shifts per day ",""),4,3)&SUBSTITUTE($B$3:B3,"Shifts per day ",""),7,3,"")+0)
 
Back
Top