vba Sumifs -formula dynamic

[Malleshg24]

Hi Team,

I have attached my Macro with Input File,
Macro gives correct result. but the code is not dynamic.

Need your help in creating Single custom Sumifs Formula , (Criteria ,sumranges and header, parameter should pass from Mapping sheet.)

User will update Mapping sheet, According to that Macro will find header and sum the range.

I have taken few *Cost Center Description) as an example, list is lengthy, header to sum max there will be 4 headers.


Please assist thanks in advance !


Regards,
mg

 

[Marc L]

but the code is not dynamic.

Hi,​

​

what that means ?​

​

 

[Malleshg24]

Hi Marc,

Thanks for looking in my thread, I want something like below, or any other option I am ok with it.

I want outer loop in on Cost Center Description which is in Mapping sheet,
, inner loop for finding headers as per mapping sheet , and summing those headers.

User can change add extra headers or reduce headers, macro should sum it.

if no header found as per mapping in input file, sum of that header should be 0.

One header sum 1250000, another header missing - missing header value should be 0. Macro should not throw error.

My Current macro works, but in future if user want to add extra header , I have to add extra sumifs.



Regards,
MG

 

[Marc L]

If the source data is converted to an Excel table so formulas can use directly the headers names rather than their columns addresses​

or using a named range for the source data …​

​

Microsoft Excel Tables - What are they, how to make a table & 13 tips

Table is a collection of related rows & columns. Excel Tables allow us to structure and connect data to work better. Create a table by pressing CTRL+T. Use tables to write structural references, create graphs or pivot tables easily.

chandoo.org