CODEWITHSUNDEEP
excelexcel-formula
Joseph Gregory
Joseph Gregory

Reputation: 589

Advanced Counting in Excel

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

Upvotes: 0

Views: 63

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 2

Related Questions