Excel again...

DrPhil

Outstanding Member
Joined
Jul 19, 2006
Messages
29,378
Reaction score
20,036
Points
7,907
Location
Ireland
I have a check list detailing staff attendance at annual conferences over the past 11 years.

I want a tally at the side to quickly show the boss how many conferences each person has attended.

Each person has a tick (windings font) showing if they attended.

I want excel to count the ticks. It shouldn't be so hard, but I have used every variation of count functions to match the ticks, or COUNTA to just count the boxes that aren't blank, and it won't give me the right figure. For some bizarre reason it gives me 9 every time. Even if I go back and add or remove ticks, it's still 9. Out of a range of 11 cells.

I'd prefer it to count cells containing anything, as the boss is likely to update the table at some stage but use a Y instead of a tick etc.
 
I think you can use sumif with 2 criteria but I am on my phone so unable to check. Will have a look later when I get to work, more time to play about then ;)
 
You could use openoffice calc:
eg. if Y or blank is in columns C, D and E do:
=SUMPRODUCT((ISTEXT((C1:E1))))

and copy the formula down to the other rows.
 
That's giving me 0 as a result.
Did you try it in Open Office Calc? It worked for me in the free Open Office Calc :).

Or another way: export the data from excel as csv, run a little program to add the non blank columns, import the data into excel as csv. Or export the data, import into open office calc, export back.
 
CountA should do the job and does for me

upload_2016-10-12_11-1-23.png
 
Did you try it in Open Office Calc? It worked for me in the free Open Office Calc :).

Or another way: export the data from excel as csv, run a little program to add the non blank columns, import the data into excel as csv. Or export the data, import into open office calc, export back.

Sorry I'm in work, don't have Open Office and can't install it.
 
You sure those blank cells are blank?

And silly question - have you hit refresh?
 
COUNTA works for me

as IMBW said, are you sure the cells are blank
 
Definitely empty. Have typed into them and deleted to be sure. Also cleared contents.

To make it even stranger, if I delete all the ticks in a particular row, it still tells me the answer is 10.
 
Refresh?
 
I've been refreshing with no improvement.

Now I found the update automatically option and ticked it, and it works.

Next task is to find the buffoon that unticked that option and strangle them.

Why it kept giving me 10 as an answer is a mystery though.
 
Yep - seemed like a classic refresh issue. Another Excel case closed :)
 
Not that it matters now, but there is a difference between 'Refresh' and 'Recalculate'. The former updates date references from within the Data tab. It's the latter (Formulas-Calculate Now) which DrPhil required. I just wonder if he was using the right one?
 
Yes, I know the setting was wrong: hence it started working when he reset it. But to recalculate manually, was he using F9 or the Refresh button in Data? I'm simply suggesting, probably wrongly, that since Refresh and Recalculate are different, he may have used the wrong one.
 
I'm sure the tick is actually the letter R in webdings font? I'm not by a PC at the moment to confirm. Pretty sure you can do COUNTIF([range],"R")
 
I'm sure the tick is actually the letter R in webdings font? I'm not by a PC at the moment to confirm. Pretty sure you can do COUNTIF([range],"R")

Yes, I was assuming that he would just go to a cell with a tick in and cut and paste the contents from that cell into the countif statement.

Cheers,

Nigel
 
It's a u with an umlaut iirc but in any case the requirement was to capture anything.
 
Yes, I was assuming that he would just go to a cell with a tick in and cut and paste the contents from that cell into the countif statement.

Cheers,

Nigel
Tried that too. But that wouldn't help if the boss used a Y instead of a tick (which is a little u with an accent on it via wingdings).

Anyway, working now as the COUNTA and refresh/calculate options have calmed themselves.
 
Sorry missed the required to count anything, not just ticks.

So I don't understand why COUNTA doesn't work for you then - works fine for me, seems to work for everyone else, just not you.

The only reason it wouldn't work is if the blank cells aren't blank - they have a space in or something.

And if you are not enforcing them to be blank because you end up with a requirement like

If attended - enter anything you want
If not attended - enter anything you want
Count all cells with something in them

If you want to allow 'attendance' to be marked with anything then you MUST enforce that 'non-attendance' is a null cell. Otherwise you have no hope of algorithm to count 'attendance'.

Cheers,

Nigel
 

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom