SumIf the Sum of Unique Values is greater than criteria

S

sfranks105

New Member
Joined
Jun 30, 2008
Messages
10
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>
 
Sort by date Sort by votes
sfranks105 said:
I thought I did answer your question.....
Click to expand...

Apologies. You did but I wasn't clear on the answer

I believe you could do this with DSUM but I'm not sure how well it'll work on a large dataset.

If you have your criteria in the range F2:H3 like this

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=64 height=18 u1:num>Year</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Stage</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num u1:num>2004</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Exit</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:bool="TRUE">TRUE</TD></TR></TBODY></TABLE>

Where the formula in H3 is

=SUMPRODUCT((A$2:A9999=A2)*(B$2:B$9999=F$3)*(D$2:D$9999=G$3),C$2:C$9999)<=100

Then you can use DSUM formula

=DSUM(A1:D9999,3,F2:H3)
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I was on vacation for a while so I apologize for the delayed response. Thank you very much for your help Barry, unfortunately I can't seem to get this method to bounce back the right numbers.

Here is what I am getting and an explanation below. When I used your method on the data I showed the group it did give me the right number but as soon as I added companies the same companies from 2005 it did not work. Please see below.

<table x:str="" style="border-collapse: collapse; width: 432pt;" border="0" cellpadding="0" cellspacing="0" width="576"><col style="width: 48pt;" span="9" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" x:str="Company " width="64" height="17">Company </td> <td class="xl22" style="width: 48pt;" x:str="Year " width="64">Year </td> <td class="xl22" style="width: 48pt;" x:str="Amount " width="64">Amount </td> <td class="xl22" style="width: 48pt;" width="64">Stage</td> <td style="width: 48pt;" width="64">
</td> <td class="xl22" style="width: 48pt;" width="64">Year</td> <td class="xl22" style="width: 48pt;" width="64">Stage</td> <td class="xl22" style="width: 48pt;" width="64">Unique</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="XYZ " height="17">XYZ </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">10</td> <td class="xl22">Exit</td> <td>
</td> <td x:num="" align="right">2004</td> <td>Exit</td> <td x:bool="TRUE" align="center">TRUE</td> <td x:num="" align="right">153</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="XYZ " height="17">XYZ </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">20</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="ABC " height="17">ABC </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">5</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="ABC " height="17">ABC </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">25</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="DEV " height="17">DEV </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">14</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="DEV " height="17">DEV </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">19</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">100</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">120</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">100</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl22">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BLU</td> <td class="xl22" x:num="" align="right">2005</td> <td class="xl22" x:num="" align="right">25</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl22">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:str="TRU " height="17">TRU </td> <td class="xl22" x:num="" align="right">2005</td> <td class="xl22" x:num="" align="right">35</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl22">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BLU</td> <td class="xl22" x:num="" align="right">2004</td> <td class="xl22" x:num="" align="right">35</td> <td class="xl22">Exit</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl22">
</td> <td>
</td> </tr> </tbody></table>

So for instance, when I added lines with companies that already existed Excel seems to be ignoring the the other criteria and just adding the company values if they meet the less than 35 statement.

EX. When I run this routine with the criteria above and Unique criteria as the SUMPRODUCT as shown below;

=SUMPRODUCT((A$2:A9997=A2)*(B$2:B$9997=F$2)*(D$2:D$9997=G$2),C$2:C$9997)<=35

And the DSUM as shown below;

=DSUM(A1:D9997,3,F1:H2)

It gives me 153, when I should get 128. So it is adding BLU, 2005, 25, Exit line. This line should be excluded because it is not a 2004 investment. Now it will add right if I change that same line to Stage=Seed so that 2 out of the three criteria are met. Can anyone explain where I am going wrong?

Thanks
 
Upvote 0
Sorry, I missed out a $ in the formula criteria which is important, amend to

=SUMPRODUCT((A$2:A$9997=A2)*(B$2:B$9997=F$2)*(D$2:D$9997=G$2),C$2:C$9997)<=35

Then you should get 128.......
 
Upvote 0
Once again thanks for sticking with it, it appears that it still is not working.
Exercise in Frustration.xls
ABCDEFGHI
1CompanyYearAmountStageYearStageUnique128<-----Shouldequalthis
2XYZ200410Exit2004ExitTRUE188
3XYZ200420Exit
4ABC20045Exit
5ABC200425Exit
6DEV200414Exit
7DEV200419Exit
8TRU2004100Exit
9TRU2004120Exit
10TRU2004100Exit
11BLU200525Exit
12XYZ200535Exit
13BLU200435Exit
Sheet1



Here is my sheet, its adding 2005 numbers into the total. Any help would be appreciated.

Thanks
 
Upvote 0
I can't replicate that result. When I use exactly the same data and formulas I get 128 as expected!

Do you have any data below row 13 that might contribute the extra 60 to the result?

What do you get if you put 2005 in E2?
 
Upvote 0
Barry, thanks for your help, I got it to work by transferring all the data to another sheet and retyping the routines in. Thanks again, Cheers. Scott
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
435
smozgur
smozgur
M
  • Solved
VBA to Split value across uniques found with multiple criteria
Replies
1
Views
419
melkent
M
R
  • Question
Sumifs with dynamic criteria range
Replies
4
Views
1K
Peter_SSs
Peter_SSs
A
  • Question
Lookup+sum+max
Replies
2
Views
532
aarky
A
E
  • Question
Count filtered unique values where sum of values in another column meets criteria
Replies
9
Views
995
excelhelppleas
E

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top