Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Exclamation Countif for various possibilities

    Hi,

    I have a spreadsheet that is for an event registration system (20 events worldwide annually).

    As time goes on we add new tidbits to the events, upgrade options etc, and now I've run into an issue.

    I have columns in Excel for food, accommodation etc. We have added vendors, who may have multiple meals and accommodations required for staff. The order comes in one row only.

    Values in a food cell are for instance: (// denotes comments to clarify)

    8 meal package, Thursday dinner to Sunday breakfast // these were for the original system for attendees)
    1 - 8 meal package, Thursday dinner to Sunday breakfast // vendors
    up to
    12 - 8 meal package, Thursday dinner to Sunday breakfast // vendors

    I need to count the total meal packages! The first above is 1, and second is also 1, and up to 12 meal packages ordered in one cell.

    Total here is 14.

    Cells are named,
    top of the column is named: Food1_Begin
    bottom of the column is named: Food1_End
    Food1_name is a cell that contains the base value: 8 meal package, Thursday dinner to Sunday breakfast

    I WAS using this:
    =COUNTIF(Food1_Begin:Food1_End,+Food1_name) to get a total.

    BUT it doesn't count the additional meals in the new system: "1 - " to "12 - "

    So I need to: countif Food1_name exists in the cell, (for the "no 1 - " case) and count the numbers 1 - up to 12 - !

    (The 1 - can be changed to 1 space or 1 x or whatever makes it easier, BUT no I can't change the no number value to 1 - )

    scares me just thinking about it... I am NOT a spreadsheet expert, this is the only one I have - it works very well and does a lot, but it's taken me many years to get it to where it is now.

    Hopefully someone will say - "oh that's easy!"

    thanks VERY much for any help you can give me!

    Grant

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    ..it is much easier and quicker for us to help if you attach a sample spreadsheet.


    zeddy

  3. #3
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Talking

    Hmmm... not so easy or I would have, but here it is - VERY complicated sheet. I've stripped obviously not needed stuff as best as and sanitized the data.

    Hopefully this will help...

    On the Dashboard sheet:
    Food1_name is cell J101 that contains the base value: 8 meal package, Thursday dinner to Sunday breakfast
    RESULT I'm looking for is at cell: E65, gives the total of, in this case the number of "8 meal package, Thursday dinner to Sunday breakfast"

    Data comes from the Registration sheet:
    top of column AH, cell 3 is named: Food1_Begin
    bottom of column AH, cell 112 is named: Food1_End

    PROBLEM value cells are in column AH, see AH95 and 96 for the 1 - 8 meal etc sample, (also AK97 for another meal option). EASY to count cell values are in AH 89-90 for "no 1 - " and loads more samples at AH4-AU75.

    Issue to be clear - totalling the number of meals ordered in column AH, when they are in ANY OF the following formats:

    8 meal package, Thursday dinner to Sunday breakfast
    1 - 8 meal package, Thursday dinner to Sunday breakfast
    2 - 8 meal package, Thursday dinner to Sunday breakfast
    = 4

    (sample data in red)

    Sheet attached! No groans please, it's ugly but it works!

    thanks, Grant
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Grant

    Couldn't have done this without your example file!

    In the attached file, I have added a custom Function named foodCount.
    On you sheet [Registration Sheet orig] in row 112 of your example file, just change your =COUNTIF( ) formulas to =foodcount( ) formulas.

    For example, change..
    =COUNTIF(Nofood_Begin:Nofood_End,+Food0_name)
    to
    =foodcount(Nofood_Begin:Nofood_End,+Food0_name)
    etc etc.

    I have done this in the attached file, for several food columns.

    To test this, on sheet [Registration Sheet orig], scroll to row 97.
    You can see the entry in cell [AH97] and the total in cell [AH112]
    Now change the "2 - 8 meal package, Thursday dinner to Sunday breakfast" to
    "6 - 8 meal package, Thursday dinner to Sunday breakfast"
    etc etc and see the corresponding change in the totals cell [AH112]

    This should be robust enough for you to use.
    Or Maud, our custom Functions expert, could do better.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    GrantHorizons (2015-03-04)

  6. #5
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks zeddy, that works, and VERY nicely documented too! That's such a pleasure to see. And I'd never heard of custom functions, so learning something new.

    But it doesn't work consistently in the camping columns (I can't see why it wouldn't, but it doesn't.) e.g. cell as97 has a 1 - Camping... and it's not counted. Copy that cell into AT98 and it counts it fine!?? BUT it shouldn't - as it's the wrong name/description. (But I see you're just testing for length, so probably not an issue - it SHOULD always be the correct description in each column.)

    And I just noticed it's not counting AK97 1 - 4 meal package. Change it to another value and no change, but it is counting the non # - values above.

    In the food columns if you change a 1 - 8 meal package to just 8 meal package it no longer counts it at all. i.e. If I change ANY # - to the plain value, it stops counting it. Change any plain value to # - and it stops counting it.
    Is there a way to force a recalculate? I tried ctrl-shirt-alt F9 but no joy.

    So I think we're close, but not quite there.

    Thanks for your help, it's MUCH appreciated!

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Grant

    Ooops
    In the code, change the line
    zLen = zLen + 4
    to
    zLen = zLen + 3
    ..and it should then work.

    Reason:
    'if a cell has an entry like "12 - 8 meal package, Thursday dinner.." or
    "5 - Camping Thursday, Friday and Saturday night, per PERSON"
    then the length of the cell text of this type will be at least (zLen+4) characters,

    But the line of code that actually checks for this is testing for a 'greater than' value (i.e. using > than)
    ..So we should be testing for a line that is 'greater than 3 characters extra' (i.e. at least 4 or more) i.e.
    If Len(zEntry) > zLen Then 'test if entry is LONGER than just the food name

    see updated attached file with this fix in.

    Your file could be simplified greatly, but I thought I'd keep it as similar as possible for you.
    Perhaps the function should be re-named to use =mycount(range, item)???

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-03-05 at 12:43.

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi KZ

    ..also, I didn't put the new formula in [AK112] in the first example file (it's still the old COUNTIF formula!).
    So, if you change the COUNTIF to =foodcount( it should give you the correct result when you amend the AK97 1 - 4 meal package entry etc etc.
    So check all the formulas in row 112 are now =foodcount rather than =COUNTIF

    I have attached an update file, v3, as I accidentally left 'filters' on in version v2 attached previously. (I used the 'filter mode' to check the number totals).
    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-03-05 at 12:46.

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

    GrantHorizons (2015-03-05)

  10. #8
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Excellent, that works!

    Huge thank you for your efforts on this, I'd NEVER have got it to work!

    Now all I have to do is work out how to move that function to all my other sheets... but I think I can do that ok.

    thanks again, kudos!

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Grant

    Many thanks for your feedback.
    If you have any other issues, please come back.
    We can help you tweak your file a lot more.
    Like using automatic custom right-click menus to choose your items when you are in particular columns.
    And show you more nifty Excel stuff.

    zeddy

  12. #10
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for the offer!

    And I do have an issue with the above function sadly.

    IF there is NO value on the dashboard e.g. for Camping3, then I get a Value# error.

    I've tweaked the function to:


    ' function to count various quantities of food, camping and accommodation options
    ' by zeddy at http://windowssecrets.com/forums/sho...-possibilities

    Function optionCount(range, optionname)

    Application.Volatile 'allows calculation refresh

    zRange = range 'count range
    zOption = optionname 'e.g. "8 meal package, Thursday dinner to Sunday breakfast"
    zOption = UCase(zOption) 'convert to UPPERCASE (e.g. "no Food" > "NO FOOD" )

    zLen = Len(zOption) 'e.g. 51; length of Option name description ;"8 meal package,.."
    'NOTE:
    'if a cell has an entry like "12 - 8 meal package, Thursday dinner.." then the length
    'of the cell text of this type will be at least (zLen+4) characters,
    'so we can update the test value..
    zLen = zLen + 3 'e.g. 55
    'We need this to be able to distinguish the "no food?" entries

    j = UBound(zRange) 'e.g. 109; equals number of rows in range

    optionCount = 0 'initialise count

    For i = 1 To j 'loop through all entries in range
    zEntry = zRange(i, 1) 'fetch cell contents (as stored in array zRange )
    zEntry = UCase(zEntry) 'convert to UPPERCASE
    If zEntry = zOption Then 'cell contents exactly match the food name, so..
    optionCount = optionCount + 1 '..add 1 to food count
    End If 'end of test for EXACT match

    If Len(zEntry) > zLen Then 'test if entry is LONGER than just the food name
    zNumber = Trim(Left(zEntry, 2)) 'e.g. must be like "3 - 8 meal package, Thursday..."
    optionCount = optionCount + zNumber 'add the detected number in first two characters of name
    End If

    Next 'process next entry in specified range

    End Function


    Any thoughts on dealing with the case of an empty value in the food/camping/accommodationName cells?

    thanks! Grant

  13. #11
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Grant

    Like using automatic custom right-click menus to choose your items when you are in particular columns.
    And show you more nifty Excel stuff.

    zeddy
    The menu etc items aren't chosen here, they are imported from a flat text file created by a cgi script.

    I enter initial values on the webpage, (and copy them to the dashboard) someone registers, a "row" is created in the text file, at some point we import them into Excel and the totals etc are all automatic from there.

    Most important is to make calculations as easy as possible and get the most results/info possible.

    There is also a t-shirt order page that gets the values from the dashboard sheet and fills in an order form to send to the tshirt printers for instance. Probably uglier than it needs to be too- but it works, just tricky to make changes.

    If you'd like to get into this I'd have to go offline for more details, I don't want to put the whole spreadsheet on the web.

    ANY help is greatly appreciated! Excel is not my area of expertise!

    thanks, Grant

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Grant

    Any thoughts on dealing with the case of an empty value in the food/camping/accommodationName cells?
    I have fixed that, but I'm working on some additional simplifications.
    Will post an updated file tonight or tomorrow.

    zeddy

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Grant

    Version4 attached. In this version, I have simplified the function to be
    =myCount(item)

    So now the formula in the totals row112 can be simply copied across the whole range
    i.e. in cell [AG112]
    =myCount(AG3)
    ..then copy this formula cell across from [AG112] to [BO112]
    NOTE: you must use cell address AG3 in the first formula!
    This makes it a lot easier.
    The new function works out the named ranges required.

    Now, the new thing added:
    A 'context-sensitive' right-click custom menu!
    In the sample file attached, put your cellpointer in say, any cell in column [AJ]
    Now right-click the mouse, and look at the first two items in the right-click menu:
    The first menu item is
    Select meal package..
    Select this option to show a selection menu.

    Now try right-clicking in column [AT]
    The first menu item is
    Select Camping package..

    Now try right-clicking in column [BH]
    The first menu item is
    Select Accommodation package..

    NOTE: if no descriptions have been set on the [Dashboard] sheet, they WON'T appear in the corresponding right-click menu!!

    NOTE: the second option in the right-click menu, Save, has been added for convenience!

    zeddy
    Attached Files Attached Files

  16. The Following User Says Thank You to zeddy For This Useful Post:

    GrantHorizons (2015-03-14)

  17. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Grant,

    From someone who has an understanding of VBA, I can appreciate the phenomenal piece of work Zeddy has provided. I know you are most grateful but I'd be leaning heavily on that "Thanks" button for this one!

    Maud

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    GrantHorizons (2015-03-14)

  19. #15
    Lounger
    Join Date
    Mar 2007
    Location
    Canada
    Posts
    32
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Zeddy, that's awesome! Works a treat for the count, and much simpler indeed.
    I have that working fine in a live worksheet! Thanks a bunch!

    --------------
    But the menu item bit doesn't work correctly, don't know why. It works perfectly in the downloaded sheet, giving correct menu options in the correct locations, but obviously I've done something wrong in the copying modules across to a live sheet.

    I opened both worksheets, opened vba, dragged modules. And I get the same menu item everywhere on the entire sheet. Sometimes it's "select Camping" and the options are "1 - " etc. If I close it then reopen, it may show "select meal package" and the options are "1- 6 meal package..." etc . No apparent reason for change, and won't always change. Finally, if I select a menu item, it only puts in "1 - " or up to "10 - ".

    --------------
    NOTE: you must use cell address AG3 in the first formula!
    This makes it a lot easier.
    The new function works out the named ranges required.
    Does this mean there is an issue if I add a new column before AG3? From what I can understand in the code, I only need to make sure the named cell "Food1_begin" is referenced. Correct?

    -------------------

    thanks again, awesome job!

    And yes Maudibe, if I could give multiple thanks I would

Page 1 of 3 123 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
  •