Counting cells in offset column based on contents of another column.

Hi all!

Could anyone advise me on my formula problem please?

I have some survey data, see example below, and need to count the number of 'Agrees' in A2 when the role is selected in A1. I.e. If I select the role 'Team Member' in A1, I want A2 to tell me how many of those Team Members responded with 'Agree'. SUMIF appears to do this for numbers, but I can't find anything that will just give me just the count of cells which meet the criteria.

Column A Column B
Manager Agree
Team Leader Agree
Team Leader Agree
Team member Agree
Supervisor Agree
Team member Agree
Team member Strongly agree
Team member Strongly agree
Team member Agree
Team member Strongly agree

Can anyone help please?

Thanks for looking from sunny London!

Chris.

Maybe a Sumproduct formulation?

Try something like:

=Sumproduct(--(A2:A10="Team Member"),--(B2:B10="Agree"))

You can replace the text strings above with actual cell references containing those strings....

adjust ranges to suit.

If that's your picture I'm so embarrassed that a little baby is helping me!!

I put the formula in as suggested and it's just returning zero, when based on the data in the example picture attached, it should return 2.

Any other thoughts please?

Thanks again for looking.

Chris.

You took out the double unaries (--) . They need to stay...they convert the resulting True/False arrays to 1's and 0's, respectively so that Sumproduct can do the math...

alternatively, you can use:

=Sumproduct((A5:A16=A1)*(D5:D16="Agree"))

Both work....and despite my claim to be 'useful' with Excel formulae I'm still trying to work out how and why!

Many thanks, your advice is much appreciated.

Chris.