# Thread: Countif for various possibilities

1. ## 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!"

Grant

2. Hi

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

zeddy

3. 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

4. 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

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

GrantHorizons (2015-03-04)

6. 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. 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

8. 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

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

GrantHorizons (2015-03-05)

10. 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. Hi Grant

If you have any other issues, please come back.
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. 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 https://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. Originally Posted by zeddy
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. 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. 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.

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:
Select meal package..
Select this option to show a selection menu.

Now try right-clicking in column [AT]
Select Camping package..

Now try right-clicking in column [BH]
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

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

GrantHorizons (2015-03-14)

17. 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. 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 Last

#### Posting Permissions

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