SUMIFS and MAX

[thart21]

Just need some final validation that you cannot use the MAX function with SUMIFS, can't find any examples and thought I read somewhere that it can't be done, but can't remember the site.

I have a sheet with my data and need to use SUMIFS, totalling one column based on 4 different criteria, one being the MAX of a date range. To explain that further, I have a column "DailyDate" and "WEEK No" & I need to sum the my totals column based on the MAX DailyDate within the WEEK No that is selected. I know I can do this with INDEX MATCH if I already have my totals, but need to use the SUMIFS on this one.

Month = August
WeekNo DailyDate(MAX)
1 8/5/2011 Just total the column for WeekNo 1 and 8/5 in DailyDate
2 8/12/2011
3 8/19/2011

Thanks for any help.

Toni

 

[T. Valko]

Just need some final validation that you cannot use the MAX function with SUMIFS, can't find any examples and thought I read somewhere that it can't be done, but can't remember the site.

I have a sheet with my data and need to use SUMIFS, totalling one column based on 4 different criteria, one being the MAX of a date range. To explain that further, I have a column "DailyDate" and "WEEK No" & I need to sum the my totals column based on the MAX DailyDate within the WEEK No that is selected. I know I can do this with INDEX MATCH if I already have my totals, but need to use the SUMIFS on this one.

Month = August
WeekNo DailyDate(MAX)
1 8/5/2011 Just total the column for WeekNo 1 and 8/5 in DailyDate
2 8/12/2011
3 8/19/2011

Thanks for any help.

Toni

You can use MAX with SUMIFS.

Did you try it and it didn't work?

I'm having trouble trying to understand your layout and what it is you're wanting to sum.

If possible, posting some sample data might help.

 

[thart21]

Thanks for the reply, below is my current formula:

=IFERROR(SUMIFS(PGI_Data[Open Qty],PGI_Data[Div],$M$1,PGI_Data[CCD Bus Mo Yr],$N$1,PGI_Data[SO Plan Gds Iss Dt],">"&DATE(YEAR($P$1),MONTH($P$1),0),PGI_Data[SO Plan Gds Iss Dt],"<="&DATE(YEAR($P$1),MONTH($P$1)+1,0),PGI_Data[RptgMonth],$P$1,PGI_Data[WeekNo],F9),"-")

Now it is summing all the "Open Qty" for the selected WeekNo but, what I need it to do is just sum "Opent Qty" for the most current day that falls within that WeekNo, so the MAX day in WeekNo "WEEK 1".
Example:
WeekNo selected is "WEEK 1" of August and has DailyDates of 8/1,8/2,8/3,8/4,8/5 - I just want the total for 8/5, not the cumulative total of 8/1 through 8/5.

Excel Workbook
	A	B	C
1	WeekNo	DailyDate	OpenQty
2	WEEK 1	8/1/2011	5000
3	WEEK 1	8/1/2011	4500
4	WEEK 1	8/2/2011	300
5	WEEK 1	8/2/2011	5000
6	WEEK 1	8/3/2011	500
7	WEEK 1	8/3/2011	250
8	WEEK 1	8/4/2011	50
9	WEEK 1	8/4/2011	500
10	WEEK 1	8/5/2011	4000
11	WEEK 1	8/5/2011	2500
Sheet1

Excel 2007

I need to see a total of 6500 for a DailyDate of 8/5/2011, but am getting 22,600 for all days. Hopefully I have made sense here, thanks!

 

[T. Valko]

Thanks for the reply, below is my current formula:

=IFERROR(SUMIFS(PGI_Data[Open Qty],PGI_Data[Div],$M$1,PGI_Data[CCD Bus Mo Yr],$N$1,PGI_Data[SO Plan Gds Iss Dt],">"&DATE(YEAR($P$1),MONTH($P$1),0),PGI_Data[SO Plan Gds Iss Dt],"<="&DATE(YEAR($P$1),MONTH($P$1)+1,0),PGI_Data[RptgMonth],$P$1,PGI_Data[WeekNo],F9),"-")

Now it is summing all the "Open Qty" for the selected WeekNo but, what I need it to do is just sum "Opent Qty" for the most current day that falls within that WeekNo, so the MAX day in WeekNo "WEEK 1".
Example:
WeekNo selected is "WEEK 1" of August and has DailyDates of 8/1,8/2,8/3,8/4,8/5 - I just want the total for 8/5, not the cumulative total of 8/1 through 8/5.

Excel Workbook
	A	B	C
1	WeekNo	DailyDate	OpenQty
2	WEEK 1	8/1/2011	5000
3	WEEK 1	8/1/2011	4500
4	WEEK 1	8/2/2011	300
5	WEEK 1	8/2/2011	5000
6	WEEK 1	8/3/2011	500
7	WEEK 1	8/3/2011	250
8	WEEK 1	8/4/2011	50
9	WEEK 1	8/4/2011	500
10	WEEK 1	8/5/2011	4000
11	WEEK 1	8/5/2011	2500
Sheet1

Excel 2007



I need to see a total of 6500 for a DailyDate of 8/5/2011, but am getting 22,600 for all days. Hopefully I have made sense here, thanks!

Ok, I NEVER use that structured referencing but for your application you won't be able to use the SUMIFS function.

You can use an array SUM formula.

Based on just the sample data provided it would be like this using range addresses:

=SUM(IF(A2:A11="week 1",IF(B2:B11=MAX(IF(A2:A11="week 1",B2:B11)),C2:C11)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[thart21]

Thanks Biff,

Are you saying that, in order to use your formula, I have to restructure my formula to using range addresses? Unfortunately, I will be working with thousands of lines and up to 5 WeekNo's. and have found that my worksheet drags considerably when doing this. I believe my workaround will have to be to create a "weekly" query in Access (where the data currently resides) and base my SUMIFS on that.

Thanks for confirming for me, I appreciate your time.

Toni

 

[T. Valko]

Thanks Biff,

Are you saying that, in order to use your formula, I have to restructure my formula to using range addresses? Unfortunately, I will be working with thousands of lines and up to 5 WeekNo's. and have found that my worksheet drags considerably when doing this. I believe my workaround will have to be to create a "weekly" query in Access (where the data currently resides) and base my SUMIFS on that.

Thanks for confirming for me, I appreciate your time.

Toni

No.

You should be able to convert it to structured references.

I NEVER use structured references so I'm not real proficient in reading all that structured referencing "gobbledygook".