Lookups within Array Formulas

[akcramblet]

I'm pretty good with excel, but this is tricky...

I have a table of bid results for multiple projects my company has bid.

Outside of the table, I need to look at a given company's performance, like the total backlog (or rank 1 bids):

{=SUM(([Company]=F2)*([Rank]=1)*([Bid]))} = $691,469

Not too bad.

I'd also like to see the total "tabled" bid dollars for a given company. Tabled dollars is the difference between the first and second ranked bidders on a single project. So for a given company, I need to add up the total rank 1 bids, and subtract that number from the corresponding rank 2 bids. This is what I've tried, but it doesn't seem to work (The problem is in the second half, which is really all I need):

{=SUM(([Company]=F2)*([Rank]=1)*([Bid]))-SUM((([Project]=IF([Company] =F2,IF([Rank]=1,[Project])))*([Rank]=2)*([Bid Amount])))}

I've never encountered this type of puzzle before, but can't afford the time to figure it out.

Any help is appreciated. Thanks.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Project	Company	Bid	Rank		Company	Backlog	Tabled $
2	AAA	Bill	$1,583,777	1		Silas	$691,469	????
3	AAA	Bob	$1,644,807	2
4	AAA	John	$1,645,761	3		???? =
5	AAA	Silas	$1,704,000	4		$57,872
6	BBB	Silas	$210,000	1
7	BBB	Bob	$215,000	2
8	BBB	John	$223,500	3
9	CCC	Silas	$481,469	1
10	CCC	Bill	$534,341	2
11	EEE	John	$1,365,449	1
12	EEE	Bill	$1,367,757	2
13	EEE	Silas	$1,393,100	3
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[vaskov17]

how do you get to $57,872?

 

[akcramblet]

The sum Copmany Silas' successful bids is $691,469. On the projects that Company Silas successfully bid, the 2nd ranked bidders total $749,341. The difference is $57,872, or the total amount that company Silas "Left on the Table."

Make sense?

 

[pgc01]

Hi

You should always post your excel version. A good idea is to add it to your board signature.

Try in F5:

=SUM(IF(B2:B13=F2,IF(D2:D13=1,SUMIFS(C2:C13,A2:A13,A2:A13,D2:D13,2)-C2:C13)))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

 

[pgc01]

P. S. The formula I posted works on a list that is not ordered. If the list you have is ordered, like in the example you posted, by ranking of the bids, then the formula is very simple. In F5:

=SUM(IF(B2:B13=F2,IF(D2:D13=1,C3:C14-C2:C13)))

... also confirmed with cse.

 

[mgirvin]

pgc01,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Absolutely magnificent!<o></o>
<o></o>
I had never seen this:<o></o>
<o></o>
SUMIFS(C2:C13,A2:A13,A2:A13,D2:D13,2)<o></o>
<o></o>
as a means to gather up all the second place amounts and repeat for each Project!<o></o>
<o></o>
Absolutely great!
<o></o>
Taking your trick and adapting it to a non-Ctrl+Shift+Enter formula, maybe:<o></o>
<o></o>
=SUMPRODUCT(--(B2:B13=F2),--(D2:D13=1),SUMIFS(C2:C13,A2:A13,A2:A13,D2:D13,2)-C2:C13)
<o></o>
<o></o>
This is great if you have Exel 2007 or later, but what if you had 2003 or ealier?

I could not think of a good single cell formula. I could only come up with a Helper Column Method like this:

<TABLE style="WIDTH: 439pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=584><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 38.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 height=51 width=64>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=64>Company</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=86>Bid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=64>Rank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=64>Company</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=82>Backlog</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=73>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=87>Amount of Second Bid</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl72 height=20 width=64>AAA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl72 width=64>Bill</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=86>$1,583,777 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl72 width=64>Silas</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=82>$691,469 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>BBB</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Silas</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$210,000 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>BBB</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87>$215,000 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>CCC</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Silas</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$481,469 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>CCC</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87>$534,341 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>EEE</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>John</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$1,365,449 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>AAA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Bob</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$1,644,807 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>BBB</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Bob</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$215,000 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>CCC</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Bill</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$534,341 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>EEE</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Bill</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$1,367,757 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>AAA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>John</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$1,645,761 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>BBB</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>John</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$223,500 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 height=20 width=64>EEE</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Silas</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=86>$1,393,100 </TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl67 width=64>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 height=21 width=64>AAA</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>Silas</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 65pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 width=86>$1,704,000 </TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=64>4</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64></TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=82></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=34></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=73>Left on Table</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=87>$57,872 </TD></TR></TBODY></TABLE>

Cell F2:

=SUMPRODUCT(--(B2:B13=E2),--(D2:D13=1),C2:C13)

Cell G2 and copy down:

=IF(AND(B2=$E$2,D2=1),A2,"")

Cell H2 with CSE and copy down:

=IF(G2="","",INDEX($C$2:$C$13,MATCH(G2&2,$A$2:$A$13&$D$2:$D$13,0)))

Cell H14:

=SUM(H2:H13)-F2

Any ideas?

 

[pgc01]

Hi Mike

Remark: if you can first sort the data then my second solution is much easier.

If you can't or don't want to assume the data as sorted, then SumIfs() is really handy to help solve the problem. For excel versiond prior to 2007, I guess the formula will not be as efficient.

I think a possible solution is:

=SUM(IF(B2:B13=F2,IF(D2:D13=1,IF(A2:A13=TRANSPOSE(A2:A13),IF(TRANSPOSE(D2:D13)=2,TRANSPOSE(C2:C13)-C2:C13)))))

... confirmed with CSE.

 

[akcramblet]

PGCo1 has the Solution for excel 2007. Thank you, you're a wizard... sumifs.. Very cool.

Also, thank you for the posting tips. More than happy to comply.

 

[mgirvin]

pgc01,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
No doubt that if you can sort, sort. I was looking for a formula that would work without sort. That is why the SUMIFS is so cool for 2007 and later.<o></o>
<o></o>
For 2003, although the TRANSPOSE formula is expensive, it is cool to see how the TRANSPOSE helps to deal with the multiple criteria from the Project column. When I was trying to come with something, I was not sure how to deal with the “BBB" or "CCC" in the Project column and the TRANSPOSE does deal with it!<o></o>
<o></o>
Thanks for the great tips!<o></o>
<o></o>

 

[akcramblet]

Right,

Sorting wouldn't really work in this scenario anyway. Luckily, I shouldn't have to deal with 2003 too much. This works perfect, plus I'll want to deal with more data eventually anyway, like dates and bid size bins... sumifs will take care of all that.

I should probably break down and make a database I guess, but lack the time and skill.

Thanks again. Let me know if I can return the favor.