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

 

[stevekho]

Thanks a lot T!!

 

[T. Valko]

Thanks a lot T!!

You're welcome!

 

[stevekho]

It is not working perfectly

Let’s assume I have


1-Oct-11..31-Oct-11..269,575..Loc_A

1-Dec-11..31-Dec-11..1,600..Loc_A

1-Dec-11..29-Feb-12..1,161..Loc_A

1-Dec-11..29-Feb-12..16,281..Loc_A

1-Dec-11..29-Feb-12..27,551..Loc_A

1-Dec-11..29-Feb-12..31,652..Loc_A

1-Dec-11..29-Feb-12..164,544..Loc_A

1-Jan-12..29-Feb-12..8,440..Loc_A

1-Jan-12..29-Feb-12..10,854


And my start date is Jan 1st 2012 (Cells AE) and End Date is Feb 1st 2012 (Cells AF) (Not Included), it doesn’t return the right number.

I used

=
SUMIFS($E$16:$E$200,$C$16:$C$200,">="&AE16,$C$16:$C$200,"<"&AF16,$F$16:$F$200,$Y$39)+
SUMIFS($E$16:$E$200,$C$16:$C$200,"<"&AE16,$D$16:$D$200,">="&(AF16-1),K$16:K$200,$Y$39)

It should consider all of the one between December and February but it is not

Thanks for your help

Steve

 

[T. Valko]

It is not working perfectly

Let’s assume I have


1-Oct-11..31-Oct-11..269,575..Loc_A

1-Dec-11..31-Dec-11..1,600..Loc_A

1-Dec-11..29-Feb-12..1,161..Loc_A

1-Dec-11..29-Feb-12..16,281..Loc_A

1-Dec-11..29-Feb-12..27,551..Loc_A

1-Dec-11..29-Feb-12..31,652..Loc_A

1-Dec-11..29-Feb-12..164,544..Loc_A

1-Jan-12..29-Feb-12..8,440..Loc_A

1-Jan-12..29-Feb-12..10,854


And my start date is Jan 1st 2012 (Cells AE) and End Date is Feb 1st 2012 (Cells AF) (Not Included), it doesn’t return the right number.

I used

=
SUMIFS($E$16:$E$200,$C$16:$C$200,">="&AE16,$C$16:$C$200,"<"&AF16,$F$16:$F$200,$Y$39)+
SUMIFS($E$16:$E$200,$C$16:$C$200,"<"&AE16,$D$16:$D$200,">="&(AF16-1),K$16:K$200,$Y$39)

It should consider all of the one between December and February but it is not

Thanks for your help

Steve

Here is your sample data:

Book1
	A	B	C	D	E	F
2	10/1/2011	10/31/2011	269,575	Loc_A	1/1/2012	2/1/2012
3	12/1/2011	12/31/2011	1,600	Loc_A	_	_
4	12/1/2011	2/29/2012	1,161	Loc_A	_	_
5	12/1/2011	2/29/2012	16,281	Loc_A	_	_
6	12/1/2011	2/29/2012	27,551	Loc_A	_	_
7	12/1/2011	2/29/2012	31,652	Loc_A	_	_
8	12/1/2011	2/29/2012	164,544	Loc_A	_	_
9	1/1/2012	2/29/2012	8,440	Loc_A	_	_
10	1/1/2012	2/29/2012	10,854	_	_	_
Sheet1

Let's assume you want the sum for Loc_A.

What result do you expect?

Based on the date criteria in E2:F2 there are NO dates in columns A and B that fall within this date range so the sum will be 0.

 

[cgcamal]

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

Hi stevekho,

An adaptation of array formula given post #3, hoping work for you:

Excel Workbook
	A	B	C	D	E
1	Start Date	End Date	A Number A	location	Result
2	05/may/2011	06/may/2011	4	Loc_A	3
3	20/jun/2011	02/jul/2011	5	Loc_B
4	01/jul/2011	08/jul/2011	1	Loc_B
5	05/jul/2011	12/jul/2011	3	Loc_C
6	13/jul/2011	15/jul/2011	2	Loc_B
7	14/jul/2011	16/jul/2011	6	Loc_B
8	15/jul/2011	03/ago/2011	3	Loc_D
...

#VALUE!

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

Regards.

 

[stevekho]

Not working

At T Valko,

within Jan 1st 2012 and Feb 1 2012 (Not Included), we have these values:

1-Dec-11..29-Feb-12..1,161..Loc_A

1-Dec-11..29-Feb-12..16,281..Loc_A

1-Dec-11..29-Feb-12..27,551..Loc_A

1-Dec-11..29-Feb-12..31,652..Loc_A

1-Dec-11..29-Feb-12..164,544..Loc_A

1-Jan-12..29-Feb-12..8,440..Loc_A

1-Jan-12..29-Feb-12..10,854

-----------------------------------------------------

For Instance, when I write:

1-Dec-11..29-Feb-12..1,161..Loc_A

It means that from December 1st to the end of Feb 2012
, I will have on a daily basis, a delivery of 1161. ( During 3 Months)

So When I want to know the deliveries between Jan and Feb, I want to know all the one what will actually deliver something in that range.. Hope it is amking sense

 

[cgcamal]

Not working

Why the last formula doesn't work?

You want the sum of values between 2 especific dates for a especific location? or I missed something?

May you explain me a little bit.

Regards

 

[stevekho]

Well let’s assume I want the sum between July 1st 2011 and Aug 15th 2011.

The Column A contains the start of my contract, the column B contains the end date of that contract. From the start date to the end date, I will get a delivery on a daily basis.

Hence for instance from April 1st to October 31st 2011, I will get a delivery of 47,748 potatoes on a daily basis.

Hence within the range [ July 1st , Aug 15th 2011], I will go through the range April 1st to October 31st 2011 which contains the range [ July 1st , Aug 15th 2011]. Hence from July 1st to Aug 15th 2011, I will get a 47,748 delivery.

This is basically the logic, If I have several range that contains my input range, I need to consider them for the Sum.

Hope it is clear.

Steve

 

[T. Valko]

Not working

At T Valko,

within Jan 1st 2012 and Feb 1 2012 (Not Included), we have these values:

1-Dec-11..29-Feb-12..1,161..Loc_A

1-Dec-11..29-Feb-12..16,281..Loc_A

1-Dec-11..29-Feb-12..27,551..Loc_A

1-Dec-11..29-Feb-12..31,652..Loc_A

1-Dec-11..29-Feb-12..164,544..Loc_A

1-Jan-12..29-Feb-12..8,440..Loc_A

1-Jan-12..29-Feb-12..10,854

-----------------------------------------------------

For Instance, when I write:

1-Dec-11..29-Feb-12..1,161..Loc_A

It means that from December 1st to the end of Feb 2012
, I will have on a daily basis, a delivery of 1161. ( During 3 Months)

So When I want to know the deliveries between Jan and Feb, I want to know all the one what will actually deliver something in that range.. Hope it is amking sense

Ok, I think I understand now.

Instead of defining the date range as 1/1/2012 and 2/1/2012 why don't you define the actual range that you're interested in: 1/1/2012 and 1/31/2012.

Try this:

=SUMIFS(C2:C10,A2:A10,"<="&E2,B2:B10,">="&F2,D2:D10,"Loc_A")

 

[stevekho]

Guys it is working now!

Thanks a lot, the formula I posted initially was right but I didnt see that at the end of my table (Row 200), I also had some Loc_A...

Thanks a lot for your time and patience guys. I do really appreciate.

Have a nice Summer!