Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Alternative to Sumifs (Computation Slowdown)

0

Hi Excel Experts, 

I'm trying to find an alternative to SUMIFs for the below problem.  I am trying to summarize the following:  Person A is listed on multiple rows, the project identifier is in a column, and the number of hours by each month are in a column.  Sumifs easily solves this but is now bogging down.

Is there an alternative to sumifs in this scenario?  I've researched SUM(Filter , maybe a nested Xlookup summation, or DSUM.

From ChatGPT - For Example

Month Project Person Hours Jan A John 10 Jan B John 5 Jan A Jane 8 Feb A John 12 Feb B Jane 15

Create a formula to calculate the total hours worked by a specific person on a specific project for a given month.

Answer
Discuss

Answers

0
Selected Answer

 Hello olz111 and welcome to the forum,

If you could post the formula you are currently using that would help us find a better solution (if one exists). It's possible that the way your current formula is written may be the cause of the slow down.

Update Jan 19

The way you have things, your formula is checking 1,048,576 rows. If you change your range references to something more specific it should speed things up. Example: change AJ:AJ to AJ5:AJ200 and L:L to L5:L200. Use row references to suit your situation but they must be the same for each range (AJ2:AJ125, L2:L125)

Hope this helps.

Cheers   :-)

Discuss

Discussion

Thanks Willie- Formula below
=Sumifs('Data'AJ:AJ,'Data'A:A,'Activesheet'A10,'Data'L:L,Activesheet'J1)
This is replicated across 140 columns and potentially 100+ rows.
I read that sorting the raw data and limiting the column reference could help..
olz111 (rep: 2) Jan 19, '25 at 3:10 pm
Olz111- Willie is right but you may not know that every version of Excel (2010 onwards) allows a maximum of 1,048,576 rows per sheet. 
John_Ru (rep: 6612) Jan 20, '25 at 3:23 am
@olz111,

Thanks for selecting my answer.
WillieD24 (rep: 657) Jan 20, '25 at 10:37 am
Add to Discussion
0

Break down by Month or Year seperate sheets. 

Discuss

Discussion

Sorry donblue  but that is NOT what we'd regard as an Answer- was it meant to be a question? If so, please raise a separate new question and give more details of what you're trying to do, preferably uploading a sample Excel file too. 
John_Ru (rep: 6612) Jan 20, '25 at 2:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login