Sum of min of several conditionally matching rows

kjaget

New Member
Joined
Mar 26, 2013
Messages
2
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
Data1Data2
Data3
A
B
1
123
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)))

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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top