formlua code needed???

[richard hales]

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

 

[barry houdini]

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]

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]

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

 

[barry houdini]

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]

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

 

[mgirvin]

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

Z2:Z9 had the numbers to return and then add.

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]

mgirvin.. Thanks the formlua works fine..cheers

 

[barry houdini]

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

Z2:Z9 had the numbers to return and then add.

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]

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!