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!

  • Hello,


    There are many possible solutions ....:)


    Attached is your test file


    Hope this will help

    Files

    • Test hahsj.xlsx

      (12.26 kB, downloaded 45 times, last: )

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

  • 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 "Like" icon ...just below...in the bottom right corner...:)

  • Correction below :


    Code
    1. =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 "Like" icon ...just below...in the bottom right corner...:)

  • Correction below :


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

    Thank you for your suggestion but unfortunately it did not work so please instruct me by a new formula of countifs

    Attached is for your information

  • Try this:


    Code
    1. =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
    1. =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
    1. =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.

  • 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")))

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

  • @ 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 "Like" icon ...just below...in the bottom right corner...:)