Sum Column Than Look For Cell

JMata806

New Member
Joined
May 26, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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 the roadblock.

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.

Roadblock.PNG


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
Joined
Dec 30, 2008
Messages
13,132
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 26, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,091
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
Joined
May 26, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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.

Roadblock.PNG


You guys are amazing on that too thanks so much!
 

Attachments

  • 1622066110810.png
    1622066110810.png
    71.4 KB · Views: 0

JMata806

New Member
Joined
May 26, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
Joined
May 26, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

Forum statistics

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

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
Top