Creating a function
 

All,

I created a spreadsheet and would like some assistance in creating a
function or formula. The example below best describes the spreadsheet that I
have:
A B C D
1 AD AA CAD 70
2 AA PEN PM 60
3 PR VP LR 40

Column A = Billing Rate
Column B = Labour Code
Column C = Staff code
Column D = $ amount

On a seperate worksheet, if row showed up as AD-AA-CAD, I would like colum D
automaticaly add the $ amount(70). If a row showed up as AA-PEN-PM, column D
would show up as $60. It is crucial that the formula looks through colum A
first, then B, and lastly C.

Your anticipated assistance is greatly appreciated.
Rick

Creating a function
 

=SUMPRODUCT(--(Sheet1!A1:A3="AD"),--(Sheet1!B1:B3="AA"),--(Sheet1!C1:C3="CAD"),Sheet1!D1:D3)

Adjust range to suit



"BeginnerRick" wrote:

All,

I created a spreadsheet and would like some assistance in creating a
function or formula. The example below best describes the spreadsheet that I
have:
A B C D
1 AD AA CAD 70
2 AA PEN PM 60
3 PR VP LR 40

Column A = Billing Rate
Column B = Labour Code
Column C = Staff code
Column D = $ amount

On a seperate worksheet, if row showed up as AD-AA-CAD, I would like colum D
automaticaly add the $ amount(70). If a row showed up as AA-PEN-PM, column D
would show up as $60. It is crucial that the formula looks through colum A
first, then B, and lastly C.

Your anticipated assistance is greatly appreciated.
Rick

Creating a function
 

Dear Rick,

In cell D1, instead of 70 try following formula:

=IF(AND($A9="AD",$B9="AA",$C9="CAD"),70,IF(AND($A9 ="AA",$B9="PEN",$C9="PM"),60,40))

then copy the formula down to row 3

You can keep on adding conditions in same manner upto 7 levels of If function.

Harsh

"BeginnerRick" wrote:

All,

I created a spreadsheet and would like some assistance in creating a
function or formula. The example below best describes the spreadsheet that I
have:
A B C D
1 AD AA CAD 70
2 AA PEN PM 60
3 PR VP LR 40

Column A = Billing Rate
Column B = Labour Code
Column C = Staff code
Column D = $ amount

On a seperate worksheet, if row showed up as AD-AA-CAD, I would like colum D
automaticaly add the $ amount(70). If a row showed up as AA-PEN-PM, column D
would show up as $60. It is crucial that the formula looks through colum A
first, then B, and lastly C.

Your anticipated assistance is greatly appreciated.
Rick

Creating a function
 

lets say your data is on sheet1

your criteria and formula on sheet2
e.g.

A1: "AD" B1: "AA" C1: "CAD"
on D1
=
SUM((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1! C1:C10=C1)*(Sheet1!D1:D10))

on edit mode Press Ctrl+Shift+Enter
view formula as enclosed by braces { }



"hsg" wrote:


Dear Rick,

In cell D1, instead of 70 try following formula:

=IF(AND($A9="AD",$B9="AA",$C9="CAD"),70,IF(AND($A9 ="AA",$B9="PEN",$C9="PM"),60,40))

then copy the formula down to row 3

You can keep on adding conditions in same manner upto 7 levels of If function.

Harsh

"BeginnerRick" wrote:

All,

I created a spreadsheet and would like some assistance in creating a
function or formula. The example below best describes the spreadsheet that I
have:
A B C D
1 AD AA CAD 70
2 AA PEN PM 60
3 PR VP LR 40

Column A = Billing Rate
Column B = Labour Code
Column C = Staff code
Column D = $ amount

On a seperate worksheet, if row showed up as AD-AA-CAD, I would like colum D
automaticaly add the $ amount(70). If a row showed up as AA-PEN-PM, column D
would show up as $60. It is crucial that the formula looks through colum A
first, then B, and lastly C.

Your anticipated assistance is greatly appreciated.
Rick