# Sum Column Than Look For Cell

#### JMata806

##### New Member
Greetings I am a green horn in Excel and studying up to make my life fun and efficient.
I've used this site many times to get broaden my knowledge on Excel and thank each and every brain head here!

However, I've hit a dead end and perhaps I am overthinking or just not using the right wording when searching this vast forum.
I am attempting to setup either a VBA or formula to sum off a specific table say (Table 1 is Apples, Table 2 is Oranges, etc) using SUMIFS which helps me get the totals no issues.

Now I want to know based on this list when said amount reaches a total, say ">=10" upon which it will report a corresponding column that has the date.
So in short it would pull the date of when the sum of a count reaches 10 or more.

In case my words are confusing I drew up a basic idea to help understand my process.

So if you can help me I would greatly appreciate it, I was thinking of using Lookup or such to resolve this but again I've lost myself in trying to resolve this and for all I know it may be simple and I just am looking at this more difficultly than it should.

^_^

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
Doing it with a single formula in the output table would need a convoluted combination of sumproduct, offset, subtotal, and others.

Much easier would be a helper column in the source table. Use this In D2, then fill down to create a running total for each item
Excel Formula:
``=SUMIFS(C\$2:C2,B\$2:B2,B2)``
Then you could use MINIFS to extract the date from column A with
Excel Formula:
``=MINIFS(\$A\$2:\$A\$25,\$B\$2:\$B\$25,"Apple",\$D\$2:\$D\$25,">=10")``
substituting "Apple" in the formula for the cell in the result table that contains the relevant description.

#### JMata806

##### New Member
See I was looking at it harder than I should of lol I'll try this out and get back with you but just knowing its using the MINIFS is enough to know it'll work and make it easier.

#### Eric W

##### MrExcel MVP
It's not terrible without a helper column:

Book1
ABCDEFG
1DateNameTotalNameDateLimit
21/1/2021Apple1Apple1/1/202110
31/1/2021Banana2Banana1/15/2021
41/15/2021Orange5Orange4/26/2021
53/5/2021Grapes1GrapesN/A
64/26/2021Olives5Olives8/13/2021
78/13/2021Carrots3CarrotsN/A
82/10/2021Lettuce7Lettuce11/1/2021
91/6/2021Pumpkins5PumpkinsN/A
101/1/2021Apple9
111/15/2021Banana8
123/5/2021Orange2
134/26/2021Grapes5
148/13/2021Olives7
1512/10/2021Carrots0
1610/3/2021Lettuce2
171/1/2021Pumpkins3
181/15/2021Apple4
193/5/2021Banana2
204/26/2021Orange9
218/13/2021Grapes1
2212/10/2021Olives6
231/1/2022Carrots4
2411/1/2021Lettuce2
251/15/2021Pumpkins1
Sheet2
Cell Formulas
RangeFormula
E2:E9E2=UNIQUE(B2:B25)
F2:F9F2=IFERROR(SMALL(IF(SUMIFS(C\$2:C\$25,A\$2:A\$25,"<="&A\$2:A\$25,B\$2:B\$25,E2)>=G\$2,\$A\$2:\$A\$25),1),"N/A")
Dynamic array formulas.

Depending on your version of Excel, you might not have the UNIQUE function. And you might need to confirm F2 with Control+Shift+Enter.

#### JMata806

##### New Member

Let me try with a different example than, cause I think I have it nearly working with MINIFS but see if I am on the right track.
I'll test it with the Unique and IFERRORS here as well just in case, but maybe this will help explain my thought process.

Basically I and summing points =SUMPRODUCT(SUMIFS(B2:B41,C2:C41,E2:E41)), this will give me a total for only the codes that match this list.
But next I want to know based of this list where the 10th or greater point occurred than report back the Date (A) from the corresponding cell.

There are several Category lists I use but figured if I can calculate the first list than I can just transition this to the others.

You guys are amazing on that too thanks so much!

#### Attachments

• 1622066110810.png
71.4 KB · Views: 0

#### JMata806

##### New Member
Tried this with my latest concept from above and seem to have trouble where using the

=UNIQUE(C2:C41)
=IFERROR(SMALL(IF(SUMIFS(B\$2:B\$41,A\$2:A\$41,"<="&A\$2:A\$41,C\$2:C\$41,E2:E41)>=G\$2,\$A\$2:\$A\$41),1),"N/A")

Where G2 is 10

Seems like its not wanting to use the list from E2:E41 but just unique values only.

Similar here when I attempt trying to make a helper table its refusing to accept comparing the sum points of the table E2:E41 with C2:C41 till it gets to point 10 than giving date from A2:A41.

May be pushing the limits on this though I've tried using =Filter as well, but its limited to 365 I believe and I need to warrant it to compare the sums first.

Still thanks though for the ideas its really helping! Going to try the mini-sheet soon once I am home and have more free time to play with this.

#### JMata806

##### New Member
Sadly after a bit neither option worked going to try going back to the beginning and rethink my method.

Though I do want to say thanks the initial idea worked but adding the other variable threw me for a loop lol.

Replies
2
Views
67
Replies
2
Views
230
Replies
0
Views
113
Replies
7
Views
335
Replies
3
Views
96

1,136,207
Messages
5,674,415
Members
419,508
Latest member
trinstrick

### 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?

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