Sum Visible Cells with Arrays

[billpq]

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

 

[Redwolfx]

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]

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]

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]

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!