Formula for calculating a percentage of a range of information

[woodm71]

I am trying to put together a formula that will calculate a percentage of contracts that our company bid against what we receive.

We would also use this to calculate contracts won against bid.

The problem is that the list of contracts will increase as we tender works and therefore the formula needs to be able to take blanks cells into account.

		Tender Bid %	Formula needed here		Tenders Won %	Formula needed here
TXM ITT Ref.	Year	Title	Client	Partner	Client ITT Ref. (If available)	Date Recieved	Bid / No Bid
PCL-ITT-001	2014	Tender 1	ABC	Structures			Bid
PCL-ITT-002	2014	Tender 2	DEF	N/A			Bid
PCL-ITT-003	2014	Tender 3	GHY	N/A			Bid
PCL-ITT-004	2014	Tender 4	LEF	SML			Bid
PCL-ITT-005	2014	Tender 5	BBR	N/A			No Bid
PCL-ITT-006	2014	Tender 6	VRE	N/A			No Bid
PCL-ITT-007	2014	Tender 7	BFK	N/A			Bid
PCL-ITT-008	2014	Tender 8	TRR	Structures		30 September 2014	Bid
PCL-ITT-009

<colgroup><col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3225;"> <col width="50" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1689;"> <col width="170" style="width: 127pt; mso-width-source: userset; mso-width-alt: 5785;"> <col width="149" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5068;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5017;" span="2"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5017;" span="2"> <tbody> </tbody>

Thanks,

Mark

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this help you?

Excel 2010
	A	B	C	D	E	F	G	H
1			Tender Bid %	75%	Tenders Won %	17%
2	TXM ITT Ref.	Year	Title	Client	Partner	Client ITT Ref.	Date Received	Bid / No Bid
3	PCL-ITT-001	2014	Tender 1	ABC	Structures			Bid
4	PCL-ITT-002	2014	Tender 2	DEF	N/A			Bid
5	PCL-ITT-003	2014	Tender 3	GHY	N/A			Bid
6	PCL-ITT-004	2014	Tender 4	LEF	SML			Bid
7	PCL-ITT-005	2014	Tender 5	BBR	N/A			No Bid
8	PCL-ITT-006	2014	Tender 6	VRE	N/A			No Bid
9	PCL-ITT-007	2014	Tender 7	BFK	N/A			Bid
10	PCL-ITT-008	2014	Tender 8	TRR	Structures		30-Sep-14	Bid
11	PCL-ITT-009
Sheet1

Cell Formulas
Range	Formula
D1	=COUNTIF(H:H,"Bid")/SUM(COUNTIF(H:H,{"Bid","No Bid"}))
F1	=COUNTIFS(H:H,"Bid",G:G,">0")/COUNTIF(H:H,"Bid")