SUB-TOTAL FROM ANOTHER SHEET 1

[achillese]

Hi,

I need some help on this.

My objective is to place the sum amount from one tab sheet to another.

Source Sheet Tab:
----------------
Dealer - TrnType - Amount
------ ------- ------
ZT5 RUBY $100
ZT5 RUBY 150
ZT5 PORT 90
ZT5 PORT 110
XXX RUBY 100
XXX PORT 150


Target Sheet Tab/Desired Result:
-------------------------------

Dealer PORT RUBY
------ ------ -------
ZT5 $200 $250
XXX 150 100

The Dealer is common in both spreadsheet, however, the Port and Ruby columns in Target Tab are not.

It would have been easier using VLOOKUP function if there is only one condition (Dealer). However, in this case there are more than 1 condition to meet, Dealer, Port, and Ruby.

I can not use also sub-total function since the result would have to be added to another tab sheet.

I tried SUMIF and COUNTIF functions but I could not work it out with more than 1 condition.

Thanks +++

 

[arthurbr]

Hallo Achille,

would a Pivot Table not be the best solution to your problem ?

Cheers

 

[petrosky]

Hi there,

It looks like you may be able to utilise a Pivot Table to get what you want.

Data, Pivot Table Report then follow the wizard.

HTH.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[petrosky]

Doh!



Remember- It's nice to be important,
but it's important to be nice

 

[achillese]

Pivot would have been an easy job. But my target tab sheet is pre-formatted with charts ready for printing and contains the monthly figure which is updated from other tabs also. the source tab sheet is just one of the sheets that updates the target tab sheet.

I have updated the target tab sheet using SUMIF, SUMPRODUCT, and COUNTIF functions for other data but could not work out this one. Even without using pivot, I can achieve the desired data using cascading sub-total but this is not what I desired.

Thanks for the quick reply.

 

[petrosky]

Hi again,

You could check out array formula for evaluating multiple conditions.

http://www.cpearson.com/excel/array.htm

HTH.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[achillese]

Hi Petrosky,

Thanks for the link. The example given in the link does not actually work. The SUM formula doesn't work but making it as SUMPRODUCT works. I didn't realised that you can actually add more than two arrays. I was just lazy not going back to online help.

Here is my final formula that works...

=SUMPRODUCT((Jul!$N$1:$N$2862=$C8)*(Jul!$L$1:$L$2862="RUBY ")*Jul!$W$1:$W$2862)

Thanks a lot...