Sum Visible Cells with Arrays

billpq

Board Regular
Joined
Oct 19, 2004
Messages
102
I've read numerous threads and tried several examples, but can't get this to work.

I have a Sheet called Data with columns A,B,C containing text and Column D with a value. There are entries for about 1000 rows. On a separate sheet (Sheet2), I want to get the sum of Column D on Data if the contents of Columns A,B,C of Data match my Columns A,B,C on Sheet2. So I have this formula, entered as an array in cell D3 of Sheet2:

{=SUM(IF(A3&B3&C3=Data!A3:A1000&Data!B3:B1000&Data!C3:C1000,Data!D3:D1000,0))}

This works fine, except when I filter it, the result does not change. Can someone please help me "convert" the above formula so that it will only sum displayed Data (ie filtered)?

I've played with Subtotal, and Sumproduct examples, but could not get to work.

Thank you!!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
Try:

=SUMPRODUCT((Data!A3:A1000=A3)+0,(Data!B3:B1000=B3)+0,(Data!C3:C1000=C5)+0,SUBTOTAL(109,OFFSET(Data!D3,ROW(Data!D3:D1000)-ROW(Data!D3),0)))
 

billpq

Board Regular
Joined
Oct 19, 2004
Messages
102
Red -

You nailed it. I am very grateful! Thank you!

I would love to learn something from this, but quite honestly I don't understand how/why this works. If you can relatively easily explain the equation's logic, I would really appreciate. That being said, it works and I'm thrilled, so if that's a pain or too much to ask I completely understand and will try to dissect on my own.

Again, thanks for your time and fast response.

- Bill
 

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
So to be completely honest, I'm just starting to unerstand this one myself. When I first looked at your post I thought it would be easy. I'm pretty familiar with Sumproduct, so that's where my mind went immediately. I started playing with

=SUMPRODUCT((Data!A3:A1000=A3)*(Data!B3:B1000=B3)*(Data!C3:C1000=C5)*Data!D3:D1000)

Which gave me the similar results to what you were getting.

So then I started working with subtotal, but honestly I ran into quite a few problems. So I turned to Google. But let me try to explain best I can.

When you do something like "Sumproduct((Data!A3:A1000=A3)," It creates an array of 1s and 0s, when you combined them it begins to return rows that satisfy only those that have 1s all the way across. Then as a final step The Subtotal. SubTotal(109 is Sum, In an array fuction You must give it rows to look in, but then compensate for the header rows hence the "OFFSET(Data!D3,ROW(Data!D3:D1000 )-ROW(Data!D3),0)"

Some unlikely uses for Excel's SUMPRODUCT function | AccountingWEB Will help you with some of the uses of Sumproduct.

In any case, I'm glad it worked for you. Thanks for the feedback
 

billpq

Board Regular
Joined
Oct 19, 2004
Messages
102
I kind of get the idea, just need to play and practice with a few examples until it sinks in. Thanks again for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,347
Latest member
tbanack

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