Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: Formula Help

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula Help

    Greetings,

    I won do counting for two items, hope and real using mutiple criteria. The hope is a straight one(countifs), the real one i need help to introduce aditional criteria which is do counting when secondary exist in both real & hope. Appreciate any suggestion.

    YEAR GENERAL SPECIFIC BRANCH SECONDARY STAGE GENERAL SPECIFIC BRANCH STAGE 2016
    2016 FLY HI BUSH BBBC-485 HOPE FLY HI BUSH HOPE 4
    2016 FLY HI BUSH BBBC-940 REAL REAL 4
    2016 FLY HI BUSH BBBC-941 REAL
    2016 FLY HI BUSH DRUM-600 REAL
    2016 FLY HI BUSH DRUM-601 REAL
    2016 FLY HI BUSH DRUM-602 HOPE
    2016 FLY HI BUSH DRUM-602 REAL
    2016 FLY HI BUSH DRUM-603 HOPE
    2016 FLY HI BUSH DRUM-603 REAL
    2016 FLY HI BUSH DRUM-604 HOPE
    2016 FLY LOW BUSH DRUM-604 REAL
    2016 FLY LOW BUSH DRUM-605 REAL
    2016 FLY LOW BUSH RANK-818 REAL
    2016 FLY LOW BUSH COOF-1000 REAL
    2017 FLY HI CHRL COOF-231 HOPE
    2017 FLY HI CHRL COOF-231 REAL
    2017 FLY HI CHRL COOF-238 REAL
    2017 FLY HI CHRL COOF-412 HOPE
    2016 CALM HI BUSH COOF-412 REAL
    2016 CALM HI BUSH COOF-441 REAL
    2016 CALM HI BUSH COOF-442 REAL
    2016 CALM HI BUSH COOF-443 REAL
    2016 CALM HI BUSH COOF-460 REAL
    Last edited by dubdub; 2014-12-21 at 02:11.
    TIA
    dubdub

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 978 Times in 908 Posts
    I'm not sure of the format because the forum doesn't retain the formatting. Can you post your example as a spreadsheet?

    cheers, Paul

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi dubdub

    If you are using Excel2007 or later, you can use the Countifs formula for multiple conditions.
    See attached workbook for examples using your data.

    zeddy
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    DubDub,

    If I read your requirements correctly this formula should do the trick if placed in K2:
    =COUNTIFS($A$2:$A$24,K$1,$B$2:$B$24,$G2,$C$2:$C$24 ,$H2,$D$2:$D$24,$I2,$F$2:$F$24,$J2)
    dubdbucountifs.JPG
    You'll note that I've added a 2017 column and changed one of your values (in yellow) to show that the formula is dragable right for additional years. I also added an additional criteria row to show it can be dragged down for additional criteria. However, if you want less or more criteria items you'll have to adjust the formula.

    I would also suggest you create Dynamic Range names for Year, General, Specific, Branch, Secondary, and Stage. Then you won't have to worry about adding rows of data.

    Formula w/Dynamic Range Names: =COUNTIFS(Year,K$1,General,$G2,Specific,$H2,Branch ,$I2,Stage,$J2)

    Here's a copy of my test worksheet w/Dynamic Range Names defined. dubdubhopereal.xlsx

    Now, can you explain your second criteria a little more what do you want if a Secondary is present? Or can you figure it out from the above?

    HTH
    Last edited by RetiredGeek; 2014-12-21 at 07:46.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you RetiredGeek for your reply, i have included definition and example of "REAL" count that I need in the attachment.
    Attached Files Attached Files
    Last edited by dubdub; 2014-12-22 at 05:41.
    TIA
    dubdub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi Paul, and sorry for my late reply. I have included an attachment.
    Last edited by dubdub; 2014-12-22 at 06:00.
    TIA
    dubdub

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Zeddy, and sorry for my late reply. The " REAL" counts is not what i am after, i have included more explanation in my reply to RetiredGeek, i hope it is more clear.
    TIA
    dubdub

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If we can assume that a Secondary will only ever appear once for each of HOPE and REAL, you can use:
    =COUNT(1/(FREQUENCY(IF((Year=K$1)*(General=$G2)*(Specific=$ H2)*(Branch=$I2),MATCH(Secondary,Secondary,0)),MAT CH(Secondary,Secondary,0))=2))
    array entered with Ctrl+Shift+Enter
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2014-12-22)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi dubdub

    If, as Rory says, we can assume that a Secondary will only ever appear once for each of HOPE and REAL, then another way would be to use a 'helper' column to check for duplicates and then use SUMIFS.
    see attached file
    Rory's formula will work of course (although I would have used row 3 rather than row 2 in the formula) as shown in attached file

    zeddy
    Attached Files Attached Files

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi dubdub

    ..I should say, my method requires the data to be sorted, but Rory's doesn't.
    So you should use Rory's method.
    He has a brain the size of a planet.

    zeddy

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I just have a big head (literally and metaphorically) and people infer strange things...
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Rory,

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Thanks, RG.

    I think your hat needs some tinsel for this time of year though.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #14
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Great thanks Rory, unfortunately, the appearance is more than once in each and asymmetric; are you indicating formula will be hard to develop under such situation.
    Last edited by dubdub; 2014-12-22 at 11:30.
    TIA
    dubdub

  16. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post a more representative sample of your data then? It should still be perfectly possible, just a little more complex.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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