Sumif from other worksheet

[sachar]

Dear All,

With reference to the attach sample file, May I know how to sum the sale of “Hamac” from associated worksheets (from: 01 Jan to 04 Jan) with the help of a single formula on the summary sheet which, I did manually (without a formula).

Please help me.

 

[ANKUSHRS1]

copy and paste below formula

=SUM(SUMIFS('01 Jan.'!H:H,'01 Jan.'!F:F,"hamac"),SUMIFS('02 Jan.'!H:H,'02 Jan.'!F:F,"hamac"),SUMIFS('02 Jan.'!H:H,'03 Jan.'!F:F,"hamac"),SUMIFS('04 Jan.'!H:H,'04 Jan.'!F:F,"hamac"))

 

[xlstime]

Hey Sachar,

You can use 3D reference using INDIRECT function, pFA

Dear All,

With reference to the attach sample file, May I know how to sum the sale of “Hamac” from associated worksheets (from: 01 Jan to 04 Jan) with the help of a single formula on the summary sheet which, I did manually (without a formula).

Please help me.

 

[deciog]

sachar

It would be that way you need

Décio

 

[xlstime]

Good one Décio!.

There are many ways to use function or formula, it is completely depends on user or formula coder

sachar

It would be that way you need

Décio

 

[sachar]

Dear All,

Thanks to all of you. Mr. Deciog formula is good but, it will not be working if the quantity is more than 1. Mr. Xlstime formula is some complicated for me and, I am trying to understand but, that I need.

 

[xlstime]

Some explanation below to help you to understand.

INDIRECT function is helps to convert text (non range) value to range.

Why Sumproduct function? - I have used sumproduct function because sumif function will return 4 values (check your self - just select sumif part formula and press F9 (function key) ) for sum 4 values we need any array formula to sum up values, thats why i have used sumproduct here (you can use sum but with ctrl+shft+enter )

Dear All,

Thanks to all of you. Mr. Deciog formula is good but, it will not be working if the quantity is more than 1. Mr. Xlstime formula is some complicated for me and, I am trying to understand but, that I need.

 

[deciog]

sachar.


What do you mean much more than one, because I testing and is showing the quantity, better explains that I can understand.

I am using Google translator, because I do not speak nor write in English, excuse if the translator does not correct this with English language.

Decio in Brazil - São Paulo

 

[bosco_yip]

Or try this without helper cells formula,

Total sale amount of “Hamac” from Sheets 01 Jan to 04 Jan :

=SUMPRODUCT(SUMIF(INDIRECT("'0"&ROW($1:$4)&" Jan.'!F:F"),"Hamac",INDIRECT("'0"&ROW($1:$4)&" Jan.'!L:L")))

Edit : You can change this number 4 (in red color) to additional new sheet number

Regards
Bosco

 

[deciog]

bosco yip, spectacular this form to add a spreadsheet, great, thank you
Decio