# addition to a specific formula to deduct 1 pax each certain Number

#### Hany ali

##### Active Member
hello my masters, i want your help to amend for this formula

Code:

=IF(OR(\$B3="",\$C3=""),"",\$C3*VLOOKUP(\$C\$1&\$F3,Prices!\$B\$3:\$F\$714,4,0)+\$D3*VLOOKUP(\$C\$1&\$F3,Prices!\$B\$3:\$F\$714,5,0))

to can to deduct 1 pax each number of pax in Cell J1 Value
in the same items each Invoice
I mean for Example ,if I have in this invoice like more than 10 pax in
compact car … it should be deduct 1 pax each 10 pax( cell J1)

#### Attachments

• 49.2 KB Views: 4
• 59.9 KB Views: 4
Last edited by a moderator:

#### bosco_yip

##### Excel Ninja
Maybe,

In E3, copied down :

=IF((\$B3="")+(\$C3=""),"",SUM(VLOOKUP(\$C\$1&\$F3,Prices!\$B\$3:\$F\$714,{4,5},0)*(\$C3:\$D3-INT(\$C3:\$D3/\$J\$1))))

Regards
Bosco

#### Hany ali

##### Active Member
thanks alot my Dear... it's very Nice Array
but when i have for example in the same Invoice from the same item 33 pax ,it should to deduct 3 pax ,even in differant Rows in the Invoice

#### Attachments

• 54.9 KB Views: 8
• 62.6 KB Views: 5
Last edited:

#### bosco_yip

##### Excel Ninja
This additional requirement would require longer formula

In E3, copied down :

=IF((\$B3="")+(\$C3=""),"",VLOOKUP(\$C\$1&\$F3,Prices!\$B\$3:\$F\$714,4,0)*(\$C3-INT(SUMIF(\$F\$2:\$F3,\$F3,\$C\$2:\$C3)/\$J\$1)+INT(SUMIF(\$F\$2:\$F2,\$F3,\$C\$2:\$C2)/\$J\$1))+VLOOKUP(\$C\$1&\$F3,Prices!\$B\$3:\$F\$714,5,0)*(\$D3-INT(SUMIF(\$F\$2:\$F3,\$F3,\$D\$2:\$D3)/\$J\$1)+INT(SUMIF(\$F\$2:\$F2,\$F3,\$D\$2:\$D2)/\$J\$1)))

#### Hany ali

##### Active Member
Thank you very much, tired of you, exactly that is required