Excel Formula

[KristieH]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’m hoping you can help me with a formula. I have multiple tabs in my excel spreadsheet; one is labelled ‘Selection Criteria’. This tab allows the user to list multiple names in column B. I need to input a formula on a different tab which calculates the number of names input into column B of the Selection Criteria tab. For example, if an individual was to list 20 names, I would like this formula to identify all 20 names and insert the number 20 in a different tab. Could you please advise what the formula to do this would be?<o></o>
<o> </o>
Thank you,<o></o>
<o> </o>
Kristie.<o></o>

 

[Trebor76]

Hi ,

Welcome to MrExcel!!

Put the following formula into whatever cell you wish to see the result, just change the range [A2:A10] to suit:

=COUNTA('Selection Criteria'!A2:A10)

HTH

Robert

 

[KristieH]

Great, thanks Robert.

What about if I just want to count the fields that say 'John'?

 

[Trebor76]

=COUNTIF('Selection Criteria'!A1:A10,"John")

 

[KristieH]

Thanks again!

I just have one more question. I have two columns in the Selection Criteria tab. One is name; the other gender. I can use the COUNTIF formula to work out how many Johns there are; however, what formula would I use if I wanted to calculate the number of 'Johns' and the number of these Johns who are 'male'?

Hope that makes sense!

 

[Trebor76]

Hi Kristie,

Assuming the names are in A1:10 and their gender are in B1:B10, (change to suit) try this:

=SUMPRODUCT(--('Selection Criteria'!A1:A10="John"),--('Selection Criteria'!B1:B10="Male"))

Regards,

Robert

 

[KristieH]

Hi Robert,

I tried using the sumproduct formula; however, it's returning a 0 (it should be returning an 8).

I had to leave out the - - as the system wouldn't allow it. Could this why?

 

[Trebor76]

had to leave out the - - as the system wouldn't allow it. Could this why?

Yes, this is the issue and the system will allow it as "--" are simply two minuses

 

[T. Valko]

Hi Robert,

I tried using the sumproduct formula; however, it's returning a 0 (it should be returning an 8).

I had to leave out the - - as the system wouldn't allow it. Could this why?

What system is that?

Are you using Excel or some other spreadsheet application (Google Docs, Open Office Calc)?