Imagine a complex data set where each department, in a corporation, spends on variety of items (example data set given below). How to get a summary of what each department spent? In our example how to find answers to the question marks? Dept 1 spent on “Maintenance”, “IT”, “Infrastructure” and did not spend on others.

First let’s look at the logic: If the sum total of all the items is zero, then the department has not spent on anything. If any of the other spending items is greater than zero, then return the value of the respective column name. Then concatenate all the returned value together for the final output.

Let’s take a look at each step of the logic in detail. First, let’s get the sum function to see if any of the department is spending at all. If total spend is “0”, then return “nil”, else “spending”. Formula, for our specific example, would be:

*=if(sum(B3:G3)=0,”nil”,”Spending”)*

Next let’s get each column name based on the value of each cells under the column. Formula, for our specific example, would be:

*=if(B3>0,$B$2&” “,”‘)*

The reason we add “&” is to get a space for the end result when we concatenate. The result of the above formula will be:

Let’s continue the same formula for all the columns (items where spend is happening. List of formulas, for our specific example, would be:

*=IF(C3>0,$C$2&” “,””) in column L*

*=IF(D3>0,$D$2&” “,””) in column M*

*=IF(E3>0,$E$2&” “,””) in column N*

*=IF(F3>0,$F$2&” “,””) in column O*

*=IF(G3>0,$G$2&” “,””) in column P*

Now, let’s concatenate all the results from each if condition. Formula, for our specific example, would be:

*=concatenate(K3,L3,M3,N3,O3,P3)*

You must be wondering we have done too many formulas and used too many columns to get the output. Don’t worry – next we will be converting all these formula into one. First, let’s replace the K3, L3 etc in the concatenate formula with the respective if condition in K,L,M,N,O & P columns. The formula, for our example after adding all the IF conditions, would be:

*=CONCATENATE(IF(B3>0,$B$2&” “,””),IF(C3>0,$C$2&” “,””),IF(D3>0,$D$2&” “,””),IF(E3>0,$E$2&” “,””),IF(F3>0,$F$2&” “,””),IF(G3>0,$G$2&” “,””))*

After adding the above formula, feel free to delete K,L,M,N,O,P columns.

Now let’s get back for the first ever formula under “Final Output”. Replace “Spending” with the concatenate function. The final formula would look like this:

**=IF(SUM(B3:G3)=0,”nil”,CONCATENATE(IF(B3>0,$B$2&” “,””),IF(C3>0,$C$2&” “,””),IF(D3>0,$D$2&” “,””),IF(E3>0,$E$2&” “,””),IF(F3>0,$F$2&” “,””),IF(G3>0,$G$2&” “,””)))**With the above final formula, answers to all the question marks are achieved.

Sorry for the long post for a single formula – but I wanted to break down each element of the formula. I hope you will find this tutorial helpful. Please provide us with your valuable feedback to improve our content. Thanks for reading.