Find Jobs
Hire Freelancers

Google Sheets Optimization (rewrite formula to calculate sum based on multiple creteria)

$10-30 USD

Completed
Posted over 8 years ago

$10-30 USD

Paid on delivery
I am trying to generate a table based on number of hours spent per month and per project for employee timesheets. The entry is done on the "Project Hours" page and the table on the "Summary" genarated automatically each time employee makes an entry. Each employee can enter time upto 3 projects per day. Here is the link for the sheet: [login to view URL] I have created some formulas to generate the table, they all work but my problem is the calculation time. It is takes too long to recalculate each entry. I have tried following formulas: 1. SUM(FILTER()) 2. SUMIFS() 3. SUMPRODUCT() Sum(filter() works the fastest among others, but still slow since it is a huge table (150rows by 72 columns, which may also grow in the future). The formula that I am using on cell C4 is as follows: =IFERROR(SUM(FILTER('Project Hours'!$D:$D; 'Project Hours'!$C:$C=$A4; 'Project Hours'!$A:$A<=C$2; 'Project Hours'!$A:$A>=C$1));0)+IFERROR(SUM(FILTER('Project Hours'!$F:$F; 'Project Hours'!$E:$E=$A4; 'Project Hours'!$A:$A<=C$2; 'Project Hours'!$A:$A>=C$1));0)+IFERROR(SUM(FILTER('Project Hours'!$H:$H; 'Project Hours'!$G:$G=$A4; 'Project Hours'!$A:$A<=C$2; 'Project Hours'!$A:$A>=C$1));0) WOULD THERE BE AN ALTERNATIVE and FASTER FORMULA TO GENERATE THE TABLE? MAYBE WITH OFFSET and COUNTA FUNCTIONS. Any help is greatly appreciated.
Project ID: 8992903

About the project

2 proposals
Remote project
Active 8 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
A proposal has not yet been provided
$30 USD in 4 days
5.0 (6 reviews)
2.7
2.7
2 freelancers are bidding on average $25 USD for this job
User Avatar
Did you try SUMPRODUCT ? .
$20 USD in 1 day
5.0 (81 reviews)
4.9
4.9

About the client

Flag of TURKEY
Istanbul, Turkey
5.0
8
Payment method verified
Member since Oct 14, 2012

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759) & Freelancer Online India Private Limited (CIN U93000HR2011FTC043854)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.