carrach

I have a summary worksheet linked to other worksheets in the same workbook. I am using the following to count events actioned by a person with the surname of "knight". =COUNTIF(sa_advisor_LAST_NAME,"knight")
It works well as it is but I have a problem:
1. how do I get it to check for last name in column b and first name in column a (needed if we have the same surname duplication).? I can't work out how to have 2 conditions in a countif but cant find another function that would work instead.

Elkar

For multiple conditions, use SUMPRODUCT instead.

=SUMPRODUCT(--(sa_advisor_LAST_NAME="Knight"),--(sa_advisor_FIRST_NAME="Michael"))

HTH
Elkar

carrach

Thank you Elkar, but that doesn't work I'm afraid. I've not used SUMPRODUCT before, can you explain the function and what the hyphens are for.
regards

Elkar

SUMPRODUCT takes multiple ranges (of the same size) and multiplies the 1st value in each range, then the second value in each range, third etc... Then, it adds each of those results together to get a total. So, for example:

LAST_NAME
Knight
Knight

FIRST_NAME
Michael
Bill

The first values (Knight, Michael). Since LAST_NAME="Knight" it returns 1 for True. And since FIRST_NAME="Michael", it returns another 1 for True. 1x1=1.

The Second values (Knight, Bill). Since LAST_NAME="Knight" it returns 1 for True. And since FIRST_NAME<>"Michael" it returns 0 for FALSE. 1x0=0.

Then it adds the results. 1+0=1. So, there is one set of data that satisfies both conditions.

The purpose of the hyphens is basically the same as multiplying by -1 twice. This is because SUMPRODUCT only works with numbers. So the TRUE/FALSE results are converted to 1 or 0.

It should work based on the example you provided. I tested it on an Excel 2003 workbook and it works for me. Make sure that the named ranges are spelled correctly and that the ranges are the same size.

HTH
Elkar

carrach

Hi again
thank you for the reply, but I still can't get it to work & dont know enough to know why.
I've doctored the sheet (removed the sensitive information and unecessary sheets) and attached. As you can see I used the formulae for Hilary Green. The ones I also need to use it for are highlighted in purple.
I am grateful for your help.
regards

Attached Files

query.zip (28.2 KB, 2 views)

Elkar

Ok, I see the problem. Your named ranges refer to entire columns (D:D and E:E). SUMPRODUCT can only be used with limited range values, not entire columns. If you adjust your named ranges to refer to cells D3:D999 instead of just D:D it should work. Repeat for each range. You can use whatever number you want for the ending value up to 65535.

HTH
Elkar

carrach

Wow, today is a wonderful day!! Thank you so so so much. I have been fighting with this for so long but hadn't realised that it was something so simple. It works!!!
Thank you many times over.
regards