+ Reply to Thread
Results 1 to 17 of 17

Complex countif

  1. #1
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    Complex countif

    Hi, please have a look at the attached picture. Column A is a code for different times of the day (each code representing a different time). Columns E and F translate each code as being either AM or PM. Column B is a list of names.

    What i would like to do is count the number of instances where Neil has an AM code next to his name, a PM code next to his name and the same for Swift.

    This is a simplified version of my problem which contains countless codes and multiple name.

    I need the final formula to basically say:
    "Count the number of instances for Neil where he has a code which represents the AM" (and PM and dito for Swift).

    For example, =SUMPRODUCT(--(A:A="2b"),--(B:B="Neil")) returns all of those which have a code of 2b, but i want to also return everything else which is PM - WITHOUT simply adding 2b + 1k + r4. Ie I want it to determine at runtime which are the PM ones.

    I hope this makes sense! Please help! Any suggestions!!

    Thanks!
    Attached Images Attached Images
    Last edited by brad.tho; 04-03-2008 at 01:38 AM.

  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Try this:

    AM:

    Please Login or Register  to view this content.
    PM:

    Please Login or Register  to view this content.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Hi,

    Handy Hint.

    Quote Originally Posted by brad.tho
    .......returns all of those which have a code of 2b, but i want to also return everything else which is PM - WITHOUT simply adding 2b + 1k + r4...........
    If you got countless codes and multiple name set up an area on the spreadsheet for the formula.

    ....A50="ph")*(B1:B50="Neil"))

    eg Have "Neil" name in G3 and "ph" code in H3 and the formula will look a bit like this

    ....A50=H3)*(B1:B50=G3))

    Then lock in the right cells and copy drag the cells



    If need further help is needed post another post in this thread or PM me
    Last edited by ratcat; 04-03-2008 at 02:28 AM.

  4. #4
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Quote Originally Posted by brad.tho
    .............I need the final formula to basically say:
    "Count the number of instances for Neil where he has a code which represents the AM" (and PM and dito for Swift).......

    I was thinking about the problem and what about this formula

    Please Login or Register  to view this content.
    Last edited by ratcat; 04-03-2008 at 02:46 AM.

  5. #5
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    Not quite!!

    Thanks everyone, and yes they have achieved a result similiar to what I'm after.

    However, your solutions still require hard coding of the time codes. What I specifically want is for the function to determine which time codes it needs to include at runtime... Ie whichever time codes are associated with AM or PM.

    The vlookup() function might be what I'm after but I cant get it to work!

    Does that make sense? Thanks for all of your suggestions so far! Please keep them coming!
    Last edited by brad.tho; 04-03-2008 at 06:28 AM.

  6. #6
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    I think the next suggestion is a upload an example in zip file.

    Not your whole file, just enough for all of us (I mean you too) the understand the goal at hand.


    Cheers

  7. #7
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    Thanks!

    Thanks Ratcat,

    The screenshot in my first post is pretty much what im using, only where the 4 zero's are is where im trying to get the data breakdown to go. There are no other formulas in the screenshot that ive posted.

    Anyway, its attached for good measure. Good luck! Thanks for all your help so far! Keep it up!
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Quote Originally Posted by brad.tho
    Thanks Ratcat,

    The screenshot in my first post is pretty much what im using, only where the 4 zero's are is where im trying to get the data breakdown to go. There are no other formulas in the screenshot that ive posted.

    Anyway, its attached for good measure. Good luck! Thanks for all your help so far! Keep it up!

    Well,..... here it is


    Is that it ???? I looked like there was the code information double up ??

    This what I......hmm workout.

    Edit: I forgot. Just entered the a code then a "AM" or "PM in front of "Swift" name then look at the count in the boxes on the right hand side of the sheet.
    Attached Files Attached Files
    Last edited by ratcat; 04-03-2008 at 07:37 AM.

  9. #9
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    Maybe this?
    It requires you have a vlookup on another column.
    What you must have is a list of all codes so the vllokup works for everything.
    Attached Files Attached Files

  10. #10
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    Quote Originally Posted by brad.tho
    Hi, please have a look at the attached picture. Column A is a code for different times of the day (each code representing a different time). Columns E and F translate each code as being either AM or PM. Column B is a list of names.

    What i would like to do is count the number of instances where Neil has an AM code next to his name, a PM code next to his name and the same for Swift.

    This is a simplified version of my problem which contains countless codes and multiple name.

    I need the final formula to basically say:
    "Count the number of instances for Neil where he has a code which represents the AM" (and PM and dito for Swift).

    For example, =SUMPRODUCT(--(A:A="2b"),--(B:B="Neil")) returns all of those which have a code of 2b, but i want to also return everything else which is PM - WITHOUT simply adding 2b + 1k + r4. Ie I want it to determine at runtime which are the PM ones.

    I hope this makes sense! Please help! Any suggestions!!

    Thanks!
    See the attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    Cool thanks!

    Thanks Teethless Mama!

    I changed:
    =SUMPRODUCT((ISNUMBER(MATCH($A$1:$A$15,INDIRECT(F$17),0)))*($B$1:$B$15=LOOKUP(REPT("z",255),$F$16:F16)))

    to:
    =SUMPRODUCT((ISNUMBER(MATCH($A:$A,INDIRECT(F$17),0)))*($B:$B=LOOKUP(REPT("z",255),$F$16:F16)))

    for when my list is longer...

    but i don't really understand how this works... I cant see where the formula evaluates the code as being AM or PM... Can you explain it to me please?

    I also tried adding another code (eg 4m which is am PM code) into the list of codes and adding a shift on the left, "4m, Neil" but it didn't get included...

    Thanks!
    Last edited by brad.tho; 04-03-2008 at 11:03 PM.

  12. #12
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    This might help everyone!

    Here is a food example which is trying to achieve the same thing as my example, only a little less confusing!

    The question being, how many peices of red and green fruit did mark and david eat each?

    Thanks again everyone!
    Attached Images Attached Images

  13. #13
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Hi everyone,

    I've included both problems with the same layout and idea.

    The idea is a little bit simply but effective. Two formulas first is a vlookup and the second is countifs.

    Add more information (codes and users) to the sheet will be easier and copy down the formula.

    Hopefully this one closer to home.

    Cheers

    Al
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    So close!

    Thanks for your work ratcat - but theres still a little problem! My end spreadsheet cannot have that third column in it... the one with AM or PM... so the tally part of the sheet cannot refer to that column, it has to include the vlookup in the formula itself!

    I've come pretty close myself too today, ive just discovered array formulas, and have found the perfect solution! NEARLY! Again, my example (below) has this extra column which I need to get rid of; but I cant get a formula to work!

    Seriously thanks for your help everyone! Keep it up!
    Attached Files Attached Files
    Last edited by brad.tho; 04-04-2008 at 02:54 AM.

  15. #15
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Quote Originally Posted by brad.tho
    Thanks for your work ratcat - but theres still a little problem! My end spreadsheet cannot have that third column in it... the one with AM or PM... so the tally part of the sheet cannot refer to that column, it has to include the vlookup in the formula itself!

    I've come pretty close myself too today, ive just discovered array formulas, and have found the perfect solution! NEARLY! Again, my example (below) has this extra column which I need to get rid of; but I cant get a formula to work!

    Seriously thanks for your help everyone! Keep it up!
    There is no need for find the perfect solution, you've got it. You mention the column with AM/PM showing. To fix this problem, highlight the column -> right click on the column letter then select "hide". If you need to edit that column later highlight the two column both side of that column right click then select unhide.

    If you need more help I'll upload the example.

    Cheers

    Al

  16. #16
    Registered User
    Join Date
    04-03-2008
    Posts
    15

    Ok...

    Thanks Al,

    That would be a good solution except for one problem...

    once i hide that column, when i add data into a new row, how can i make it automatically add AM or PM without unhiding it? (and without using a macro)

    I'm making this form for someone else, so im trying to keep it as simple for them as possible. Also, adding this information manually would be frustrating!

    Any ideas? Thanks again!
    Last edited by brad.tho; 04-04-2008 at 07:01 AM.

  17. #17
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Well.............I'm starting to get confused.

    Quote Originally Posted by brad.tho
    Thanks Al,

    That would be a good solution except for one problem...

    once i hide that column, when i add data into a new row, how can i make it automatically add AM or PM without unhiding it?.......
    Look at at file "brad tho forum formula" -> sheet "Shift TOD". In Col F in that col is the =VLOOKUP formula copy down that formula to the cell in where you stop entering the information (Shift, Employee), then hide the col.


    Please see wait for a PM.


    Cheers

    Al

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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