# Need Help to SUM First NON_BLANK Cell

#### HotNumbers

##### Well-known Member
Need Help to Sum 12 columns from the first non blank cells that appear only after a certain criteria is meet. The Data columns are from Column G:AP. The Criteria that I need are in Columns BD and BE. The formula will be in BF. Here is what I need

If BD is Blank and BE is (Burbank or ESCO) then I need it to sum 12 columns from the first non Blank Cell if There is data in BD and BE then I want

IF(ISERROR(SUM(INDEX(G96:BC96,MATCH(BD96,NC_FY_PERIODS,0)-3):INDEX(G96:BC96,0,MIN(MATCH(BD96,NC_FY_PERIODS,0)+14)))),"",SUM(INDEX(G96:BC96,MATCH(BD96,NC_FY_PERIODS,0)-3):INDEX(G96:BC96,0,MIN(MATCH(BD96,NC_FY_PERIODS,0)+14))))

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
please post a screen shot or example.

Because of posting limitations I had to cut some of the data. In this case the formula needs to be in J
Book3
ABCDEFGHIJ
1Apr FY06May FY06Jun FY06Jul FY06Aug FY06Sep FY06Combo TagGU 1st Unit ShippedTerritoryGU 12 Periods Total
2Burbank
3-14 EA-10 EA-3 EA-7 EA-3 EA-15 EAJul FY04France-1,287
4Esco
5-2 EA-1 EA0 EA-1 EA0 EA-2 EAJul FY04France-262
6Dec FY05Europe Licensees-4
Sheet1

Unfortunately, I still don't understand what it is you're looking for. Can you elaborate, preferably with an example? Also, do mean first non-zero value as oppose to non-blank? And is it possible to get rid of 'EA' for each of the cells so that you're left with only a number?

Thought I through to clearify So I am repositng my example with more detail
Book4
ABCDEFGHIJKLMNOPQRSTUV
1Apr FY05May FY05Jun FY05Jul FY05Aug FY05Sep FY05Oct FY06Nov FY06Dec FY06Jan FY06Feb FY06Mar FY06Apr FY06May FY06Jun FY06Jul FY06Aug FY06Sep FY06Combo TagGU 1st Unit ShippedTerritoryGU 12 Periods Total
2-1 EA-6 EA-5 EA-4 EA0 EA2 EA4 EA7 EA9 EA12 EA14 EABurbank
3-1 EA-15 EA-8 EA-1 EA-2 EA-17 EA-58 EA-44 EA-3 EAJul FY04France
4-58 EA-44 EA-3 EA0 EA-1 EA0 EA0 EAEsco
50 EA0 EA-5 EA-4 EA0 EA-7 EA-7 EA-5 EA0 EA-1 EA0 EA0 EAJul FY04UK
Sheet1

Burbank As you can is the first Entery is in D, so from D I need to SUM 12 Columns from that point
Esco The first enter is in Column F so from hat point I need to sum 12 columns from the point
However I need to make sure the T is Blank and U either says Burbank or Esco for the calculations to take Place

Now it's clear. Just one other quick question. If we use G2:BC2 as an example, what happens if the first non-blank is located at AS2? Do you want to sum the eleven columns, AS2:BC2? If not, what do you want to happen?

The original working file has 48 columns with only the first 36 columns being used. So if I have a number at the 36 columns it will still calculate. I am using this formula to calculate Actuls for Movie Titles. Becuase Each movie will be released in different time frames I need to be able to calculate from the first time an entery is made for Burbank or ESCO. I have my other formula to calculate the other territories.
The Data columns are from Column G:AP. The Criteria that I need are in Columns BD and BE. The formula will be in BF. Here is what I need

Thanks for all you help and specialy for not giving up on help me.

In that case, I think the following would suffice...

=IF((BD2="")*(OR(BE2={"Burbank","Esco"})),SUM(INDEX(G2:BB2,MATCH(TRUE,INDEX(G2:BB2<>"",0),0)):INDEX(G2:BB2,MATCH(TRUE,INDEX(G2:BB2<>"",0),0)+11)),"")

Hope this helps!

I forgot to address the fact that your cells contain the text 'EA'. If you can do without including it with the numbers, get rid of them using FIND/REPLACE. If, however, you'd like to keep them, try the following...

1) First, get rid of them altogether using FIND/REPLACE.

2) Then, custom format those cells as follows...

0" EA"

Hope this helps!

Domenic

Thanks for the formula it seems to work. As for the EA that is only a Cell formating that doesn't effect the results. EA is for each.

Thanks

Replies
3
Views
543
Replies
9
Views
1K
Replies
2
Views
171
Replies
4
Views
235
Replies
0
Views
94

Threads
1,211,853
Messages
6,104,371
Members
447,902
Latest member
chriswebs23

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

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