Need Help to SUM First NON_BLANK Cell

HotNumbers

Well-known Member
Joined
Feb 14, 2005
Messages
732
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top