# formlua code needed???

#### richard hales

##### Board Regular
hi all
am looking for a formlua which will leave me a number if a code number is put into a cell and if no code number is put in to return as blank...
Cell A1 to Cell A4 is the cell's area
Cell A5 will hold the = from the code or be blank.
codes:
S2 =2
S6 =6
S7 =7
S8 =8
H2 =2
H6 =6
H7 =7
H8 =8

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### barry houdini

##### MrExcel MVP
Hello richard,

Do you mean that there will be codes in all 4 cells A1 to A4. Should A5 have the "sum" of all the codes entered?

#### richard hales

##### Board Regular
Hi Barry
Thanks for repling am looking for, should i enter any of the codes into any of the cell's A1 to A4 cell A5 will only give me the = number or stay blank you could say that Cell A5 will look in A1 to A4 for a answer.

#### mgirvin

##### Well-known Member
Dear richard hales,

I am not sure what you mean when you say “Cell A1 to Cell A4 is the cell's area”.

But this formula would work for checking A1 for a code and then returning the associated number to the cell:

=IF(A1="","",VLOOKUP(A1,{"S2","2";"S6","6";"S7","7";"S8","8";"H2","2";"H6","6";"H7","7";"H8","8"},2,0))

Or if you had the codes in column C and numbers in column D (C3:D10), this would work:

=IF(A1="","",VLOOKUP(A1,C3:D10,2,0))

If as barry houdini suggested you need a formula for summing this could work:

Enter with the key strokes “Ctrl + Shift + Enter”:

=IF(AND(A1:A4=""),"",SUM((A1:A4={"S2";"S6";"S7";"S8";"H2";"H6";"H7";"H8"})*({"2";"6";"7";"8";"2";"6";"7";"8"})))

Or this if values are in cells:

Enter with the key strokes “Ctrl + Shift + Enter”:

=IF(AND(A1:A4=""),"",SUM((A1:A4=C3:C10)*(D3:D10)))

Last edited:

#### barry houdini

##### MrExcel MVP
The simplest way would probably to enter your codes somewhere on the worksheet, e.g. in Y2:Y9 with the corresponding values in Z2:Z9 then formula in A5 could be

=IF(COUNTA(A1:A4),SUMPRODUCT(SUMIF(Y2:Y9,A1:A4,Z2:Z9)),"")

#### richard hales

##### Board Regular
Thanks Barry it works just the way i need it to... what can i say but your the man.

#### mgirvin

##### Well-known Member
Dear barry houdini,

I like the COUNTA for the true false test, it avoids the AND(A1:D1="") Ctrl + Shift + Enter!

But I could not get this to work:

SUMPRODUCT(SUMIF(Y2:Y9,A1:A4,Z2:Z9))

Let me make sure that I got the arguments right in the SUMIF:

Y2:Y9 will be the codes S2, S6. etc.

A1:A4 has the codes to lookup

And another question:

You used SUMPRODUCT(SUMIF(Y2:Y9,A1:A4,Z2:Z9)) to avoid "Ctrl + Shift + Enter", right? And this is because the SUMPRODUCT handles arrays without Ctrl + Shift + Enter?

#### richard hales

##### Board Regular
mgirvin.. Thanks the formlua works fine..cheers

#### barry houdini

##### MrExcel MVP
Dear barry houdini,

I like the COUNTA for the true false test, it avoids the AND(A1:D1="") Ctrl + Shift + Enter!

But I could not get this to work:

SUMPRODUCT(SUMIF(Y2:Y9,A1:A4,Z2:Z9))

Let me make sure that I got the arguments right in the SUMIF:

Y2:Y9 will be the codes S2, S6. etc.

A1:A4 has the codes to lookup

And another question:

You used SUMPRODUCT(SUMIF(Y2:Y9,A1:A4,Z2:Z9)) to avoid "Ctrl + Shift + Enter", right? And this is because the SUMPRODUCT handles arrays without Ctrl + Shift + Enter?

I think all of your assumptions are correct Mike. Yes, you could use

=SUM(SUMIF(Y2:Y9,A1:A4,Z2:Z9))

....but it would require CSE. I've got nothing against "array entered" formulas but obviously some people prefer to avoid them or don't know how to apply them so if it's simple enough to do I'll try to avoid them.

#### mgirvin

##### Well-known Member
Dear barry houdini,

Got it! It does make sense to provide a non CSE, because most don't do the CSE.
In addition to the COUNTA on/off trick, thanks for the SUMIF inside the SUMPRODUCT trick also!
Two new tricks for my Excel tool box!

Replies
1
Views
138
Replies
14
Views
426
Replies
1
Views
361
Replies
9
Views
124
Replies
3
Views
93

1,190,597
Messages
5,981,856
Members
439,738
Latest member
Knockout1992

### 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