Need Help to SUM First NON_BLANK Cell

HotNumbers

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

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

HotNumbers

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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?
 

HotNumbers

Well-known Member
Joined
Feb 14, 2005
Messages
727

ADVERTISEMENT

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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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?
 

HotNumbers

Well-known Member
Joined
Feb 14, 2005
Messages
727

ADVERTISEMENT

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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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!
 

HotNumbers

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

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,147
Members
412,306
Latest member
fabio6
Top