Tina has attended:
Excel Intermediate course
Excel Advanced course
SUMIFS
Hello
Thanks to your training, I’ve finally learned how to calculate complex data using the SUMIFS formula. For example: =SUMIFS(G13:G307, H13:H307, C320).
However, in one of my calculations, some amounts are missing. My question is: How can I find out what amount is missing and why the formula isn't capturing it?
Thank you
Tina
RE: SUMIFS
Hello Tina,
It is frustrating to have missing amounts in your SUMIFS calculation. Here are a few steps you can take to identify the issue:
Check the Sum Range and the Criteria Range: ensure that both ranges reference exactly the same rows. Any mismatch can cause the formula to miss some amounts.
Verify the Criteria: Make sure the criteria you're using in the SUMIFS formula are correct and match the data exactly. Even a small difference, like an extra space or different case, can cause the formula to miss amounts.
Data Format: Ensure that the data in both the criteria range and the sum range are in the same format. Sometimes, numbers stored as text can cause issues.
Hidden Rows/Columns: Check if there are any hidden rows or columns in your data range that might be affecting the calculation.
Error Checking: Use Excel's error checking feature to see if there are any errors in the cells that might be causing the issue.
If you can share more details about your formula and data, we can provide more specific guidance.
Kind regards
Marius Barnard
STL
RE: SUMIFS
Dear Marius
Thank you for the suggestions above. However, I have tried all of them and still could not find the difference. Additionally, I was wondering if there’s another method to identify where the error is coming from, rather than checking the data one by one.
I would like to send you an Excel file with an example in it. Maybe you can spot the difference and advise me on how to find it.
Could you please advise how I can send you the excel file as I cannot see any attachment option here
Thank you in advance
Tina
RE: SUMIFS
Hi Tina,
Please send your sample data to:
info@stl-training.co.uk
Also, please include in the spreadsheet a description of the issue you are having and perhaps recolour some of the 'problem' records.
Many thanks
Marius Barnard
STL
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
