How to sum

  • Thread starter Thread starter scoutleader101
  • Start date Start date
S

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!
 
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!
Click to expand...

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
 
Hi scoutleader,
Will this function do what you want? =SUMIF(D$2:D$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
 
Back
Top