# Sum of min of several conditionally matching rows

#### kjaget

##### New Member
My problem : I have data set up as key1, key2, data1, data2, data3, ..., dataN

Key1 is unique, there can be several instances of the same key2. At the top of the sheet as a summary, I want to have a table of key1, key2, <thing>. <thing> is the sum of the minimum data entry from each matching. That is, the <thing> for key1 = A, key2 = B with this :

 Key1 Key2 Data1 Data2 Data3 A B 1 12 3 A B 7 5 11 A B 10 9 16

<tbody>
</tbody>

Would be 1 + 5 + 9 = 15. There's no pattern expected on which is the min in each row.

I have this working by creating a helper column that's just the min of each row and use either sumif, a CSE if statement or whatever to select from that min column. It works, but it's a bit error prone since it requires users not to mess this column up.

What I can't seem to figure out is how to do it without that helper row. I either seem to get the minimum of all matching key1, key2 data rows or I end up causing errors in array syntax trying to grab the min of each matching row. Basically I'm having problems combining min, sum and if to get the array syntax right.

VBA would probably be more trouble that it is worth, considering I have a working solution using a helper column. But at least if that's the answer I can stop banging my head against the wall...

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hello and welcome:
=SUMPRODUCT(SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,3)))

Hello and welcome:
=SUMPRODUCT(SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,3)))

Are we not missing the surrounding conditions?

Either...

=SUMPRODUCT(--(A2:A4=H1),--(B2:B4=H2),SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,3)))

Or...

=SUMPRODUCT(--(A2:A4=H1),--(B2:B4=H2),SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,COLUMNS(C2:C4))))

where H1 = A and H2 = B.

Are we not missing the surrounding conditions?

Either...

=SUMPRODUCT(--(A2:A4=H1),--(B2:B4=H2),SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,3)))

Or...

=SUMPRODUCT(--(A2:A4=H1),--(B2:B4=H2),SUBTOTAL(105,OFFSET(C2,ROW(A2:A4)-ROW(A2),0,1,COLUMNS(C2:C4))))

where H1 = A and H2 = B.

I was under the impression that we are taking every row.
But you could be right Aladin.

Awesome - subtotal() was the piece I was missing. I have some reading to do since that seems like a function ripe for abuse.

Thanks a bunch for the help!

Replies
6
Views
351
Replies
16
Views
250
Replies
5
Views
213
Replies
8
Views
351
Replies
5
Views
185

1,206,969
Messages
6,075,918
Members
446,169
Latest member
luckyfind4u

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