Now I'd like to use the same
condition but if true, add BA(i) and BA(i+1) to the sum instead of
just BA(i). HOWEVER, sometimes, consecutive days meet the
aforementioned condition so if you always add BA(i) and BA(i+1)
you will double count BA(i+1) in times where consecutive days
meet the condition.
If you simply add a 5th array $BA$6:$BA$250 to include the
second day, the result is 290.6 but it should be 87.4
Okay, I think I understand what you want to do and what the problem
is.
Unfortunately, my ability to help you is hampered by the fact that you
use XL2007 and I use XL2003. The conversion was incomplete; one
obvious error arises because you use SUMIFS, not available in XL2003.
So I certainly cannot prototype solutions without a great deal of
effort. And I am not even sure that the workbook I am looking at
survived the conversion process without loosing some integrity.
I will a take a stab at helping you. If that does not work for you, I
suggest that you contact Don Guillett. He often posts the following
response to complex problems like this. I don't know how he would
feel about you contacting him out of the blue.
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
As to your problem.... You say you want to add BA(i) and BA(i+1) if
BJ(i)<=A(i)<=BK(i) and AT(i-1)>=0.2*BB(i), but you want to avoid
adding BA(i+1) twice when BJ(i+1)<=A(i+1)<=BK(i+1) and AT(i)>=0.2*BB(i
+1).
First, it is unclear if you want to add BA(i+1) only when
BJ(i+1)<=A(i+1)<=BK(i+1). I assume that is the case.
Then perhaps the following will do the job....
=SUMPRODUCT(--($A$5:$A$249>=$BJ18),
--($A$5:$A$249<=$BK18),
--($AT$4:$AT$248>=0.2*$BB$5:$BB$249),$BA$5:$BA$249)
+ SUMPRODUCT(--($A$5:$A$249>=$BJ18),
--($A$5:$A$249<=$BK18),
--($AT$4:$AT$248>=0.2*$BB$5:$BB$249),
--($A$6:$A$250>=$BJ18),
--($A$6:$A$250<=$BK18),
--($AT$5:$AT$249<0.2*$BB$6:$BB$250),$BA$6:$BA$250)
Note: For that to work and in order to keep things "simple" (I use
that word advisedly :->), that assumes that row 250 following the data
is empty.
At this point, it is probably more efficient to use the following
array formula [*] instead:
=SUM(IF(AND($A$5:$A$249>=$BJ18,$A$5:$A$249<=$BK18,
$AT$4:$AT$248>=0.2*$BB$5:$BB$249), $BA$5:$BA$249
+ IF(AND($A$6:$A$250>=$BJ18,$A$6:$A$250<=$BK18,
$AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250)))
An array formula is entered by pressing ctrl+shift+Enter instead of
just Enter. Excel will display the formula surrounded by curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select the cell, press F2 and edit
as needed, then press ctrl+shift+Enter.
