SUMIFS Using a MIN Formula

julia55

Board Regular
Joined
Dec 2, 2010
Messages
73
I am trying how to figure out if I can use a MIN function from within a SUMIFS formula. I need the formula in the second criteria to look through a list of months, and see if it is the lowest month listed (compared to C4) for that team (compared to C3) and return the value. Basically like the below, but of course the below doesn't work. Please help!

=(SUMIFS(Data1!$C3:$C100000,Data1!$A3:$A100000,$C$3,MIN(Data1!$B3:$B100000),$C$4))
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
MIN(Data1!$B3:$B100000),$C$4


is illegal inSumIfs, but it says: Yield TRUE if min of B-range equal to C4.

Data1!$B3:$B100000),$C$4 would say: Yield TRUE for every value of B-range equal to C4.

Does this clarification help?
 

julia55

Board Regular
Joined
Dec 2, 2010
Messages
73
MIN(Data1!$B3:$B100000),$C$4


is illegal inSumIfs, but it says: Yield TRUE if min of B-range equal to C4.

Data1!$B3:$B100000),$C$4 would say: Yield TRUE for every value of B-range equal to C4.

Does this clarification help?
I do get that, but I don't know a way that I can make it work. Is there something I can use other than SUMIFS? I don't want to use an array formula because that will slow down my workbook, but is there something else that can help get the results I am after?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I do get that, but I don't know a way that I can make it work. Is there something I can use other than SUMIFS? I don't want to use an array formula because that will slow down my workbook, but is there something else that can help get the results I am after?

The intention is not clear; and a formula is not a good means to convey that. Care to describe the problem in words?
 

julia55

Board Regular
Joined
Dec 2, 2010
Messages
73

ADVERTISEMENT

Sorry I tried to explain it, but I guess I really didn't go into much detail huh? I got lost in my crazy tizzy to figure out this :).

I have Months going across the top of a sheet in cells C2 through G2 (my formula would go on this sheet in cell C6-G6.) Then on another sheet there is a list of Teams with months, and a number of units for each team in each month. So there is one row for each team, each month. Some teams won't have a row for every month. So I want to return the value of the minimum month, but I want to compare the minimum month to the current month by referencing the month in cell C2 on my formula sheet mentioned in my first sentence. The team data is in rows A-C on the other sheet. Row A contains the team name, B contains them month, and C contains the value I want to return. Hope this makes more sense now, thanks again for the help1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Sorry I tried to explain it, but I guess I really didn't go into much detail huh? I got lost in my crazy tizzy to figure out this :).

I have Months going across the top of a sheet in cells C2 through G2 (my formula would go on this sheet in cell C6-G6.) Then on another sheet there is a list of Teams with months, and a number of units for each team in each month. So there is one row for each team, each month. Some teams won't have a row for every month. So I want to return the value of the minimum month, but I want to compare the minimum month to the current month by referencing the month in cell C2 on my formula sheet mentioned in my first sentence. The team data is in rows A-C on the other sheet. Row A contains the team name, B contains them month, and C contains the value I want to return. Hope this makes more sense now, thanks again for the help1

Would you post Data!A1:C5?
 

julia55

Board Regular
Joined
Dec 2, 2010
Messages
73

ADVERTISEMENT

Yes, it will probably go a little beyond C5 eventually. But for now that is right.
 

julia55

Board Regular
Joined
Dec 2, 2010
Messages
73
So this is basically what the table looks like where I am pulling the data from, this is company data I can't post the actual data, but this is basically the same.
Team Name
Month Identified
# of Sites
Team 1
1/31/13
1100
Team 1
2/28/13
1101
Team 1
3/31/13
995
Team 2
3/31/13
1600
Team 3
3/31/13
2000

<TBODY>
</TBODY>










Then the formula will go here.
FILTERS TO TEAM w/ DROP DOWN
1/31/13
2/28/13
3/31/13
other data
xxx
xxx
xxx
other data
xxx
xxx
xxx
other data
xxx
xxx
xxx
other data
xxx
xxx
xxx
other data
xxx
xxx
xxx
other data
xxx
xxx
xxx
# of completed sites
5
10
75
Under all this, is the row the formula needs to go in to always find the min month's data in the above table. based on the filtered team in the drop down

<TBODY>
</TBODY>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Let A1:C6 house the data, the headers included. Not sure where the formula must go and all those triple xxx's mean.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top