How to use CONCATENATE and COUNTIF together

Prerequisite 

Know how to use "CONCATENATE" and "COUNTIF" individually. Please refer to Microsoft help for 

Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.


Simple Example 

Lets consider a simple scenario,

Data given (raw data) : quantity produced by different machines on different days

Data needed : Number of times (frequency) each machine used on each date.

The steps (How to do)

Stap 1: Create a new column to join date and machine using "Concatenate".

  

Note: I am using  "-" to make sure no duplicates.

the formula in column (refer to the image) is  =CONCATENATE(N3,"-",O3).

Make sure the formula is dragged to all the way to the bottom of the table.

Step 2: create the matrix 

Manufacturing date in the column and machines in the raw (it can be done vise-verse also)

Step 3: formula to calculate the total quantity produced by each machines on different days.

Use" COUNTIF" to do the calculation. Lets start in cell B3.


range    Required. Column M (where the concatenate values are)
criteria    Required. CONCATENATE($A3,"-",B$2)

Let's focus on CONCATENATE($A3,"-",B$2). This formula is the same as in column M. The main difference in freezing column and raw values. $A3 is column freeze and  B$2 is raw freeze. The freezing allow us to drag the formula.

Step 4: Drag the formula to fill up the entire table

DONE

Step 5: Check and Balance

Always good to do a check on the accuracy.

In this case SUM(B3:J12) sould be equal to COUNT(P:P)