Totals changing despite sumif

  • Hi everyone,


    i have a spreadsheet that contains simple sumif formulas and then has a summary tab that contains the same to create a consolidated position. The trouble is, when I filter on the input tab, this changes the totals tab! I have never know this for sumifs. I am a fairly advanced user of Excel (able to write and interpret VBA level) but I am perplexed by this because it has never happened before. Has anyone else experienced this?


    thank you


    Kieron

  • Re: Totals changing despite sumif


    Hello,


    When it comes to filtered rows, to be on the safe side, take a look at the SubTotal() function (with type 109 ...)


    See explanation in link : https://www.techonthenet.com/excel/formulas/subtotal.php


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: Totals changing despite sumif


    Hi Callum,


    Thank you for getting back to me. The problem is that i don't want the totals to change on the totals tab. This should be a total for the whole company despite what filters are on in the input tab.


    thank you


    Kieron

  • Re: Totals changing despite sumif


    Hello,


    If you want to include the hidden values, use the type 9 ... and if you need to exclude them, use the type 109 ...


    If this does not help ... just attach a sample workbook with your next message ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)