Excel Sheet Specific Formula not working

Copper Contributor

I have specific Excel Spreadsheet which needs to have a correct results of the formula. The formulas are working however the data within the Excel Sheet are not corresponding. Any helpful answers will be catered and appreciated. 

14 Replies
I'm guessing calculation is set to manual. Check the "Calculation options" button on the formulas tab (on the right).

@Jan Karel Pieterse 

 

I just double checked and the calculations options in excelsheet is set to Automatic.

Without seeing your Excel file, this is really hard to trouble-shoot. Can you perhaps create and upload an anonymized version of the file that shows the same problem?
will it be possible to access my laptop remotely and take a look the excelsheet?
I'm sorry, I do not offer free support like that. Looking at an anonymized copy you upload here (or share a link to download) is what I can offer.

@Jan Karel Pieterse I did update and there are 2 spreadsheet.

the first one is the worksheet where the data is collected

the statistic where the data is represent in an analytical or table data.

I am more concern the 1st table specially Column D (current) where each row has its own data after applying the formula. I am thinking its more about the formula for the subtotal which display the wrong data then realized the calculation of the data in the whole table need to be verify or look at it.

 

I just upload the file as per requested.

Any guidance or advice will be greatly appreciated it.

So which cell(s) show wrong results and what is the expected outcome of those cells? For example, if it is D7 then what number are you expecting to see in D7?
D7 will be around 167 as result
D6 could be around 15 as result as well.
If D7 is supposed to count all the TRUE values in Worksheet!$AY$8:$AY$1900 then the correct formula in D7 is:

=COUNTIF(Worksheet!$AY$8:$AY$1900,TRUE)
I did apply the formula on D7 and seems to be what we were expecting.
Would you mind checking the other row results like: D2,D4,D5,D9,D10,D11 and D12 ? If the formula was applied correctly or not?
The formulas in there are not necessarily wrong, it depends on what exactly you are intending to count. Example: D4 counts all cells in column CS which contain the number 2. There is no way for me to decide whether that is the correct way to determine how many rows are about "wiring and switch". You know the business logic which determines what exactly needs to be counted.
There are some simple mistakes however. For example, many of the formula's in D do not point to the same rows as other formulas in column D. This is bad practice which you can easily avoid by converting your data on "Worksheet" to a table. See https://jkp-ads.com/Articles/Excel2007Tables.asp
cool
How can I remove ALL the files I uploaded into the techcommunity forum.
looking forward to hearing from you.
Thanks
can you check the D11 row I am getting a negative number which is not the case.
Would you checking please for me
This forum is meant to post specific questions about things you are trying to do in Excel but have not succeeded in yet. So we need:
- A clear description of your problem
- What you have tried so far
- A correct example of the expected outcome