# 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

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

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:

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.