# Need Help using Arrays

#### nickos

##### New Member
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 .

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi

Can the acceptable ranges be split over 2 columns?

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

Tony

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.

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

Help with Using Arrays

Thanks Tony!

I'll give it a whirl.

A Clever Wizard!

Nick.

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

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

Yep.

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

Cheers!

Replies
5
Views
196
Replies
6
Views
250
Replies
9
Views
735
Replies
15
Views
305
Replies
1
Views
227

1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back