Multiple Sum If

[stevekho]

Hello Guys,

So here is my probem

I have a table with

A | B | C | D

Start Date | End Date | A Number A | location

.
.
.


I have a month range -> July 2011 and August 2011

I want to sum all the values between this range (Column C) for a specific location.


Thanks for your help!

Steve

 

[T. Valko]

Hello Guys,

So here is my probem

I have a table with

A | B | C | D

Start Date | End Date | A Number A | location

.
.
.


I have a month range -> July 2011 and August 2011

I want to sum all the values between this range (Column C) for a specific location.


Thanks for your help!

Steve

What version of Excel are you using?

 

[cgcamal]

Hi stevekho,

If I understand, try with the following array formula:

Excel Workbook
	A	B	C	D	E
1	Start Date	End Date	A Number A	location	Result
2	05/05/2011	06/05/2011	4	Loc_A	8
3	10/07/2011	15/08/2011	5	Loc_B
4	11/07/2011	16/08/2011	1	Loc_C
5	12/07/2011	17/08/2011	1	Loc_B
6	13/07/2011	18/08/2011	2	Loc_B
7	14/07/2011	19/08/2011	6	Loc_D
8	12/08/2011	20/08/2011	2	Loc_B
(

#VALUE!

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Hope this helps,

Regards.

 

[Biz]

Try

=SUMPRODUCT((MONTH($A$2:$A$8)=7)*(MONTH($B$2:$B$8=8)*($D$2:$D$8="Loc_B")*($C$2:$C$8)))

Biz

 

[stevekho]

Whoaaa thanks guys it helps a lot!

Now if I just want to now the sum betwenn 2 date range?

Like betweek July 1st 09 and July 15th 09, is there a way??

Thanks 10000 Times

 

[T. Valko]

Now if I just want to now the sum betwenn 2 date range?

Like betweek July 1st 09 and July 15th 09, is there a way??

What version of Excel are you using?

What version of Excel are you using?

 

[stevekho]

Hello

Excel 07

Thanks again

 

[T. Valko]

Hello

Excel 07

Thanks again

Try this...

Use cells to hold the criteria:

• F1 = lower date boundary = 7/1/2009
• G1 = upper date boundary = 7/15/2009
• H1 = Loc_B

Then:

=SUMIFS(C2:C8,A2:A8,">="&F1,A2:A8,"<="&G1,D2:D8,H1)

 

[stevekho]

Thanks a lot!! It worked However, there is a small problem, but I will try to figure out a way to fix it :/

If we have

• F1 = lower date boundary = 7/1/2009
• G1 = upper date boundary = 7/15/2009

And for instance

Values for

7/1/2009.....7/15/2009....8....A
7/1/2009.....7/20/2009....10....A

And I enter

• F1 = lower date boundary = 7/1/2009
• G1 = upper date boundary = 7/20/2009

It is going to return 18. I will try to ask the user to be careful because of that limitations.

Thanks a lot for your precious help

 

[T. Valko]

Thanks a lot!! It worked However, there is a small problem, but I will try to figure out a way to fix it :/

If we have

• F1 = lower date boundary = 7/1/2009
• G1 = upper date boundary = 7/15/2009

And for instance

Values for

7/1/2009.....7/15/2009....8....A
7/1/2009.....7/20/2009....10....A

And I enter

• F1 = lower date boundary = 7/1/2009
• G1 = upper date boundary = 7/20/2009

It is going to return 18. I will try to ask the user to be careful because of that limitations.

Thanks a lot for your precious help

Ok, I see that you have 2 date columns. I thought there was just a single date column.

Just refer to the 2nd date column:

=SUMIFS(C2:C8,A2:A8,">="&F1,B2:B8,"<="&G1,D2:D8,H1)