Sumif formula
 

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.

Sumif formula
 

One way...

B13 = lower date boundary
B14 = upper date boundary

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

--
Biff
Microsoft Excel MVP


"Ron0210" wrote in message
...
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.

Sumif formula
 

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


"Ron0210" wrote:

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.

Sumif formula
 

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

--
Biff
Microsoft Excel MVP


"DMcDConsult" wrote in message
...
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


"Ron0210" wrote:

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.

Sumif formula
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=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)

--
Biff
Microsoft Excel MVP


"DMcDConsult" wrote in message
...
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


"Ron0210" wrote:

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.