sumifs/sumproduct

[dschneiderch]

HI,
I'm trying to add up cells in a column based on the value of the cell in the previous row of another column.

So, BA is a column of values for days april 1 to april 30.
I want to sum any value of BA where AR is less than or equal to AT of the previous row. The column AR is column BB*0.2 which I created after getting frustrated with the formula not working in hopes of simplifying

I've tried:
=SUMIFS($BA$5:$BA$34,$AT$4:$AT$33,">"&0.2*$BB$5:$BB$34

=SUMIFS($BA$5:$BA$34,$AR$5:$AR$34,"<="&$AT$4:$AT$33)

=SUMPRODUCT(AT4:AT33>=AR5:AR34,BA5:BA34)

I also tried a countif just to trouble shoot and it gives me 30 (instead of 10)
=COUNTIF(AR5:AR34,"<="&$AT$4:$AT$33)

whats wrong?
Thanks in advance
ds

 

[joeu2004]

I've tried:
=SUMIFS($BA$5:$BA$34,$AT$4:$AT$33,">"&0.2*$BB$5:$BB$34
=SUMIFS($BA$5:$BA$34,$AR$5:$AR$34,"<="&$AT$4:$AT$33)
=SUMPRODUCT(AT4:AT33>=AR5:AR34,BA5:BA34)
I also tried a countif just to trouble shoot and it gives me 30 (instead of 10)
=COUNTIF(AR5:AR34,"<="&$AT$4:$AT$33)
whats wrong?

You need to use SUMPRODUCT. The correct syntax is:

=SUMPRODUCT(--(AT4:AT33>=AR5:AR34),BA5:BA34)

or

=SUMPRODUCT(--(AT4:AT33>=0.2*BB5:BB34),BA5:BA34)

The double negation (--) converts the logical values TRUE and FALSE
into numeric values 1 and 0, which SUMPRODUCT needs. Any idempotent
arithmetic operation would suffice.

Use relative or absolute references as you please, based on what needs
to remain the same if you copy the formula down or across.

The SUMIF and COUNTIF formulas do not work because the condition must
be constant.

(Not to mention the missing right parenthesis in the first SUMIF. I
assume that is a typo in the posting, not in your worksheet. In the
future, copy-and-paste from the Formula Bar into your posting,
especially when you are asking questions about syntax.)

 

[dschneiderch]

You need to use SUMPRODUCT.  The correct syntax is:

=SUMPRODUCT(--(AT4:AT33>=AR5:AR34),BA5:BA34)

or

=SUMPRODUCT(--(AT4:AT33>=0.2*BB5:BB34),BA5:BA34)

The double negation (--) converts the logical values TRUE and FALSE
into numeric values 1 and 0, which SUMPRODUCT needs.  Any idempotent
arithmetic operation would suffice.

Use relative or absolute references as you please, based on what needs
to remain the same if you copy the formula down or across.

The SUMIF and COUNTIF formulas do not work because the condition must
be constant.

(Not to mention the missing right parenthesis in the first SUMIF.  I
assume that is a typo in the posting, not in your worksheet.  In the
future, copy-and-paste from the Formula Bar into your posting,
especially when you are asking questions about syntax.)

Thanks! Great tip to know that sum can only use constant conditions.
Better than just an answer!

I would like to expand on this formula....here is what I have:
=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)

where bj18 and bk18 are the start and end dates of the range I
need....fill the formula down to have totals for each month.

However, I'd like to compare the result of this formula to the result
if I were to add 2 days of data based on the single condition instead
of 1 day of data. At first I added a 5th array $BA$6:$BA$250 but
realized it double counts data when multiple days in a row meet my
condition.....any ideas?
Thanks in advance
Dominik

 

[joeu2004]

 I would like to expand on this formula....here is what I have:
=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)

where bj18 and bk18 are the start and end dates of the range I
need....fill the formula down to have totals for each month.

Simplifying slight will aid readability and minimize mistakes. To
wit:

=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)

However, I'd like to compare the result of this formula to the result
if I were to add 2 days of data based on the single condition instead
of 1 day of data.  At first I added a 5th array $BA$6:$BA$250 but
realized it double counts data when multiple days in a row meet my
condition.....any ideas?

Sorry, I do not understand.

What "single condition"? Where are you adding "1 day of data", much
less 2? How do you add 1 or days: by extending the start date 1 or 2
days earlier; or by extending the end date 1 or 2 days later?

How do you get "multiple days in a __row__? Your formula looks at
__columns__. Do you mean "consecutive cells in a column"? Even so,
how does that result in "double counting"? That is, how do you want
to interpret "multiple days in a row", whatever you mean by that?

Perhaps a concrete example would help. How about 10 or 12 lines of
example data that demonstrate conditions that present problems, along
with an explanation of the expected intended result v. the actual
undesired result.

PS: It is hopeless to try to format those the example data in a
Google Groups posting. GG will undoubtedly mangle the lines to the
point that they are unintelligible.

I suggest that you do one of two things.

1. Post your data in one column, clearly labeling the intended cells
that are intended to go. That is easy to copy-and-paste into an Excel
file of my own. Or

2. Upload an example Excel file to a file-sharing web site, then post
the URL in a response here. The following is a list of free file-
sharing web sites that people have suggested. I like box.net.

MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
http://www.box.net/files

 

[dschneiderch]

Simplifying slight will aid readability and minimize mistakes.  To
wit:

=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)


Sorry, I do not understand.

What "single condition"?  Where are you adding "1 day of data", much
less 2?  How do you add 1 or days:  by extending the start date 1 or 2
days earlier; or by extending the end date 1 or 2 days later?

How do you get "multiple days in a __row__?  Your formula looks at
__columns__.  Do you mean "consecutive cells in a column"?  Even so,
how does that result in "double counting"?  That is, how do you want
to interpret "multiple days in a row", whatever you mean by that?

Perhaps a concrete example would help.  How about 10 or 12 lines of
example data that demonstrate conditions that present problems, along
with an explanation of the expected intended result v. the actual
undesired result.

PS:  It is hopeless to try to format those the example data in a
Google Groups posting.  GG will undoubtedly mangle the lines to the
point that they are unintelligible.

I suggest that you do one of two things.

1. Post your data in one column, clearly labeling the intended cells
that are intended to go.  That is easy to copy-and-paste into an Excel
file of my own.  Or

2. Upload an example Excel file to a file-sharing web site, then post
the URL in a response here.  The following is a list of free file-
sharing web sites that people have suggested.  I like box.net.

MediaFire:http://www.mediafire.com
FileFactory:http://www.filefactory.com
FileSavr:http://www.filesavr.com
FileDropper:http://www.filedropper.com
RapidShare:http://www.rapidshare.comhttp://www.box.net/files

Bad choice of words. "multiple days in a __row__" I meant consecutive
days which of course means consecutive rows in a column.
Here is my spreadsheet http://dl.dropbox.com/u/5962491/FAO-PM2009.xlsx
See tab "Summary"

I've highlighted the cells in dark orange that we are working with.
cells bj17:bn20
You'll see the list of days in column A. In columns ba:bi you will
see light orange highlighting. This is conditional formatting that
highlights the condition in the first example you helped me with.
The formula that I posted above (using your help) is in BM18 and in
BS5 you will see that I manually added the appropriate cells (they
should all be highlighted via conditional formatting). So cells in
column BA are being added based on the condition that the previous
day's rain (ATi-1) was >= 0.2*BB(i). 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

You'll note that I have the solutions (tab 'Summary (2 days)' has the
manual results of the formula I am trying to figure out) because I
added them by hand but the more I play with my spreadsheet the more
changes I make. I really appreciate your time helping me to learn
the syntax on these commands because it'll help me save time in the
future.
Hope this makes sense now.
Thanks again
Dominik

 

[joeu2004]

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.

 

[joeu2004]

Errata....

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

We cannot use AND() in this context. It does not do what we intend.
Instead:

=SUM(IF(($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(($A$6:$A$250>=$BJ18)*($A$6:$A$250<=$BK18)
*($AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250)))

 

[dschneiderch]

Errata....

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

We cannot use AND() in this context.  It does not do what we intend.
Instead:

=SUM(IF(($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(($A$6:$A$250>=$BJ18)*($A$6:$A$250<=$BK18)
*($AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250)))

Both solutions do what you intended. However, they are not completely
correct in logic. e.g. It rained April 30 and the criteria at33

=0.2bb34 was true. In this case I need to include May 1 in the sum

for May so I modified the formula a bit so the 2nd if statement
contained a logic test april 30 to may 31 (instead of may 1 to may 31)
so a6:a250>=bj18 instead of >=bk19. However it still didn't give the
right result:

Output array formula:
74.6
99.4
74.5
67.6
202.8
84.3
130.2
35.1

{=SUM(IF(($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(($A$6:$A$250>=$BJ18)*($A$6:$A$250<=
$BK18) *($AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250)))}

If you have any ideas why the array didn't work I'm open to
sugggestions....
Instead, I was able to use the SUMPRODUCT formula and when I changed
the date criteria in the 2nd sumproduct, it worked!

=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>=$BK17), --($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)

Output:
74.6
100.53
74.5
67.6
202.8
84.3
130.2
35.1


I also was able to use this method for 3 day scenario.
=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>=
$BK17),--($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)+SUMPRODUCT(--($A$5:$A$249>=$BK17),--($A$5:$A
$249<=$BK18),--($AT$4:$AT$248>=0.2*$BB$5:$BB$249),--($AT$5:$AT$249<0.2*
$BB$6:$BB$250),--($A$7:$A$251>=$BJ18),--($A$7:$A$251<=$BK18),--($AT
$6:$AT$250<0.2*$BB$7:$BB$251),$BA$7:$BA$251)

This will certainly improve my workflow and I appreciate your help
very much. I can only hope to have half the understanding you do some
day. Thanks again!
Dominik

 

[joeu2004]

Both solutions do what you intended.  However, they are not completely
correct in logic.  e.g. It rained April 30 and the criteria at33>=0.2bb34
was true.  In this case I need to include May 1 in the sum

Yes. I wrote previously: "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".

With new information comes new enlightenment ;-).

so I modified the formula [...]. However it still didn't give the
right result: [....]
If you have any ideas why the array didn't work I'm open to
sugggestions....
Instead, I was able to use the SUMPRODUCT formula and when I
changed the date criteria in the 2nd sumproduct, it worked!

I think I see your mistake (inconsistency, anyway) in the SUM array
formula. But no matter: I think I was mistaken in my original
expressions. When we correct that, it should correct the problem you
have with SUM array formula.

When you say "where consecutive days meet the condition", I presume
you mean that literally. So if you have data for April 30 which meet
the condition, you only want to consider the next line if it is for
May 1, not May 2 for instance. Right?

In that case, the formulas can be simplified at the same time they are
corrected.

SUM array formula (recommended):

=SUM(IF(($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(($A$6:$A$250=$A$5:$A$249+1)
*($AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250)))

SUMPRODUCT normal non-array formula (not recommended):

=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=$A$6:$A$250+1),
--($AT$5:$AT$249<0.2*$BB$6:$BB$250),$BA$6:$BA$250)

I also was able to use this method for 3 day scenario.

I believe the SUM array formula, highly recommended at this point,
becomes:

=SUM(IF(($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(($A$6:$A$250=$A$5:$A$249+1)
*($AT$5:$AT$249<0.2*$BB$6:$BB$250), $BA$6:$BA$250
+ IF(($A$7:$A$251=$A$5:$A$249+2)
*($AT$6:$AT$250<0.2*$BB$7:$BB$251), $BA$7:$BA$251))))