• 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.

Sumifs with array,

A

abhi2611

Member
Hi,

Attached is a sample excel file. I tried to use an array with sumifs function. It works the way I want it to but takes forever to calculate. Also, there is a lot more data than what I am showing in this test file.

Is there a better way of doing this?

Any help is appreciated.

Cheers!
 

Attachments

Hi.

How much more data is there in your real file?

Also, the range currently being used for criteria_range2, i.e. $A$3:$A$29, contains just 2 non-blank entries. Is it necessary to include these 25 extraneous values in the calculation? Or could this range be dynamically reduced so that it consists of non-blank entries only?

Can you paste a version of the formula that you are using in your real file, including the correct range references?

Regards
 
Hi,

There are about 300 rows with sumifs formula and about 9000 lines of data.

Range two has a total of 90 unique names and not all of them are listed at all times. It could be dynamic. If there is a way to make the criteria_range 2 dynamic, that will definitely reduce the calculation time.

Below is the formula that I am using.

=SUM(SUMIFS(Data!BW$2:BW$8026,Data!$E$2:$E$8026,$A$3:$A$103,Data!$B$2:$B$8026,7,Data!$M$2:$M$8026,Summary!$B6)*1000)

Thank you.
 
@abhi2611

Thanks.

I notice that the first reference in that formula (the sum_range) is relative with respect to columns, which would suggest that this formula is intended to be copied across.

I also notice that the reference to Summary!$B6 is also relative, though with respect to rows, which would indicate that this formula is also intended to be copied down.

Can you confirm that both of these assumptions are correct?

Regards
 
@Asheesh

What's your logic here?

Apart from the dynamic range that you've implemented, my first impression is that a CSE version of SUMPRODUCT will not in any significant way improve performance here.

Besides, if you're using CSE, then the use of SUMPRODUCT is a bit tautological; simply SUM will suffice.

Regards
 
XOR LX said:
@abhi2611

Thanks.

I notice that the first reference in that formula (the sum_range) is relative with respect to columns, which would suggest that this formula is intended to be copied across.

I also notice that the reference to Summary!$B6 is also relative, though with respect to rows, which would indicate that this formula is also intended to be copied down.

Can you confirm that both of these assumptions are correct?

Regards
Click to expand...

Hi XOR,

Both the assumptions are correct.

Do you have a simpler way of achieving it?

Thank you.
 
Since the calculations for the following two criteria:

Data!$E$2:$E$8026,$A$3:$A$103

and:

Data!$B$2:$B$8026,7

are fixed and appear in all formulas, it would save an awful lot of resource if each formula did not have to recalculate those parts afresh.

I would go to Name Manager and define:

Name: Arry1
Refers to: =(ISNUMBER(MATCH(Data!$E$2:$E$8026,Summary!$A$3:$A$103))*(Data!$B$2:$B$8026=7))

after which your formula becomes (non-array):

=SUMPRODUCT(N(Data!$M$2:$M$8026=Summary!$B6),Arry1,Data!BW$2:BW$8026)*1000

and, since the array resulting from Arry1 has been calculated just once (and stored), you should save a significant amount of resource this way.

Regards
 
N.B. Having said that, the fact that this requires switching to SUMPRODUCT, which is notoriously slower than SUMIFS in such situations, perhaps offsets any saving on resource that the Named Range offers.

I would be interested if you could let me know which appears to offer the most efficient set-up.

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