SUMIFS Using a MIN Formula

[julia55]

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))

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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

 

[Aladin Akyurek]

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

Care to post a copy of that range here if possible?

 

[julia55]

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.
[TABLE="width: 500, align: left"]
<TBODY>[TR]
[TD]Team Name
[/TD]
[TD]Month Identified
[/TD]
[TD]# of Sites
[/TD]
[/TR]
[TR]
[TD]Team 1
[/TD]
[TD]1/31/13
[/TD]
[TD]1100
[/TD]
[/TR]
[TR]
[TD]Team 1
[/TD]
[TD]2/28/13
[/TD]
[TD]1101
[/TD]
[/TR]
[TR]
[TD]Team 1
[/TD]
[TD]3/31/13
[/TD]
[TD]995
[/TD]
[/TR]
[TR]
[TD]Team 2
[/TD]
[TD]3/31/13
[/TD]
[TD]1600
[/TD]
[/TR]
[TR]
[TD]Team 3
[/TD]
[TD]3/31/13
[/TD]
[TD]2000
[/TD]
[/TR]
</TBODY>[/TABLE]










Then the formula will go here.
[TABLE="width: 500, align: left"]
<TBODY>[TR]
[TD]FILTERS TO TEAM w/ DROP DOWN
[/TD]
[TD]1/31/13
[/TD]
[TD]2/28/13
[/TD]
[TD]3/31/13
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]other data
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]# of completed sites
[/TD]
[TD]5
[/TD]
[TD]10
[/TD]
[TD]75
[/TD]
[/TR]
[TR]
[TD]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
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Aladin Akyurek]

Let A1:C6 house the data, the headers included. Not sure where the formula must go and all those triple xxx's mean.