Google Search

Custom Search

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


”how


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


”how


The steps (How to do)

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


”how
  

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)

”how



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


”how



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)






1 comment:

  1. Nice tutorial! I found another helpful tutorial on www.microsofttut.com at http://www.microsofttut.com/2016/11/business-applications-of-excel-sumif-countif-and-concatenate-functions.html. It may also help you!

    ReplyDelete

 

blogger templates | Make Money Online

Google Analytics Alternative ExpiresDefault access plus 1 year