Sumif formula

R

Ron0210

How do you write a sumif formula using a range for the criteria. For example:
=sumif(a20:a1000,b13:b14,c20:c1000) which does not seem to work. My
spreadsheet has a series of dates in column a with corresponding values
column c and I want to sum column c for a particular range of dates defined
by b13 and b14. A typical range would be a month. My spreadsheet will have
dates and values and I want to determine sums for certain months.
 
T

T. Valko

One way...

B13 = lower date boundary
B14 = upper date boundary

=SUMPRODUCT(--(A20:A1000>=B13),--(A20:A1000<=B14),C20:C1000)
 
D

DMcDConsult

If you want to sum based on a date range:

b13 = lower boundary date
b14 = upper boundary date

=SUMIF(a20:a1000,">="&b13,c20:c1000)-SUMIF(a20:a1000,"=<"&b14,c20:c1000)

Note that the boundary dates are included in the sum.
Format as General or Number

Credit to: --
Biff
Microsoft Excel MVP
 
T

T. Valko

=SUMIF(a20:a1000,">="&b13,c20:c1000)-SUMIF(a20:a1000,"=<"&b14,c20:c1000)

The comparison operator in the 2nd SUMIF should be "greater than":

=SUMIF(A20:A1000,">="&B13,C20:C1000)-SUMIF(A20:A1000,">"&B14,C20:C1000)
 
T

T. Valko

The comparison operator in the 2nd SUMIF should be "greater than".

Either will work but the "greater than" comparison seems more intuitive (at
least, to me!).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top