Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Formula | Excel Worksheet Functions | |||
Formula Help -- SUMIF | Excel Discussion (Misc queries) | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
Is there a MAXIF formula similar to the SUMIF formula? | Excel Discussion (Misc queries) |