Function or Code
 

Greetings
I am not sure whether my goal can be acccomplished through a funtion or if I
will have to use code. I have an inventory spreadsheet that has part numbers
on hand qty and on order qty.
Part #
On Hand
On Order

Because of the layout of the sheet the Part# may appear in different rows or
different columns, but its always in row 3,6,9, etc. I would like to either
through input box and code, or a formula be able to enter the part# and get
the total on hand and on order.
Thanks

'Sumif' seems best here.
For Example,
if the part # can be in Col A, B or C and the On-Hand Qty is in Col D, put
the Part # in E1 and put the following formula in E2...
=SUMIF(A:A,E1,D:D)+SUMIF(B:B,E1,D:D)+SUMIF(C:C,E1, D:D)

HTH,
Gary


"Allan" wrote:

Greetings
I am not sure whether my goal can be acccomplished through a funtion or if I
will have to use code. I have an inventory spreadsheet that has part numbers
on hand qty and on order qty.
Part #
On Hand
On Order

Because of the layout of the sheet the Part# may appear in different rows or
different columns, but its always in row 3,6,9, etc. I would like to either
through input box and code, or a formula be able to enter the part# and get
the total on hand and on order.
Thanks

Hi Gary
Thanks for the reply....
What if a3=PART#1
A2=15 (on hand)
A3=5 (on order)
C9=PART#1
C10= 10 (on hand)
C11= 10 (On order)

In a21 user inputs Part#1 and a22 is calculated to 25 and a23 is calculated
to 15.
Thanks
"Gary Brown" wrote:

'Sumif' seems best here.
For Example,
if the part # can be in Col A, B or C and the On-Hand Qty is in Col D, put
the Part # in E1 and put the following formula in E2...
=SUMIF(A:A,E1,D:D)+SUMIF(B:B,E1,D:D)+SUMIF(C:C,E1, D:D)

HTH,
Gary


"Allan" wrote:

Greetings
I am not sure whether my goal can be acccomplished through a funtion or if I
will have to use code. I have an inventory spreadsheet that has part numbers
on hand qty and on order qty.
Part #
On Hand
On Order

Because of the layout of the sheet the Part# may appear in different rows or
different columns, but its always in row 3,6,9, etc. I would like to either
through input box and code, or a formula be able to enter the part# and get
the total on hand and on order.
Thanks