How to sum

[scoutleader101]

I have a column of numbers representing bids from vendors X, Y and Z.
In the Winner column I use Nested IF statements utilizing the MIN
command to determine which vendor bid the lowest and then I use just
the MIN command to list the lowest bid.

X Y Z Winner
$1 $2 $3 X $1
$3 $2 $4 Y $2
$3 $3 $2 Z $2
$2 $3 $4 X $2
$3 $2 $4 Y $2
$4 $5 $2 Z $2

$16 $17 $19 $11
$3 $4 $4

I have summed each column to determine the total bid amount of each
vendor. How do I sum up each column such that only that vendors
winning bid is totalled? Eg. Vendor X bid a total of $16 for all
items. Its winning bids totalled $3.

I will then use the total value of all lowest bids ($11) to determine
the percentage that each vendor won of the total amount. Eg. Vendor X
is getting 27% ($3/$11) of the total amount of the contract.

The problem I'm having is how I go about determining that $3 total for
vendor B.

Help appreciated!

 

[Ron Rosenfeld]

I have a column of numbers representing bids from vendors X, Y and Z.
In the Winner column I use Nested IF statements utilizing the MIN
command to determine which vendor bid the lowest and then I use just
the MIN command to list the lowest bid.

X Y Z Winner
$1 $2 $3 X $1
$3 $2 $4 Y $2
$3 $3 $2 Z $2
$2 $3 $4 X $2
$3 $2 $4 Y $2
$4 $5 $2 Z $2

$16 $17 $19 $11
$3 $4 $4

I have summed each column to determine the total bid amount of each
vendor. How do I sum up each column such that only that vendors
winning bid is totalled? Eg. Vendor X bid a total of $16 for all
items. Its winning bids totalled $3.

I will then use the total value of all lowest bids ($11) to determine
the percentage that each vendor won of the total amount. Eg. Vendor X
is getting 27% ($3/$11) of the total amount of the contract.

The problem I'm having is how I go about determining that $3 total for
vendor B.

Help appreciated!

Assume your data above is in A1:E7.

Columns A, B, C contain the bids from each vendor; Columns D & E contain the
winning vendor and winning bid for each item.

Sum of lowest bids for Vendor X:

=SUMIF($D$2:$D$7,"X",$E$2:$E$7) or

=SUMIF($D$2:$D$7,A1,$E$2:$E$7)


--ron

 

[Ken Johnson]

Hi scoutleader,
Will this function do what you want? =SUMIF(D$2$7,"X",E$2:E$7)
It assumes winners in column D, winning bids in column E, values from
rows 2 to 7 and bidder is X.
Ken Johnson

 

[scoutleader101]

Thanks Ron and Ken! It works perfectly.

 

[Biff]

Hi!

Take a look at this screencap.

http://img294.imageshack.us/img294/2391/sumif1ta.jpg

Enter the formula in A10 and just copy across.

Biff

 

[Ron Rosenfeld]

Thanks Ron and Ken! It works perfectly.

You're welcome. Glad to help.
--ron