My Percentage of total sales is not 100 - Why?

%3CLINGO-SUB%20id%3D%22lingo-sub-2804125%22%20slang%3D%22en-US%22%3EMy%20Percentage%20of%20total%20sales%20is%20not%20100%20-%20Why%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804125%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%2020%20categories%20of%20Sales%20Revenue%20in%20Column%20A%2C%20the%20total%20revenue%20for%20each%20category%20is%20in%20column%20B%2C%20the%20%25%20of%20a%20category%2C%20as%20it%20relates%20to%20the%20total%20categories%20is%20in%20column%20C%20but%20the%20Total%20Percentage%20is%20over%20100%25%20when%20I%20total%20all%20the%20percentages%2C%20what%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2804125%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2811370%22%20slang%3D%22en-US%22%3ERe%3A%20My%20Percentage%20of%20total%20sales%20is%20not%20100%20-%20Why%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2811370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172646%22%20target%3D%22_blank%22%3E%40Nathalie135%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22487%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22272%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2293%22%3E%26nbsp%3BAmount%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2265%22%3E%25%20of%20Sales%3C%2FTD%3E%3CTD%20width%3D%2257%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBackup%20%26amp%3B%20Recover-Desktop%20Suppor%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2070%2C808.50%3C%2FTD%3E%3CTD%3E0.022%3C%2FTD%3E%3CTD%3E2.23%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBarracuda%20Back%20Up%20-%20Cloud%20to%20Cloud%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%2C220.00%3C%2FTD%3E%3CTD%3E0.0035%3C%2FTD%3E%3CTD%3E0.35%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBarracuda%20ESS%20-%20Security%20Edition%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2051%2C508.57%3C%2FTD%3E%3CTD%3E0.0162%3C%2FTD%3E%3CTD%3E1.62%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBarracuda%20Sentinel%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2020%2C093.52%3C%2FTD%3E%3CTD%3E0.0063%3C%2FTD%3E%3CTD%3E0.63%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECarrier%20Management%20%2F%20NOC%20Support%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20239%2C618.99%3C%2FTD%3E%3CTD%3E0.0754%3C%2FTD%3E%3CTD%3E7.54%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECust%20Support%20Srvs%2FProvisioning%2FvCIO%20Svcs%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2098%2C514.00%3C%2FTD%3E%3CTD%3E0.031%3C%2FTD%3E%3CTD%3E3.10%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EField%20Services%20Support-Single%3C%2FTD%3E%3CTD%3E%26nbsp%3B1%2C447%2C941.00%3C%2FTD%3E%3CTD%3E0.4556%3C%2FTD%3E%3CTD%3E45.56%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFTE%20Tech%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20159%2C227.50%3C%2FTD%3E%3CTD%3E0.0501%3C%2FTD%3E%3CTD%3E5.01%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHardware%20Lease%20Revenue%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%2C114.22%3C%2FTD%3E%3CTD%3E0.0035%3C%2FTD%3E%3CTD%3E0.35%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHelp%20Desk%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B1%2C604%2C649.85%3C%2FTD%3E%3CTD%3E0.5049%3C%2FTD%3E%3CTD%3E50.49%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EIH-New%20User_Laptop%20Setup%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20230%2C523.19%3C%2FTD%3E%3CTD%3E0.0725%3C%2FTD%3E%3CTD%3E7.25%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EIH-Remote%20Work%20Support%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2060%2C172.85%3C%2FTD%3E%3CTD%3E0.0189%3C%2FTD%3E%3CTD%3E1.89%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMRR%20-%20Data%20Services%2FTicket%20Overage%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2040%2C057.00%3C%2FTD%3E%3CTD%3E0.0126%3C%2FTD%3E%3CTD%3E1.26%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENetwork%20%26amp%3B%20Security%20Hardware%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20985%2C825.90%3C%2FTD%3E%3CTD%3E0.3102%3C%2FTD%3E%3CTD%3E31.02%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENetwork%20Monitoring%20Service-Devices%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20153%2C696.60%3C%2FTD%3E%3CTD%3E0.0484%3C%2FTD%3E%3CTD%3E4.84%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENRR%20-%20Carrier%20Management%20%2F%20NOC%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%2C245.00%3C%2FTD%3E%3CTD%3E0.0035%3C%2FTD%3E%3CTD%3E0.35%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENRR%20-%20ESS%20Email%20Security%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2028%2C865.00%3C%2FTD%3E%3CTD%3E0.0091%3C%2FTD%3E%3CTD%3E0.91%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENRR%20-%20Help%20Desk%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2046%2C500.00%3C%2FTD%3E%3CTD%3E0.0146%3C%2FTD%3E%3CTD%3E1.46%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENRR%20-%20Migration%20Implementation%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2098%2C595.00%3C%2FTD%3E%3CTD%3E0.031%3C%2FTD%3E%3CTD%3E3.10%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENRR%20-%20Monitoring%20Services%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015%2C000.00%3C%2FTD%3E%3CTD%3E0.0047%3C%2FTD%3E%3CTD%3E0.47%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERemote%20Server%20Monitoring%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20102%2C455.03%3C%2FTD%3E%3CTD%3E0.0322%3C%2FTD%3E%3CTD%3E3.22%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERemote%20Work%2FSharepoint%20Migration%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2084%2C026.55%3C%2FTD%3E%3CTD%3E0.0264%3C%2FTD%3E%3CTD%3E2.64%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EShipping%20%26amp%3B%20Delivery%20Fee%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049%2C970.63%3C%2FTD%3E%3CTD%3E0.0157%3C%2FTD%3E%3CTD%3E1.57%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESoftware%2FLicenses%3C%2FTD%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20491%2C422.97%3C%2FTD%3E%3CTD%3E0.1546%3C%2FTD%3E%3CTD%3E15.46%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETOTAL%3C%2FTD%3E%3CTD%3E%26nbsp%3B3%2C178%2C015.20%3C%2FTD%3E%3CTD%3E1.9235%3C%2FTD%3E%3CTD%3E100.00%25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20formula%20I%20used%20is%3A%26nbsp%3B%3DC2%2F%24C%2426%3C%2FP%3E%3CP%3Eall%20the%20way%20down%20including%20the%20Total%20to%20get%20the%20100%25%3C%2FP%3E%3CP%3Edon't%20sum%20the%20%25%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2811405%22%20slang%3D%22en-US%22%3ERe%3A%20My%20Percentage%20of%20total%20sales%20is%20not%20100%20-%20Why%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2811405%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%2C.%20I%20really%20appreciate%20your%20help%20and%20response.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have 20 categories of Sales Revenue in Column A, the total revenue for each category is in column B, the % of a category, as it relates to the total categories is in column C but the Total Percentage is over 100% when I total all the percentages, what am I missing?

  Amount% of Sales 
Backup & Recover-Desktop Suppor 70,808.502.2% 
Barracuda Back Up - Cloud to Cloud 11,220.000.35% 
Barracuda ESS - Security Edition $51,508.571.62% 
Barracuda Sentinel 20,093.520.63% 
Carrier Management / NOC Support Services $239,618.997.54% 
Cust Support Srvs/Provisioning/vCIO Svcs $98,514.003.10% 
Field Services Support-Single $1,447,941.0045.56% 
FTE Tech 159,227.505.01% 
Hardware Lease Revenue 11,114.220.35% 
Help Desk Services 1,604,649.8550.49% 
IH-New User_Laptop Setup $230,523.197.25% 
IH-Remote Work Support Services 60,172.851.89% 
MRR - Data Services/Ticket Overage $40,057.001.26% 
Network & Security Hardware $985,825.9031.02% 
Network Monitoring Service-Devices 153,696.604.84% 
NRR - Carrier Management / NOC Services 11,245.000.35% 
NRR - ESS Email Security Services 28,865.000.91% 
NRR - Help Desk 46,500.001.46% 
NRR - Migration Implementation 98,595.003.10% 
NRR - Monitoring Services 15,000.000.47% 
Remote Server Monitoring 102,455.033.22% 
Remote Work/Sharepoint Migration $84,026.552.64% 
Shipping & Delivery Fee 49,970.631.57% 
Software/Licenses 491,422.9715.46% 
TOTAL $3,178,015.20192.35% 
6 Replies

@Nathalie135 

  Amount % of Sales 
Backup & Recover-Desktop Suppor       70,808.500.0222.23%
Barracuda Back Up - Cloud to Cloud       11,220.000.00350.35%
Barracuda ESS - Security Edition       51,508.570.01621.62%
Barracuda Sentinel       20,093.520.00630.63%
Carrier Management / NOC Support Services     239,618.990.07547.54%
Cust Support Srvs/Provisioning/vCIO Svcs       98,514.000.0313.10%
Field Services Support-Single 1,447,941.000.455645.56%
FTE Tech     159,227.500.05015.01%
Hardware Lease Revenue       11,114.220.00350.35%
Help Desk Services 1,604,649.850.504950.49%
IH-New User_Laptop Setup     230,523.190.07257.25%
IH-Remote Work Support Services       60,172.850.01891.89%
MRR - Data Services/Ticket Overage       40,057.000.01261.26%
Network & Security Hardware     985,825.900.310231.02%
Network Monitoring Service-Devices     153,696.600.04844.84%
NRR - Carrier Management / NOC Services       11,245.000.00350.35%
NRR - ESS Email Security Services       28,865.000.00910.91%
NRR - Help Desk       46,500.000.01461.46%
NRR - Migration Implementation       98,595.000.0313.10%
NRR - Monitoring Services       15,000.000.00470.47%
Remote Server Monitoring     102,455.030.03223.22%
Remote Work/Sharepoint Migration       84,026.550.02642.64%
Shipping & Delivery Fee       49,970.630.01571.57%
Software/Licenses     491,422.970.154615.46%
TOTAL 3,178,015.201.9235100.00%

The formula I used is: =C2/$C$26

all the way down including the Total to get the 100%

don't sum the % column

 

cheers

Thank you so much,. I really appreciate your help and response.

@Nathalie135 Not sure about your arithmetic. When I add-up the sales amounts, I come to just over 6.1 million. I.e. not 3.2 million. That's a factor 1.9235 higher. That's why you get the wrong overall percentage. And, of course, you may add up individual percentages, provided you calculated them on the correct basis.

Screenshot 2021-10-05 at 06.50.42.png

Thank you, I thought it looked wrong, I'm not sure what the cell formatting was doing but it was definitely doing something weird. These numbers are what I thought they should have been. When I cleared the formatting of the cells and summed it again I finally got the correct numbers (your numbers). Thanks again

@Nathalie135.... FYI, "cell formatting" cannot explain the difference between 3.2 million versus 6.1 million.

 

A more likely explanation is:  the summation cell was not recalculated.  And beware: that might still be an ongoing issue, remedied on a one-time basis because the changes that you made forced a recalculation.

 

In the future, attach a example Excel file that demonstrates the problem so that we can see your formulas, the original data, and the settings of workbook-specific options.

Will do, thank you, I will keep an eye out for that and send a bit more detail next time. Thanks again