Another Scheduling formula issue part 2

[mdean32]

My current formula for "RODS AVAILABLE" is =IFERROR(G2-SUMIF(E$2:E2,E2,D$2:D2),"").

I have the table sorted exactly how I want to print it with the "Line" sorted first, followed by the "Run Date" in order. However, my formula for "RODS AVAILABLE" isn't correct by the Run Date needed. The "RODS AVAILABLE" function calculates by the line order instead of by the Run Date. How do I calculate "RODS AVAILABLE" by the Run Date, while still keeping the "Line" in the below order?

I am aware I can sort my table by Run Date and get the RODS AVAILABLE formula to calculate correctly, but I need my lines grouped together like the example below. Thank you for any help you can offer


A B C D E F G

Line	Run Date	SEQ	ORDER AMT	ROD	RODS AVAILABLE	ROD INVENTORY
13	1/21	30	500	1166H62A07250	2700	3200
13	1/22	50	500	1166H62A08000	1300	1800
13	1/23	55	500	1166H62A10000	700	1200
13	1/24	60	500	1166H62A10000	200	1200
14	1/21	10	500	1166H62A10000	-300	1200
14	1/22	25	500	1166H62A07250	2200	3200
14	1/23	15	500	1166H62A07250	1700	3200
14	1/24	75	500	1166H62A08000	800	1800
15	1/21	40	500	1166H62A08000	300	1800
15	1/22	30	500	1166H62A10000	-800	1200
15	1/23	5	500	1166H62A08000	-200	1800
15	1/24	45	500	1166H62A07250	1200	3200

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

 

[mdean32]

Any ideas? Maybe take it out of table format?

 

[mdean32]

Can any Excel experts tell me if this is even doable? I've searched all over the internet looking for a solution. Thanks

 

[mail2master]

Not sure if I am getting you right.. But if you are asking for a solution like below mentioned table, then it is possible:

Run Date	Line	SEQ	Order Amount
1/21	13	30	500
	14	10	500
	15	40	500
1/22	13	50	500
	14	25	500
	15	30	500

<tbody>
</tbody>

Please let me know we are on same platform..
In other case, if you are trying to sort the table with respect to 2 columns at the same time, I am afraid that's not doable as far as I know.

 

[mdean32]

Right now the formula is subtracting straight down the spreadsheet in the Rods Available column. For example, the spreadsheet is subtracting the 1166h62a10000 rods on dates 1/23 and 1/24 on line 13 before it's subtracting the 1166h62a10000 rods on 1/21 on line 14. I need all the lines to be grouped together like I have it laid out, but I need the Rods Available formula to subtract like values by date. Am I making sense? I can sort by date and everything is right (like your example), but the lines are not grouped together like my example.

 

[jtakw]

Hi,

Got your Tag, if I understand correctly, adjust the formula as follows:

Book1
	A	B	C	D	E	F	G
1	Line	Run Date	SEQ	ORDER AMT	ROD	RODS AVAILABLE	ROD INVENTORY
2	13	1/21/2019	30	500	1166H62A07250	2700	3200
3	13	1/22/2019	50	500	1166H62A08000	800	1800
4	13	1/23/2019	55	500	1166H62A10000	-300	1200
5	13	1/24/2019	60	500	1166H62A10000	-800	1200
6	14	1/21/2019	10	500	1166H62A10000	700	1200
7	14	1/22/2019	25	500	1166H62A07250	2200	3200
8	14	1/23/2019	15	500	1166H62A07250	1700	3200
9	14	1/24/2019	75	500	1166H62A08000	-200	1800
10	15	1/21/2019	40	500	1166H62A08000	1300	1800
11	15	1/22/2019	30	500	1166H62A10000	200	1200
12	15	1/23/2019	5	500	1166H62A08000	300	1800
13	15	1/24/2019	45	500	1166H62A07250	1200	3200
Sheet478

Cell Formulas
Range	Formula
F2	=IFERROR(G2-SUMIFS(D$2:D$13,E$2:E$13,E2,B$2:B$13,"<="&B2),"")

Formula copied down.

 

[mdean32]

Thank you so much for all of your help! You have been amazing!

 

[jtakw]

You're welcome, thanks for the feedback.