Sumproduct with Named range

[Thomas Kuriakose]

Respected Sirs,

I created four named ranges as per below to find the sum of quantity and material cost with respect to source and product to be calculated in Find tab C2:D5 - Since the number of columns change with each data workbook.


We need to get a sum of quantity and material values based on the source and product entered in columns C3 to number of columns with products and C4 to number of columns for various sources in tab Data. There are column totals, budget and difference values for each data set.

I used sum product, but it is giving me #N/A. I have entered the required results in F2 to G5 of the Find tab

Kindly find attached the workbook for your reference.

Thank you very much,

with regards,
thomas

 

[Hui]

The issue is that each named Range uses it's own Counta() to work out the length and they are all different lengths
To check goto the Name Manager
Select a Name
and click in the Refers To: box

I would use COUNTA(Data!$3:$3,0) for the lengths of all the names

eg:
Material: =OFFSET(Data!$B$7,,1,,COUNTA(Data!$3:$3,0)-2)
Product: =OFFSET(Data!$B$3,,1,,COUNTA(Data!$3:$3,0)-2)
Quantity: =OFFSET(Data!$B$5,,1,,COUNTA(Data!$3:$3,0)-2)
Source: =OFFSET(Data!$B$4,,1,,COUNTA(Data!$3:$3,0)-2)

change them and immediately the answer appear

 

[Hui]

Actually what I would do would be as follows:
Product: =OFFSET(Data!$C$3,,,,COUNTA(Data!$3:$3)-1)
Source: =OFFSET(Product,1,)
Quantity: =OFFSET(Product,2,)
Material: =OFFSET(Product,4,)

See attached file:

 

[bosco_yip]

Hi,

Another option in without using the dynamic range.

1] Try to use SUMIFS function, it will auto adjust to used range as same as the dynamic range and faster than the SUMPRODUCT function.

2] Please refer to the attached file

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the correction and solution provided.

Kindly explain why we need to keep only one reference $3:$3 for all the ranges.

Much appreciated,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Bosco Sir,

Thank you very much for this insight with SUMIFS.

Thank you very much once again,

with regards,
thomas

 

[Hui]

Sumproduct and Sumifs require that all Ranges are the same Length
By doing each line separately you didn't allow for the data at the end of the lines in Columns T & U
see diagram below:

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this clarification.

Thank you very much once again,

with regards,
thomas