countifs with specific name between two dates

    Hello!


    Hoping to get some help, my excel skills are not what they used to be but think i came to the right place!


    I have attached a sample data sheet which will help explain what I am trying to do. I'd like to count the number of times a name appears in the data set between two specific dates.


    For the first name (bob) - On the "Summary" Tab, I want to count the number of times his name appears in the "Data Tab" in column I between the dates 8/3/2019 and 8/30/2019 - the dates are in column D.


    I'm assuming I'd use the countifs function, but had a few issues. Please let me know if I can provide additional information!

    Thank you!


    Is there a way to just include the dates i want in the formula itself instead of having to reference the dates separately?

    Glad you could fix your problem

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from AkbarAfshar

    Please correct my formula

    =COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Trade SS"})

    Hello,


    Am not a Moderator ...


    But you should start your own thread ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Correction below :


    Code
    =SUM(COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"}),COUNTIFS(DIS!E:E,{"Group Loan","GMRB"}),COUNTIFS(DIS!F:F,{"Trade","Trade SS"}))

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Try this:


    Code
    =SUMPRODUCT(--NOT(ISNA(MATCH(DIS!B:B,{"Taimani","chaman","Khushalkhan"},0)))*--NOT(ISNA(MATCH(DIS!E:E,{"Group Loan","GMRB"},0)))*--NOT(ISNA(MATCH(DIS!F:F,{"Trade","Trade SS"},0))))

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

    Love 1
    Quote from GlennUK

    Try this:


    Code
    =SUMPRODUCT(--NOT(ISNA(MATCH(DIS!B:B,{"Taimani","chaman","Khushalkhan"},0)))*--NOT(ISNA(MATCH(DIS!E:E,{"Group Loan","GMRB"},0)))*--NOT(ISNA(MATCH(DIS!F:F,{"Trade","Trade SS"},0))))

    Thank you so much Mr GlennUK , You hit the nail, Now I want to extend my furmula by adding (DIS!C:C,"<200000") so could you please add this part to above formula?

    Also I need to fix below formula:


    =(SUM(SUMIFS(DIS!C:C,DIS!B:B,{"Taimani","Chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Services"},DIS!C:C,"<200000")))

    I think this might do it:


    Code
    =SUMPRODUCT(--NOT(ISNA(MATCH(DIS!B:B,{"Taimani","chaman","Khushalkhan"},0)))*--NOT(ISNA(MATCH(DIS!E:E,{"Group Loan","GMRB"},0)))*--NOT(ISNA(MATCH(DIS!F:F,{"Trade","Trade SS"},0)))*--(DIS!C:C<200000))


    and:


    Code
    =SUMPRODUCT(--NOT(ISNA(MATCH(DIS!B:B,{"Taimani","chaman","Khushalkhan"},0)))*--NOT(ISNA(MATCH(DIS!E:E,{"Group Loan","GMRB"},0)))*--NOT(ISNA(MATCH(DIS!F:F,{"Trade","Services"},0)))*--(DIS!C:C<200000))


    I haven't checked them in Excel ... let me know if they work.

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

    Love 1

    Thank you so much dear Mr. Glenn. It works properly.

    Dear Mr.Glenn could you please fix below formula or send me a useful formula instead:


    =(SUM(SUMIFS(DIS!C:C,DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Services"},DIS!C:C,"<200000")))

    @ Arsoy



    You should start your own thread ... and if possible attach a sample file :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from AkbarAfshar

    Dear Mr.Glenn could you please fix below formula or send me a useful formula instead:


    =(SUM(SUMIFS(DIS!C:C,DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Services"},DIS!C:C,"<200000")))


    Hi, sorry, I didn't realise you'd left another query. How about:

    =SUMPRODUCT(--NOT(ISNA(MATCH(DIS!B:B,{"Taimani","chaman","Khushalkhan"},0)))*--NOT(ISNA(MATCH(DIS!E:E,{"Group Loan","GMRB"},0)))*--NOT(ISNA(MATCH(DIS!F:F,{"Trade","Services"},0)))*--(DIS!C:C<200000)*DIS!C:C)

    ... but, do you realise that you've asked for a total, but excluding values >= 200000?

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

    Love 1

    @ Arsoy


    For the second time :


    Make sure to start your own thread ... and if possible attach a sample file

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login