[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1281
  • Last Modified:

Speed up =sumif(A:A,A:A,B:B) ?

=sumif(A:A,A:A,B:B)

Very quickly gets very slow.

What ways are there to tell it not to add up one million rows (or whatever)?

Typically the business use involves a very few thousand rows, tops. Usually 1 or 2 thousand only.

Purpose is balances by (accounting) reference.

I plan to make it a named formula, so any array type solutions also welcome.

I am trying to avoid A1:A1000 as a solution.

Anthony
0
Anthony Mellor
Asked:
Anthony Mellor
1 Solution
 
NorieCommented:
Anthony

You could try using dynamic named ranges that will only include rows with values.

What data do you have in the 3 columns?

By the way, the criteria (3rd argument) is normally a single expression not a range.
0
 
SteveCommented:
Get your data...
highlight a cell inside it...
then use Data > InsertTable

Then use FormulaNames to name the table.
Then in sumif start typing the table name... it will appear in the list... then open square brackets and a list of your columns will appear.
Construct formula this way =SUMIF(Table1[AA],"A*",Table1[BB])

See attached.

The table will grow / shrink as data in it changes, so the ranges will be the right size automaticaly.
Example.xlsx
0
 
barry houdiniCommented:
Normally SUMIF only uses the "used range" anyway so actually using the whole column for the ranges isn't normally any more "expensive" than using a named range.....but why are you using a whole column for the criteria range? If the formula should be copied down then use just

=sumif(A:A,A1,B:B)

in row 1 copied down

Also do you need a sum on each row even if A1 is repeated? You could invoke SUMIF just on the first row for each repeated value, i.e. like

=IF(COUNTIF(A$1:A1,A1)=1,SUMIF(A:A,A1,B:B),"")

regards, barry
0
 
Anthony MellorChartered AccountantAuthor Commented:
What data do you have in the 3 columns?

invoice numbers
reference number being cross reference to the payment
amount of invoice(s)
amount of payment

amount of payment contains a sumif to derive the payment from the invoices with the same reference as this payment

amount of balance remaining unpaid per invoice

ok, that's more than 3 columns.
if all Greek I will write and upload an example.

Anthony (I have seen the replies above and now savouring them, thank you).

edit: if an example is needed I will make it another question.

edit I am wondering if the answer is as simple as the twice mentioned criteria needs only be one cell not a column.
0
 
Anthony MellorChartered AccountantAuthor Commented:
Points for (if as a mere aside) directly and being the first to solve the problem. The Range as criteria was slowing everything down.

followup re tables Q_28146299.html

follow up re item counts: Q_28146326.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now