SUMIF TableName Contians...

  • Hi there


    I have a workbook with 9 different sheets.
    The 9 Sheets all have a different Table (different Names)


    3 where the 1st letters are "KHC", 3 are "THC", and 2 are "Admin"


    I need to sum certain columns where the Table is "KHC" - therefore add all 3 tables, and so on.


    My Formula is:

    Code
    1. =IF($D$1="KHC",SUM(SUMPRODUCT((KHCCheck[Date]>=$G$1)*(KHCCheck[Date]<=$I$1)*(KHCCheck[Category]=C6)*KHCCheck[Total]),SUMPRODUCT((KHCCon[Date]>=$G$1)*(KHCCon[Date]<=$I$1)*(KHCCon[Category]=C6)*KHCCon[Total]),SUMPRODUCT((KHCSav[Date]>=$G$1)*(KHCSav[Date]<=$I$1)*(KHCSav[Category]=C6)*KHCSav[Total])),IF($D$1="THC",SUM(SUMPRODUCT((THCCheck[Date]>=$G$1)*(THCCheck[Date]<=$I$1)*(THCCheck[Category]=C6)*THCCheck[Total]),SUMPRODUCT((THCCon[Date]>=$G$1)*(THCCon[Date]<=$I$1)*(THCCon[Category]=C6)*THCCon[Total]),SUMPRODUCT((THCSav[Date]>=$G$1)*(THCSav[Date]<=$I$1)*(THCSav[Category]=C6)*THCSav[Total])),IF($D$1="Admin",SUM(SUMPRODUCT((AdCheck[Date]>=$G$1)*(AdCheck[Date]<=$I$1)*(AdCheck[Category]=C6)*AdCheck[Total]),SUMPRODUCT((AdCon[Date]>=$G$1)*(AdCon[Date]<=$I$1)*(AdCon[Category]=C6)*AdCon[Total]),SUMPRODUCT((AdSav[Date]>=$G$1)*(AdSav[Date]<=$I$1)*(AdSav[Category]=C6)*AdSav[Total])))))


    Is there an easier way to write the code so if D1 says KHC include all tables CONTAINING "KHC". (Or Admin, or THC)

  • Re: SUMIF TableName Contians...


    Create a little table in the master sheet listing the table names in vertical columns.


    e.g


    KHCCheck THCCheck AdCheck
    KHCCon THCCon AdCon
    KHCSav THCSav AdSav


    Create a Named Range for each of these three lists, naming them only by the first characters you intend to put in D1 (e,.g KHC, THC, Ad)


    Then use this formula:


    [COLOR="#0000FF"]=SUMPRODUCT(SUMIFS(INDIRECT(INDEX(INDIRECT($D$1),0)&"[Total]"),INDIRECT(INDEX(INDIRECT($D$1),0)&"[Date]"),">="&$G$1,INDIRECT(INDEX(INDIRECT($D$1),0)&"[Date]"),"<="&$I$1,INDIRECT(INDEX(INDIRECT($D$1),0)&"[Category]"),C6))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: SUMIF TableName Contians...


    Thanks NBVC


    Just one final question, i need to do a consolidated report too, currently using:


    =SUM(SUMPRODUCT((KHCCheck[Category]=Consolidated!C6)*(KHCCheck[Date]>=Consolidated!$G$1)*(KHCCheck[Date]<=$I$1)*KHCCheck[Total]),SUMPRODUCT((KHCCon[Category]=C6)*(KHCCon[Date]>=$G$1)*(KHCCon[Date]<=$I$1)*KHCCon[Total]),SUMPRODUCT((KHCSav[Category]=C6)*(KHCSav[Date]>=$G$1)*(KHCSav[Date]<=$I$1)*KHCSav[Total]),SUMPRODUCT((THCCheck[Category]=C6)*(THCCheck[Date]>=$G$1)*(THCCheck[Date]<=$I$1)*THCCheck[Total]),SUMPRODUCT((THCCon[Category]=C6)*(THCCon[Date]>=$G$1)*(THCCon[Date]<=$I$1)*THCCon[Total]),SUMPRODUCT((THCSav[Category]=C6)*(THCSav[Date]>=$G$1)*(THCSav[Date]<=$I$1)*THCSav[Total]),SUMPRODUCT((AdCheck[Category]=C6)*(AdCheck[Date]>=$G$1)*(AdCheck[Date]<=$I$1)*AdCheck[Total]),SUMPRODUCT((AdCon[Category]=C6)*(AdCon[Date]>=$G$1)*(AdCon[Date]<=$I$1)*AdCon[Total]),SUMPRODUCT((AdSav[Category]=C6)*(AdSav[Date]>=$G$1)*(AdSav[Date]<=$I$1)*AdSav[Total]))


    Any clues so as to sum from ALL Tables?

  • Re: SUMIF TableName Contians...


    Maybe, instead of placing the table names in 3 separate columns as I mentioned in my earlier post, you would list them all in one column. You can name the 3 separate sections, then you can give the whole list a single name like "All"... then you can use this formula for all tables.


    [COLOR="#0000FF"]=SUMPRODUCT(SUMIFS(INDIRECT(INDEX(INDIRECT("All"),0)&"[Total]"),INDIRECT(INDEX(INDIRECT("All"),0)&"[Date]"),">="&$G$1,INDIRECT(INDEX(INDIRECT("All"),0)&"[Date]"),"<="&$I$1,INDIRECT(INDEX(INDIRECT("All"),0)&"[Category]"),C6))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: SUMIF TableName Contians...


    I think i tried that, but will have another attempt, incase I missed something, and copy your formula to my sheet.


    Thanks for your help certainly the 1st formula is tidier, and allows for easy additional tables etc.

  • Re: SUMIF TableName Contians...


    NBVC


    Thankyou, just to report, that your Consolidated worked perfectly well. Not quite sure what I did the first time (with my attempt), but it works now.


    Thankyou.