• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help required in counting... from different sheet

udprocks

udprocks

Member
Hallo Again Dear all,

Can anyone please help me with this i have a file that have 3 sheets

1st sheet (i.e master sheet)

2nd and 3rd are source that what i want to count on master sheet...

which formula i can use for it please suggest its urgent ,,,,,

sample file uploaded....

here i want no count source "total calls " count only..

hope i am clear with my query.....
 

Attachments

Hi udprocks,
you can use =IFERROR(VLOOKUP($A2,Source1!$A$2:$K$10,COLUMNS($A2:B2),FALSE)+VLOOKUP($A2,Source2!$A$2:$K$10,COLUMNS($A2:B2),FALSE),"")

copy down and across.
Regards,
 
Hi Jitendra,

You can also take this step:

On the Master sheet Create a list of names of sheet, Say In L15: Source1 and in L16: Source2.

Give it a name: SheetList
and than use formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A:A"),A23,INDIRECT("'"&SheetList&"'!B:B")))
in B23 and copy down. Confirm the formula with Ctrl+Shift+Enter.

EDIT: Changed the formula for SheetList in the file to dynamic, so if you add another sheet say Source3, just add the sheet name below the list and formula will be updated.

See the file.

Regards,
 

Attachments

Somendra Misra said:
Hi Jitendra,

You can also take this step:

On the Master sheet Create a list of names of sheet, Say In L15: Source1 and in L16: Source2.

Give it a name: SheetList
and than use formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A:A"),A23,INDIRECT("'"&SheetList&"'!B:B")))
in B23 and copy down. Confirm the formula with Ctrl+Shift+Enter.

EDIT: Changed the formula for SheetList in the file to dynamic, so if you add another sheet say Source3, just add the sheet name below the list and formula will be updated.

See the file.

Regards,
Click to expand...


hallo somendra sir,

its great for further action its help me lot,

and one thing can we add this formula for also rest fields ? (like we can add also column C, D,E,F,G,H,I,J) this formula ...
 
EDIT: Changed the formula for SheetList in the file to dynamic, so if you add another sheet say Source3, just add the sheet name below the list and formula will be updated.


i have a question in this sir, can u please define how to add sheetlist for dynamic, for further source... (and also in ur file u have not give name sheetlist for sourses but formula is working) i am little bit confused in this...
 
Somendra Misra said:
In the file, go to Name Manager: By formula tab or pressing Ctrl+F3, there you can see SheetList definition. See the file.

Regards,
Click to expand...


oh yes i can see there after pressing CTRL+F3

=OFFSET(Master!$L$15,0,0,COUNTA(Master!$L$15:$L$30),1)

so when i need to create another like my sample excel then this formula is same for all sheet list's except range change like here ($L$15$L$30)..

is this correct ?
 
L15:L30 is the range where I will put my sheet names starting from L15. Suppose your sheet list goes upto 100 nos. so you need to increase the range something like L15:L200.

Regards,
 
Hi,
I tried using sumifs formula, try this also. it is simple way.
=+SUMIFS(Source1!$B$2:$B$9,Source1!$A$2:$A$9,Master!A2)+SUMIFS(Source2!$B$2:$B$9,Source2!$A$2:$A$9,Master!A2)

You need to change the source as you required.
Kindly let me know if you have any clarification.
 

Attachments

Syedali said:
Hi,
I tried using sumifs formula, try this also. it is simple way.
=+SUMIFS(Source1!$B$2:$B$9,Source1!$A$2:$A$9,Master!A2)+SUMIFS(Source2!$B$2:$B$9,Source2!$A$2:$A$9,Master!A2)

You need to change the source as you required.
Kindly let me know if you have any clarification.
Click to expand...
Thank you again dear,

its also work like charm,....

:)
 
You must log in or register to reply here.
Back
Top