Formula to Find the Cell Address of a Different Value in an Array after a Specific Occurre

[jmca2000]

Formula to Find the Cell Address of a Different Value in an Array after a Specific Occurrence

PLEASE Help...

What I am trying to do is Sum the values in a column between two cell locations from another column

I can find the first starting Address for the summation, but I can not figure out how to determine what the ending address is?

I want to sum up the quantities for each PART# based on its location horizontally and stock availability (Please see Photos).

I use a unique Array in each column, such as in column AE:

{=IF(
AD8<>"PART#","",
SUM(INDIRECT(ADDRESS(SMALL(IF(D2:D$100="Americas Stock",ROW(D2:D$100),ROW(D$100)),1),3)):INDIRECT(ADDRESS(SMALL(IF(D2:D$100<>"",ROW(D2:D$100)),2),3))))} <--- *** WRONG (Should be the next non blank cell address after "Americas Stock", which is "PART#" in D12)***

This formula does not work because I can not figure out what the next Non Blank Cell address is after "Americas Stock", which in this example should be the address of the "PART#" in D12?

I have also tried using this Array, but still can not figure out how to select the proper next address occurrence.

{=IF(
AD2<>"PART#","",
SMALL(IF(AD2:AD$250={"PART#","Americas Stock","Europe Stock","Asia Stock","Americas Broker Stock","Europe Broker Stock","Asia Broker Stock"},ROW(AD2:AD$250),ROW(AD$250)),3))}

Thank You in Advance for your Assistance.

ComponentCalculator_EXAMPLE1.jpg
ComponentCalculator_EXAMPLE2.jpg