# Calculate data from multiple sheets and big rows.

1. ## Calculate data from multiple sheets and big rows.

hi all,

I want to calculate the data from the sheet "data 1", "data 2" and "data 3" at the same time (3 sheets should only have 1 sheet, because the row data is more than 500 thousand, it is divided into several sheets). From these 3 sheets I want to retrieve data from the "eff_date" column in all sheets, where the formula is to calculate the amount of data with the number "5" in the "BUCKET_FROM" column in all sheets divided by the total number of data in these columns per each date. please provide an efficient formula for this.

sample : attached

thank you,

Regard

Dolphino

2. ## Re: Calculate data from multiple sheets and big rows.

with EFF date 31/01/2015, there are 28 rows of 5 in sheet data2. Expected: G5: 0.0233139050791007
How was G5 calculated to be that result?

3. ## Re: Calculate data from multiple sheets and big rows.

hi bebo021999

this is for sample EFF date 31/01/2015 ,
kindly help for this.

thankyou

4. ## Re: Calculate data from multiple sheets and big rows.

Try

=IFERROR((COUNTIFS('data 1'!\$C:\$C,\$A3,'data 1'!\$H:\$H,5)+COUNTIFS('data 2'!\$C:\$C,\$A3,'data 2'!\$H:\$H,5)+COUNTIFS('data 3'!\$C:\$C,\$A3,'data 3'!\$H:\$H,5))/(COUNTIF('data 1'!\$C:\$C,\$A3)+COUNTIF('data 2'!\$C:\$C,\$A3)+COUNTIF('data 3'!\$C:\$C,\$A3)),0)

5. ## Re: Calculate data from multiple sheets and big rows.

Try:
``Please Login or Register  to view this content.``

6. ## Re: Calculate data from multiple sheets and big rows.

Hi JohnTopley & bebo021999

thank you for your help, that formula running correctly.

Best Regard

Dolphino.

7. ## Re: Calculate data from multiple sheets and big rows.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

8. ## Re: Calculate data from multiple sheets and big rows.

hi

There are currently 1 users browsing this thread. (0 members and 1 guests)