SOLVED

Table Swapping

Copper Contributor

To start, this is a terrible process I have inherited. I am in discussions with the team I'm working with to change it but until then I still need to present this data to my executive team.

 

I am having some difficulties with a few tables. I use them to compare old deployment data with new data. Table 1 is static containing the old data. Table 2 gets data from a table on a wiki copied into it. Table 3 then has logic that checks to see if Table 2 has any new data and if so displays the current quarter. Once this comparison is complete, I copy the data in Table 3 into Table 1 using the "Values (V)" option.

 

This is where the problem exists because Table 1 is totaling all of the deployments but when I paste in the data from Table 3 it totals all cells in the column. COUNTA() counts the blank cells as well. If I manually select all the blank cells and hit delete the subtotal only counts cells with a quarter displayed. There are no spaces in the cells and if I use COUNTBLANK() the number remains the same whether the cell has "data" pasted into it or I have deleted "it."

 

I am at my wits' end with this. I'm trying to automate a terrible process as much as I can with this worksheet and this makes me want to tear my hair out. I have attached a sample of the worksheet. Any insights here would be greatly appreciated.

7 Replies

Would you be able upload  a sample worksheet with some example data points, problem areas (cell reference) and expected results ? 

That will help you and contributors here to get a quicker and better solution for you. 

 

 

@Kodipady I think I did that correctly. Hopefully someone can figure this out. Thank you for the suggestion!

@SteeltownRiot 

The issue with table 3 formula is that it results in empty text (""). COUNTA counts empty text. 

 

To avoid this issue, you can use following formula in subtotal field Solution3. 

=SUM(--([Solution3]<>""))

Similiarly for other solution columns . Hope this helps !!

 

ps: If you are using subtotal  to sum up when filters are applied on table 1 or 3.  If yes, the behavious of SUM will be different from Subtotal. Subtotal ignores the rows filtered out and SUM does not.     

 

@Kodipady thank you! I tried that, but I'm getting #VALUE! now.

 

Annotation 2019-08-21 131608.pngAnnotation 2019-08-21 131846.png

best response confirmed by SteeltownRiot (Copper Contributor)
Solution

@SteeltownRiot 

 

Could you please check any of the following ? 

=SUM(--(TABLE1[Solution3]<>""))

=SUMPRODUCT(--(LEN(TABLE1[Solution3]) >0)) 

 

If any of these do not work , could you please upload the excel file with the formula ?  the formula seems to work for me , just wanted to ensure that this is not an office version issue. 

@Kodipady The second one did it. Thank you so much! I've attached the workbook again.

@SteeltownRiot 

good to hear that it worked and glad i could help !! 

1 best response

Accepted Solutions
best response confirmed by SteeltownRiot (Copper Contributor)
Solution

@SteeltownRiot 

 

Could you please check any of the following ? 

=SUM(--(TABLE1[Solution3]<>""))

=SUMPRODUCT(--(LEN(TABLE1[Solution3]) >0)) 

 

If any of these do not work , could you please upload the excel file with the formula ?  the formula seems to work for me , just wanted to ensure that this is not an office version issue. 

View solution in original post