Need Help using Arrays

[nickos]

Hi,

Can I please get some help on ideas with a formula on the following:

I have the scenario:

Code: Acceptable Ranges

100200 100180 - 100290


-----------------------------------------------------------------------------------
G Code Number 1 Number 2

100185 100 200
100285 100 200
100385 100 200

Looking at these two tables, can you suggest a useful formula or combination of formulas to do the following in one cell:

- Look in the second table and take the sumsquare (sum(Num1*Num2)) of those codes that are included in the acceptable range for the table above?

Intuitively, we can see that the answer would be 400, since only the first two codes in the second table would be picked up by the criteria, BUT HOW do you do this in Excel? SUMIF? ARRAYS? HELP .

 

[acw]

Hi

Can the acceptable ranges be split over 2 columns?

Shouldn't the result be 40,000 not 400?


Tony

 

[nickos]

Using Arrays

Hi

Can the acceptable ranges be split over 2 columns?

Shouldn't the result be 40,000 not 400?


Tony

Yes, the ranges can be split over two columns,

and yes, its 40,000 my bad.

nickos.

 

[acw]

Hi


Assuming

A1: Code
A2: 100200
B1: Upper
B2: 100180
C1: Upper
C2: 100290
E1: G Code
F1: Number 1
G1: Number 2
E2:G4 - numbers as above

Formula
=SUMPRODUCT(--(E2:E4>=B2),--(E2:E4<=C2),(F2:F4),(G2:G4))


HTH

Tony

 

[nickos]

Help with Using Arrays

Thanks Tony!

I'll give it a whirl.

A Clever Wizard!

Nick.

 

[nickos]

Hey Tony.

I tried the formula out using the cellls you suggested.

I'm just finding that the formula returns zero. I used CSE to activate the array calculation but nothing's happening.

What do the double dashes in the formula do? I have never seen these used in Excel before

Nick

 

[acw]

Nick

1) No need for CSE - just enter normally
2) the -- convert a true / false result into a number. The first will convert it to a negative number, and the second return it to the positive...


Tony

 

[nickos]

Yep.

That time it worked, and I forgot to convert the numbers from Text to Column data type.

Cheers!