0

I have an excel spreadsheet that counts candidates that have gained work in a certain country, but each job (Company) is liked to another sheet with a sector attached.

I need to be able to count if the applicant lives in certain Country and the sector the Company sits in and each table is collected Monthly. I don't know if this can be achieved with a COUNTIF function or if I need to look down the route of MATCH or VLOOKUP

My Database:

****************************************************
*   Name  *  Country  *   Company   *  Start Date  *
****************************************************
*  John   *  Brazil   *     Gap     *  21/03/2016  *
* Jessica *    USA    * Burger King *  21/02/2016  *
****************************************************

Sector Sheet:

***************************
*   Company   *  Sector   *
***************************
*     Gap     *  Retail   *
* Burger King * Catering  *
***************************

Data collection table:

********************************************
*   FEB 2016   *  Brazil  *   USA   *  UK  *
********************************************
*    Retail:   *     0    *    0    *  0   *
********************************************
*  Catering:   *     0    *    1    *  0   *
********************************************

Any assistance on where to look would be amazing and apologies if there is a simple function to find this data

Improve this question
1
  • 1
    Do you know COUNTIFS??? Commented Mar 4, 2016 at 15:44

1 Answer 1

Reset to default
2

SUMPRODUCT() would be a better fit:

=SUMPRODUCT(($B$2:$B$3=K$1)*(MONTH($D$2:$D$3)=MONTH($J$1))*(YEAR($D$2:$D$3)=YEAR($J$1))*(COUNTIFS($F$2:$F$3,$C$2:$C$3,$G$2:$G$3,$J2)))

enter image description here

Once put in the upper left cell of the matrix and the correct references are used, then drag across and down. Pay attention to the $ notation that makes the various absolute.

I had to remove the : on the third table data. The data has to match perfectly or it will not count.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Amazing! This is perfect except if one of the "Country" fields is blank it causes the #N/A error and sometimes I may not enter the Country right away. Really sorry to be a pain.
Then wrap the whole thin in an IFERROR(),=IFERROR(SUMPRODUCT(($B$2:$B$3=K$1)*(MONTH($D$2:$D$3)=MONTH($J$1))*(YEAR($D$2:$D$3)=YEAR($J$1))*(COUNTIFS($F$2:$F$3,$C$2:$C$3,$G$2:$G$3,$J2))),0). Remember to mark it as correct.
Hi, sorry for the late response, you are brilliant

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.