Aug 19 2019 09:07 AM - edited Aug 20 2019 11:30 AM
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.
Aug 19 2019 08:56 PM
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.
Aug 20 2019 11:23 AM
@Kodipady I think I did that correctly. Hopefully someone can figure this out. Thank you for the suggestion!
Aug 21 2019 01:16 AM - edited Aug 21 2019 01:49 AM
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.
Aug 21 2019 11:20 AM - edited Aug 21 2019 11:33 AM
Aug 21 2019 11:36 PM
Solution
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.
Aug 22 2019 07:20 AM
@Kodipady The second one did it. Thank you so much! I've attached the workbook again.
Aug 22 2019 07:37 AM
good to hear that it worked and glad i could help !!
Aug 21 2019 11:36 PM
Solution
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.