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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,S
regards, barry
=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,S
regards, barry
ASKER
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.
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.
ASKER
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
followup re tables Q_28146299.html
follow up re item counts: Q_28146326.html
highlight a cell inside it...
then use Data > Insert > Table
Then use Formula > Names 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*",Tab
See attached.
The table will grow / shrink as data in it changes, so the ranges will be the right size automaticaly.
Example.xlsx