# SUMIFS Using a MIN Formula

#### julia55

##### Board Regular
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?

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?

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?

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

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?

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

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?

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>

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

Replies
1
Views
129
Replies
12
Views
348
Replies
4
Views
238
Replies
15
Views
494
Replies
6
Views
270

1,218,945
Messages
6,145,359
Members
450,611
Latest member
JodiWe

### 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.

### Which adblocker are you using?

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

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