# is a named range the solution?

#### unstuck

##### New Member
Hi,
How do I create a formula to copy a value from a certain cell into a formula based on information from another cell(s).
For example if
A1=John A2=smith A3=value of interest1
B1=John B2=smith B3=value of interest2
A1=John A2=Jones A3=value of interest1
B1=John B2=Jones B3=value of interest2

I would like the formula to take the value from cell in column 3, depending on which john I am interested in, and say make an average.
Of course the problem is that although the related cells will always be adjacent to each other, the spreadsheet is varaible from time to time (for example John Smith may not be there at all, or represented only once, in which case the formula calculation is unncessary)
could the formula be something like
If A1 and A2 = B1 and B2 then average A3 and B3. But instead of using specific cell addresses, use a named range?
I hope this is even remotely clear!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### JohnG

##### Board Regular
Humm, dont know if this will help.

IF(COUNTIF(smith,"smith")>1,(SUM(smith_Int)/COUNTIF(smith,"smith")),0)

smith= name range
smith_int=interest range

#### unstuck

##### New Member
that sort of works, except that the values I mentioned (eg smith) were arbitrary. I will not actually know what text string will be input there. The important thing is to compare A2 to A5, if the entery is the same, carry out some math on associated cells (in B column) otherwise, skip it!
thanks, I'm further ahead than I was before.

##### MrExcel MVP
On 2002-09-20 10:48, unstuck wrote:
Hi,
How do I create a formula to copy a value from a certain cell into a formula based on information from another cell(s).
For example if
A1=John A2=smith A3=value of interest1
B1=John B2=smith B3=value of interest2
A1=John A2=Jones A3=value of interest1
B1=John B2=Jones B3=value of interest2

I would like the formula to take the value from cell in column 3, depending on which john I am interested in, and say make an average.
Of course the problem is that although the related cells will always be adjacent to each other, the spreadsheet is varaible from time to time (for example John Smith may not be there at all, or represented only once, in which case the formula calculation is unncessary)
could the formula be something like
If A1 and A2 = B1 and B2 then average A3 and B3. But instead of using specific cell addresses, use a named range?
I hope this is even remotely clear!

=SUMPRODUCT((A1:A3=E1)*(B1:B3=F1),C1:C3)/MAX(1,SUMPRODUCT((A1:A3=E1)*(B1:B3=F1)))

where E1 is a value like "john" and F1 a value like "smith" (all without double quotes).

#### paliman

##### Active Member
I think this will do:

In A6 type "John"
In B6 type "Smith"

In C6 array-enter this formula:

=AVERAGE(IF(CONCATENATE(\$A\$1:\$D\$1,\$A\$2:\$D\$2)=CONCATENATE(A6,B6),\$A\$3:\$D\$3))

Now, change the names in A6 and/or in B6 and it will show you the averages

(to array-enter a formula you need to press "ctrl+shift+enter" instead of "enter")

#### paliman

##### Active Member
Sorry, I made a mess with the references. In my prior post, I had constructed a spreadsheet as follows:

A1: John
A2: Smith
A3: Value#1

B1: John
B2: Smith
B3: Value#2

C1: John
C2: Jones
C3: Value#3

D1: John
D2: Jones
D3: Value#4

Then I wrote the formula and it worked OK.

Pali.

Replies
4
Views
309
Replies
2
Views
228
Replies
15
Views
587
Replies
8
Views
223
Replies
1
Views
144

### Forum statistics

1,195,700
Messages
6,011,193
Members
441,594
Latest member
AVO ### 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