Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Excel

Copper Contributor

PXL_20230816_132534428.jpg

Hi everyone...

 

I have three columns with data init . The headers are Month and Total sales, total profits.  The data is filled till 100th row. 

 

Now I need to prepare a table with totals sales and month wise. I was using SUMIFS formula to create the table and it worked and I got the a number for each month  but the total sum for 12 months  with the formula is not matching with the total sum of (1-100 rows ) number . I tried finding answer on internet but I didn't no understand it. Can someone explain it  please . Thanks.  

4 Replies

@pavankumarmechineni 

That's hard to say what's wrong without seeing the file, perhaps you may share it removing sensitive information.

 

Another option is PivotTable.

Hi Sergei...I have uploaded the pic with highlighting the totals of pivot table and SUMIFS total sum . Please refer to the formula in the ribbon and let me know my mistake. Thanks.
best response confirmed by pavankumarmechineni (Copper Contributor)
Solution

@pavankumarmechineni Begin by changing the month names to Jan, Feb, Mar in the table in column G. Just like you have them in the data.

 

Then change the formula in H5 to reference G5 rather than A31. Even better if you enter the reference by clicking on cell G5. That will enter a structured reference @[Month] and all should work just fine.

 

When referring to A31 like you did, the next rows will refer to A32, A33 etc. and these cells do NOT contain Feb, Mar etc. Your 'February balance' adds up numbers for Mar and March does it for Nov etc.

Hi....it worked as you instructed . Thanks for the inputs.
1 best response

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

@pavankumarmechineni Begin by changing the month names to Jan, Feb, Mar in the table in column G. Just like you have them in the data.

 

Then change the formula in H5 to reference G5 rather than A31. Even better if you enter the reference by clicking on cell G5. That will enter a structured reference @[Month] and all should work just fine.

 

When referring to A31 like you did, the next rows will refer to A32, A33 etc. and these cells do NOT contain Feb, Mar etc. Your 'February balance' adds up numbers for Mar and March does it for Nov etc.

View solution in original post