Sum based on multiple conditions

[Guest]

I have Column A with a "stage" in it that will range from 1 - 8
I have column B with a "close date" in it that could be any date
I have column C with a value in it.

I need the sum of the values in C that have Close dates in any given range
(ex. 10/1/2004 through 10/31/2004), that are in any given sales stage.

I tried using SUMIF which seems to only work with 1 condition...so I can do
=SUMIF(AA,1,CC) and it will give me the sum of the values that are in stage
1. SUMIF doesn't appear to work with ranges or multiple conditions though.

 

[Bob Umlas]

=SUMPRODUCT((C1:C100>="10/1/2004")*(C1:C100<="10/31/2004")*(A1:A100=7)*(C1:C
100))
Bob Umlas
Excel MVP

I have Column A with a "stage" in it that will range from 1 - 8
I have column B with a "close date" in it that could be any date
I have column C with a value in it.

I need the sum of the values in C that have Close dates in any given range
(ex. 10/1/2004 through 10/31/2004), that are in any given sales stage.

I tried using SUMIF which seems to only work with 1 condition...so I can do
=SUMIF(AA,1,CC) and it will give me the sum of the values that are in stage
1. SUMIF doesn't appear to work with ranges or multiple conditions

though.

 

[Domenic]

Assuming that your data starts in the second row...

=SUMPRODUCT(--(A2:A100=D2),--(B2:B100>=E2),--(B2:B100<=F2),C2:C100)

....where D2 contains the stage of interest, E2 the lower date boundary,
and F2 the higher date boundary. Adjust the range accordingly.

Hope this helps!

 

[Frank Kabel]

Hi
you probably have to adapt the ranges. In what columns are your date
values, etc.

 

[Frank Kabel]

Hi
whyt is the exact formula you have used?. Note: you can use ranges such
as C:C

 

[Frank Kabel]

Hi
as said the first formula can't work as range such as E:E are not
allowed in SUMPRODUCT. It would be helpful if you could explain what is
in each column in your sheet

 

[Aladin Akyurek]

You need to coerce the date constants between the double quotes:

=SUMPRODUCT((C1:C100>="10/1/2004"+0)*(C1:C100<="10/31/2004"+0)*(A1:A100=7)*(
C1:C100))

or equivalently: switch to the comma syntax with the conditionals coerced
using double negation...

=SUMPRODUCT(--(C1:C100>="10/1/2004"+0),--(C1:C100<="10/31/2004"+0),--(A1:A10
0=7),C1:C100)