COUNTIFS - Criteria based on a named range.

[paularthur90]

Hi,

This is my first post and was wondering if someone could help me.

I am trying to count the amount of times 2 conditions are met in Excel 2010. The first criteria is to look to see if a value appears in a named range in column C and the second is just to check if the value is equal to a cell in column G.

Is this at all possible I was trying;

Please Login or Register  to view this content.

Stream1 being the named range containing 16 possible cells and A4 being the name of an employee.

I would be really grateful for some help.

Thanks,
Paul

[daddylonglegs]

You can do that - your current formula will return an array of 16 values (1 each for each value in Stream1) so you need a function to sum that array and get the total - SUMPRODUCT can do that and then you don't need "array entry", i.e.

=SUMPRODUCT(COUNTIFS('6-12 E'!$C:$C,Stream1,'0-6 E'!$G:$G,A4))

Note: I see you are referencing two different worksheets in that formula, is that deliberate?

[paularthur90]

Hello!

Thanks you so much for coming back to me so quickly.

This worked perfectly and I understand your explanation.

Yea I sorted the different sheets thanks for spotting it!

Paul