Counting cells

[Anton1983]

Hello,

In one column (B), users can choose between 'Essential' and 'Desirable'. In another column (C), users can choose between 1 and 5. Do you have a formula for counting the numbers of Essentials in column B and the number of 1s in column C, etc.? It's one formula I need, for doing both, e.g. The number of essentials AND the number of 1s.

Columns B and C, where the data we want to count is, is in a tab called 'Person specification', but we are counting from another tab, 'Shortlisting'.

 

[tyija1995]

Hey,

Try a COUNTIFS function:

=COUNTIFS('Person specification'!B:B,"Essential",'Person specification'!C:C,1)

You can write that in Shortlisting tab and it pulls the information from Person specification tab.

 

[Anton1983]

It isn't counting. Does it matter that we're using drop downs?

 

[tyija1995]

It isn't counting. Does it matter that we're using drop downs?

Drop down won't affect it - so your essential/desirable values & the numbers 1-5 are in the "Person specification" worksheet correct?

It should pull through fine. Do you have your calculation set to manual?

 

[Anton1983]

Yep, the essential/desirable values and the numbers 1-5 are in the 'Person specification' worksheet.

How do I know if the calculation is set to manual?

 

[tyija1995]

Yep, the essential/desirable values and the numbers 1-5 are in the 'Person specification' worksheet.

How do I know if the calculation is set to manual?

If you go to File -> Options -> Formulas -> Calculation options will list it, change to "Automatic" if it is not already.

 

[Anton1983]

Any way of sending you an attachment?

 

[tyija1995]

I've sent you a PM so we can try sort it out through there - I am not sure why it isn't working for you currently though, so taking a look at the file might be a good idea.

EDIT: Unless you want the following:

=COUNTIF('Person specification'!B:B,"Essential")+COUNTIF('Person specification'!C:C,1)

If that works then I misunderstood from the get-go

 

[Anton1983]

I can't see where to attach the file...

Any ideas?

 

[tyija1995]

I can't see where to attach the file...

Any ideas?

I think you have to upload it to dropbox first then link it back here?

Try the formula I edited in my last post; if that doesn't work then see my private message I sent you and we can go from there.