Array formula help across multiple tabs

**Fatnslow** · 04-05-2009, 06:57 PM

Hi all,
First time poster, long time user of the site. I'm stuck with a formula and want to see if someone here can crack it. I've attached an example.

What I'm trying to do is populate a table on tab 1, summing sales figures for different markets and different weeks. The data sheet on tab 2 is the 'raw' data from which the table (tab 1) is to populate from.

I've tried a Sumif array formula, which only gets me so far (ie: I can get it to populate for 2 variables, but I'm missing part of the formula to look up the 3rd variable).

Help on this one would be greatly appreciated as I'm stumped.
Thanks,

**shg** · 04-05-2009, 07:14 PM

In B3, =SUMPRODUCT( (Data!$C$2:$C$17 = Table!$A3) * (Data!$D$2:$D$17 = Table!B$2) * Data!$E$2:$E$17)

Then copy across and down.

**Fatnslow** · 04-05-2009, 07:23 PM

Thankyou so very much shg!

**shg** · 04-05-2009, 07:25 PM

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

**Fatnslow** · 04-05-2009, 10:24 PM

Thanks to shg for solving my formula error, I now have another problem with this file.

If I 'delete' the data sheet and insert some new data in tab 2, the table in tab 1 returns a #REF error.

Does anyone know how to fix this issue? Am I missing something from the original formula?

Thanks again,

**TonyB51** · 04-05-2009, 10:58 PM

Hi,

It appears that you deleted all data lines, that is why you got the REF.

You can select the DATA and delete the values.

The formula's will only remain in tack if you still have a reference point.

Cheers

Tony

**Fatnslow** · 04-06-2009, 01:34 AM

OK, adding another variable here into the equation.

Thanks to all those who have replyied.

I've added a 'column' on the data sheet (tab 2) as a 'product'. I now want to populate the table in tab 1 to take this new variable into account. Will this just be a quick 'rejig' of the formula given to me by shg?

New file is attached.

Thanks again,