Sum Array Using List of criteria and a horizontal lookup

[Greg]

Hi - I am hoping someone out there can help.

I am working with a raw data file and want to try to get my
calculations directly form this file versus importing to a database or
reformting the layout.

Essentially I have a layout as follows:

Store Product# 1 2 3 4 5 (1-5 are months)
abc 1 #
abc 2 #
abc 3 #
xyz 1 #

I want to reference the store (abc) and sum up 8 product numbers for a
specific month. The trick is that I want the month to be a variable in
the formula. that way I can switch the report out easly. The store
and product numbers are will not change.

Thanks in advance,

Greg

 

[Domenic]

Assuming that Column A contains the 'Store', Column B contains the
'Product Number', and Columns C through G contains the 'Month', try...

=SUMPRODUCT((A2:A5="ABC")*(ISNUMBER(MATCH(B2:B5,{1,2,3,4,5,6,7,8},0)))*C2
:G5*(C1:G1=5))

....where {1,2,3...8} represents the product numbers of interest, and 5
represents the month of interest.

OR

=SUMPRODUCT((A2:A5=A10)*(ISNUMBER(MATCH(B2:B5,C10:C17,0)))*C2:G5*(C1:G1=B
10))

....where A10 contains the store of interest, C10:C17 contains the
product numbers of interest, and B10 contains the month of interest.

Hope this helps!

 

[Greg]

Hi Domenic,

Thanks for the reply. I think I have followed your example correctly
but I get a return of zero when I know the product codes I am using
have values in the data file.

Is the syntax correct for the back end of the function?
*C2:G5*(C1:G1=5))

Also do you think this would work if I did a range reference for the
product numbers in a different area as opposed to listing them out
manually seperated by commas?

Here is a sample of my formula - it is a little hard to read as the
data is on a tab labled 2005RA

=SUMPRODUCT('2005RA'!A2:A3709=A3)*(ISNUMBER(MATCH('2005RA'!D23709,{361034,361073},0)))*'2005RA'!I2:T3709*('2005RA'!I1:T1=A11)

A3 = store code
A11 = month (4 in this case for april)
The two product codes I have listed are 361034,361073

Thanks again for the help

Greg

 

[Domenic]

Hi Greg!

Actually, your formula is missing a bracket. The formula should be...

=SUMPRODUCT(('2005RA'!A2:A3709=A3)*(ISNUMBER(MATCH('2005RA'!D23709,{361
034,361073},0)))*'2005RA'!I2:T3709*('2005RA'!I1:T1=A11))

Also, I'm assuming that I1:T1 contains numbers 1 through 12,
representing the month.

Does this help?

 

[Greg]

Thanks Domenic - that did the trick it now works.

Any thoughts as to the second to the issue of referencing my part
numbers as a list in a range of cells? I can't get a range to work in
palce of the {#,#,#,#}

If I have to hard code them in I will, but I am just thinking that if
new products come in I will have to change all the formulas with a
frind replace. Would be nice to just change a master area in one
place.

Thanks for the help!!

Greg

 

[Domenic]

You can list your products in a range of cells, and reference that range
in the formula. So, for example, if B1:B8 contains your list of product
numbers...

=SUMPRODUCT(('2005RA'!A2:A3709=A3)*(ISNUMBER(MATCH('2005RA'!D23709,B1:B
8,0)))*'2005RA'!I2:T3709*('2005RA'!I1:T1=A11))

Also, you can use a reference to a dynamic range instead for your
product numbers. This way, you can change your list of numbers without
having to change the reference in your formula each time you add or
delete your product numbers. The range will change automatically. If
you need help with this, post back.

Hope this helps!

 

[Greg]

Thanks a million Domenic!!

I worked on the worksheet till late last night and most of this
morning. I have to put it down now till later tomorrow. So I won't
get to try putting the range in the formula to list out my product
numbers.

I have created dynamic ranges before by using a count statment
somewhere on the worksheet. I then used the index refering to the
count. I don't think I could do it again without referencing my old
work. Do you have any hints? I would like to make all the ranges
dynamic as the number of rows in the raw data file can vary also.

Thanks again!!
Greg

 

[Domenic]

Try the following...

Insert > Name > Define

Name: ProdNumList (List of product numbers used as your criteria)

Refers to:
=Sheet2!$B$1:INDEX(Sheet2!$B:$B,MATCH(9.99999999999999E+307,Sheet2!$B:$B)
)

*Change the reference for Sheet2 and Column B accordingly.

Click Add

Name: Store

Refers to:
='2005RA'!$A$2:INDEX('2005RA'!$A$2:$A$65536,MATCH(REPT("z",255),'2005RA'!
$A$2:$A$65536))

Click Add

Name: ProdNum

Refers to:
='2005RA'!$D$2:INDEX('2005RA'!$D$2:$D$65536,MATCH(REPT("z",255),'2005RA'!
$A$2:$A$65536))

Click Add

Name: Data

Refers to:
='2005RA'!$I$2:INDEX('2005RA'!$I$2:$IV$65536,MATCH(REPT("z",255),'2005RA'
!$A$2:$A$65536),MATCH(9.99999999999999E+307,'2005RA'!$I$1:$IV$1))

Click Ok

Note that you can continue adding more months past Column T and the
range will automatically adjust.

Hope this helps!