SumIf the Sum of Unique Values is greater than criteria

[sfranks105]

I have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold.

For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further.


For Unique values I used this function:
=SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1
Then:
=DCOUNTA($A$1:$D9999,2, Criteria)
or
=DSUM($A$1:$D9999,2, Criteria)

This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria.

I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated.

<table x:str="" style="border-collapse: collapse; width: 297px; height: 131px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" x:str="Company " width="64" height="17">Company </td> <td style="width: 48pt;" width="64">Year</td> <td style="width: 48pt;" width="64">Amount</td> <td style="width: 48pt;" width="64">Stage</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">XYZ</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">10</td> <td>Seed</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">XYZ</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">20</td> <td>Seed</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ABC</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">5</td> <td>Early</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ABC</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">25</td> <td>Early</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">DEV</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">14</td> <td>Later</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">DEV</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">19</td> <td>Later</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">TRU</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">100</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">TRU</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">120</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">TRU</td> <td x:num="" align="right">2004</td> <td x:num="" align="right">100</td> <td>Exit
</td> </tr> </tbody></table>

 

[DonkeyOte]

Could you use a pt ?

I was trying to work out how to deactivate results based on sum totals using VBA (ie only show where sum < criteria) but I couldn't work out how to iterate the results in the PT well enough... maybe someone else could.

You could set Year & Stage as Page Fields, Company as Row field with Amount as DataField.

If someone can come up with a way of filtering out any results where Sum(Amount) > Criteria that would be great as it would avoid the need to do any calculations on your source data sheet... given you have so many rows using sumproducts would be very intensive.

If you didn't care you could still use a PT approach by adding an additional field to your source data which would give you sum of combination, ie

=SUMPRODUCT(--($A$2:$A$10=$A2),--($B$2:$B$10=$B2),--($D$2:$D$10=$D2),--($C$2:$C$10))

Then adjust to merely return a 0/1 where 1 is valid based on total criteria, ie assuming your total criteria was set on Sheet called "PIVOT" in cell A1

=1*(PIVOT!$A$1>SUMPRODUCT(....))

You could add a Pivot (sheet PIVOT) and use this new field as a Row Field and hide "0" values so only valid records are returned.

Downside... if you wanted to change criteria and auto update the PT (ie without having to Right Click - Refresh) you would need to put in some worksheet_calculation routine which would slow you down... but not as much as having all those SUMPRODUCTs

hopefully someone can show you and me how to iterate the Data field values in a PT and unhide associated row fields according to a test (value > criteria value)

 

[Fazza]

Hi,

I suspect this can be readily done with the help of a little SQL. (And avoid all worksheet formulas, BTW.) It is very well suited to handling 16,000 records efficiently. Maybe a cross tab report. That is like a matrix sort of table; like a pivot table but without a pivot table's dynamic flexibility. However, I don't understand exactly what you want.

For example, a sum is wanted and for the sample data the result is 0. I don't see how any of those values can add to zero. Presumably it is a null result & if so this sort of work is best done with a set theory type approach, such as databases uses. So a little SQL: it should be able to handle the criteria easily. (If is suits a pivot table, like lasw10 suggested, the SQL can be used as a step in between the source data and the pivot table. One way to do this is when starting the pivot table wizard select the external data option. Can be to the same workbook.)

Can you please provide the sample result that you want?

It will be tomorrow before I might be able to look at this, BTW.

regards, Fazza

 

[sfranks105]

Thanks for the reply, still looking to help myself just thought maybe someone knew how to do this, I will try your idea and see if I can't get it to work. As a disclaimer I am a VBA novice so I have not tried to formulate a plan in that respect.

The Sumproduct calculations were used because for some odd reason Pivot Tables won't count unique company values for me (ie when I have multiple company investments in one company, I tell it to count it counts each entry as unique instead of filtering by company; another problem for another day).

I have played around with getting pivot table data but the limitation there is that it won't autorefresh the pulled data table, I often need to change my pivot data view by manipulating the dropdown menus and then the table of Getpivotdata is all messed up. Also you can only pull data that is visible and I have way too many entries to make it visible all the time, I guess I could add another pivot table to an already growing spreadsheet.

Like everyone I would like to consolidate the view, tell excel to "work smarter not harder." Unfortnately I am coming to the realization that we don't speak the same language.

 

[Fazza]

Various comments

1. Again - main point - I'm not clear on what you are wanting. So very difficult to advise you.
2. People probably know how to do what you want, but point 1 applies...
3. With my idea no need for VBA. I'd probably use VBA but it might be readily doable & very fast without VBA and without formulas!
4. With sumproduct formulas over large ranges you might experience slow calculation time if you have lots of formula. And the formulas have to be set up in the cells where the results will be as opposed to a database type approach - with SQL like I alluded to - which will have no formulas, be fast and dynamically adjust the output table to suit how many results there are. No data rows when no records in the dataset, 2000 rows when there are 2000 results.
5. To whet your appetite, by using SQL before the pivot table steps like these can be done: unique records, counts, filtering, all sorts of arithmetic & text functions, formulas, combining data from multiple locations including closed files. All without VBA.
6. Using a pivot table will significantly increase your file size. A query table approach (which is a non program way to get a cross tab result via some SQL) will not add as much memory.
7. I don't know what you mean about the PT limitation about "won't autorefresh the pulled data table". Though maybe that can be overcome with a line of VBA. Just one to refresh the PT.

A general comment. With 16,000 records a database type approach is likely best. This can efficiently handle lots of data. Usually with sort of task I set up the workbook to have no formulas and I typically have VBA using ADO & SQL. Although it is an advanced approach it is actually fairly simple to do - once you know how...

HTH. Regards, Fazza

 

[sfranks105]

Fazza - sorry I am behind you guys, earlier reply was for lasw10, thank you for your response, I was suspicious of using the method you suggest only because the people using the report are not capable of trouble shooting SQL if something goes wrong. Trying to keep it simple for non technically saavy individuals.

So here goes my attempt at further explaining, I modified my table to better show what I need.

<table x:str="" style="border-collapse: collapse; width: 221pt;" width="294" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <col style="width: 55pt;" width="73"> <col style="width: 65pt;" width="87"> <col style="width: 39pt;" width="52"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 62pt;" x:str="Company " width="82" height="17">Company </td> <td style="width: 55pt;" x:str="Year " width="73">Year </td> <td style="width: 65pt;" x:str="Amount " width="87">Amount </td> <td style="width: 39pt;" width="52">Stage</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="XYZ " height="17">XYZ </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">10</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="XYZ " height="17">XYZ </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">20</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="ABC " height="17">ABC </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">5</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="ABC " height="17">ABC </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">25</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="DEV " height="17">DEV </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">14</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="DEV " height="17">DEV </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">19</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">100</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">120</td> <td>Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td x:num="" align="right">2004</td> <td x:num="" align="right">100</td> <td>Exit</td> </tr> </tbody></table>
Criteria:
Year=2004
Stage=Exit
SUM(Amount<=100) (this would be sum of Amounts corresponding to unique Companies)

Result Example:
<table x:str="" style="border-collapse: collapse; width: 268pt;" width="356" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <col style="width: 55pt;" width="73"> <col style="width: 65pt;" width="87"> <col style="width: 39pt;" width="52"> <col style="width: 47pt;" width="62"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 62pt;" width="82" height="17">Year</td> <td style="width: 55pt;" width="73">Seed</td> <td style="width: 65pt;" width="87">Early</td> <td style="width: 39pt;" width="52">Later</td> <td class="xl24" style="width: 47pt;" width="62">Exit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2004</td> <td class="xl24" x:num="0" align="right">$0.00</td> <td class="xl24" x:num="0" align="right">$0.00</td> <td class="xl24" x:num="0" align="right">$0.00</td> <td class="xl24" x:num="93" align="right">$93.00</td> </tr> </tbody></table>
If you notice the company TRU fits all criteria except the SUM amount and thus it is excluded from the summation.

Here are my constraints, the data is constantly being added to, the format of the data table must remain structurally the same and I am trying to remain in Excel, not because I like to, but because of the reason given earlier.

 

[barry houdini]

When determining whether a company meets the sum amount criteria [ i.e. < 100 ] are you summing all amounts for that company or only those that also meet the year and stage criteria?

 

[sfranks105]

They have to meet the earlier criteria first, so Year and Stage must be prioritized.

Thanks

 

[barry houdini]

I don't think that really answers my question.

What I meant was, if the data was slightly different, e.g. like this

<TABLE style="WIDTH: 212pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=282 border=0 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>Company</TD><TD class=xl23 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Year</TD><TD class=xl23 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Amount</TD><TD class=xl24 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Stage</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>XYZ</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>10</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>XYZ</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>20</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>ABC</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>5</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>ABC</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>25</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>DEV</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>14</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>DEV</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>19</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>TRU</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>90</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=90 height=17>TRU</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2005</TD><TD class=xl26 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>120</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 0.5pt solid; BACKGROUND-COLOR: white" width=64>Later</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #666699 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=90 height=18>TRU</TD><TD class=xl29 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>2006</TD><TD class=xl29 style="BORDER-RIGHT: #666699 0.5pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>100</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64>Exit</TD></TR></TBODY></TABLE>

then given the same criteria as before, i.e. 2004 and Exit, what should the result be? TRU has a total greater than 100 but TRU records for 2004 and Exit sum to less than 100

 

[sfranks105]

I thought I did answer your question but anyhow.

"When determining whether a company meets the sum amount criteria [ i.e. < 100 ] are you summing all amounts for that company or only those that also meet the year and stage criteria?"

Answer: Only those that meet the year and stage criteria should be tested against the final criteria. That is what I meant by prioritized. The spreadsheet contains thousands of records and investments in companies over many years so your example is right on.

Answer to Q2: the returned value should be 183.

Thanks for any help you can provide, hope I fully answered your questions this time.