CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Ben
Ben

Reputation: 27

How to count multiple text in multiple columns

I am Working off a large table in Excel and I want to sort the data into categories. What I’m trying to do is get Excel to count how many times a criteria in column C, D, & E occurs and returns the value. So look through C:C pick “Company”, then look through D:D for “Full Time – Temp” and then E:E for a location such as “Factory”. See link to sample table below.

Example:

G4 =COUNTIFS($C:$C,"company",$D:$D,"full time - temp",$E:$E,"home") and it returns 0

I4 =COUNTIFS($C:$C,"company bilingual",$D:$D,"bilingual - FT - perm") and it will return 3

My problem is column E

If I wanted to return in cell J4 how many “Company Bilingual” are “Bilingual - FT – Perm” and located in “Factory” I get 0.

I’ve tried using

J4 =COUNTIFS($C:$C,"company bilingual",$D:$D,"bilingual - FT – perm",$E:$E,"Factory") but it returns 0 and what I want it to return is 2, which I understand it is saying there is no Factory cell on its own, all the cells that have Factory have 3 items in them e.g Factory - Dallas. So I want to count all the factories in column E but not where the factory is actually located.

In summary what I want to do is find a function or array that will count one unique occurrence in column C, D, and E. If a cell in a column has more than one word I would like to be able to pick one word and ultimately still count all occurrences in the other columns and return a value.

In my research I have come across different suggestions but none that helps my problem.

I hope I've explained my problem, any assistance is greatly appreciated.

Screenshot of table

enter image description here

Upvotes: 0

Views: 1517

Answers (1)

skkakkar
skkakkar

Reputation: 2828

I suggest you make the criteria table and mention your criteria there as shown by me in the snapshot.Giving due credit to @Harsha Vardhan, his suggestion is correct approach as given in his comments. I have made a fully working example for clear understanding.

For partial string match I used a concatenated string in I2 ="*"&"Factory"&"*"

Criteria Table is in the Range G1:I4 and Results are in the Range J1:J4

Formula to be entered in J2 to J4 respectively are as per criteria mentioned in the table. 

=COUNTIFS($C:$C,$G$2,$D:$D,$H$2,$E:$E,$I$2)  

=COUNTIFS($C:$C,$G$3,$D:$D,$H$3)   

=COUNTIFS($C:$C,$G$4,$D:$D,$H$4,$E:$E,$I$4)

Results are as per your requirement as shown in the snapshot.

Results

EDIT DATE 23-06-2016

This has reference to OP's comments on 22nd and 23rd June 2016. There is no change in the formulas. It is required that conditions are put correctly in the criteria table. As per new criterion specified by OP, following snapshot shows that correct results are obtained. Further file count multiple text 23062016 has been uploaded for perusal. Snapshot showing results 23-06-2016

Upvotes: 1

Related Questions