+ Reply to Thread
Results 1 to 6 of 6

COUNTIF and INDIRECT

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    4

    COUNTIF and INDIRECT

    Hi All,

    Having a bit of a problem with one of the longer formulas I am using for a new template that I am building.

    Long story short I'm looking to return a value based on 3 different criteria from three different ranges that are determined by the days of the month. e.g. I want to find out what customer dealt with what client, the nature of their business and I want it to be determined by the day in question.

    So I have written the following and it is returning 0.

    =COUNT(IF((INDIRECT("Input!H"&L6&":H"&L7)=G$1)*(INDIRECT("Input!I"&L6&":I"&L7)=$A4)*(INDIRECT("Input!J"&L6&":J"&L7)=$A$3),(INDIRECT("Input!K"&L6&":K"&L7))))

    I have a formula that I am already using

    =COUNT(IF((Input!$I$2:$I$1982=Consolidated!$A4)*(Input!$H$2:$H$1978=Consolidated!F$1)*(Input!$J$2:$J$1977=Consolidated!$A$3),Input!$K$2:$K$1985))

    which is returning the value that I want, but it is the total for the month rather than the days in question. Hence the attempted INDIRECT in the first formula above.

    So if anyone can help me I would appreciate greatly. I'm sure its something relatively simple, but I cant see it.


    Guys I appreciate any help you can give and if you need me to provide more colour on the above formulas to help you help me, then I will! :-)

    All the best


    Simon

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIF and INDIRECT

    Maybe a sample workbook might help us to better understand?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-20-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIF and INDIRECT

    Hi,

    So I have attached a rudimentary representation of the sheet I have been working on in the office.

    This sheet has the basics of what I'm looking for, i.e. I want to know how many time client a was rejected by bank b on the 14th. My aim is to achieve this by using countif with three variables, and an indirect linked to the row start and end, dictated by the date in question.

    The formula I tried is up above, but it keeps returning me a zero even though I know that there is a value to be returned.

    Again any help would be much appreciated.

    Thanks,

    Simon
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIF and INDIRECT

    Does this work?

    =COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I11,$E$4:$E$20,"Rejected",$F$4:$F$20,DAY($N$8))

    in J11, copied down and across

    ---------- Post added at 04:55 PM ---------- Previous post was at 04:47 PM ----------

    Actually, I just noticed you have a table for Filled and one for Rejected.

    You can then use this in J11:

    =COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I11,$E$4:$E$20,$I$10,$F$4:$F$20,DAY($N$8))

    and this in J16:

    =COUNTIFS($C$4:$C$20,J$9,$D$4:$D$20,$I16,$E$4:$E$20,$I$15,$F$4:$F$20,DAY($N$8))

    watch for extra spaces in your cells... they will cause you to get 0 results....

  5. #5
    Registered User
    Join Date
    08-20-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIF and INDIRECT

    Hi,

    I have never used the COUNTIFS formula before so I just want to confirm your methodology here.

    Your using the COUNTIFS to consolidate the formula I have above and then use the last COUNTIF with the DAY to replace the INDIRECT function?

    Simon

  6. #6
    Registered User
    Join Date
    08-20-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: COUNTIF and INDIRECT

    By the way this worked, so you are a genius

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1