0

Good day - thanks in advance for any help.

Working with a moderately sized dataset (currently about 80,000 records, but it will grow over the next few months) and need to do a countif (to show how many instances of a record have been recorded) and a countifs (to show how many instances of each record have a certain value from another sheet).

Right now, I'm doing it with COUNTIF in one column, and COUNTIFS in another ... and it takes quite a while to finish.

Do any of you have any recommendations that I can try, which will accomplish those objectives?

To reiterate - I have a list of 80,000 strings which have been rated as GOOD or BAD - I am making a unique list of those strings with a counter for both TOTAL # of Instances (countif) and TOTAl # of BAD ratings.

Thank you so much for any suggestions!

JP_Romano
  • 61
  • 2
  • 9

1 Answers1

0

Excel's buit-in functions are just unbeatable for the operations they are designed for. This is especially true for CountIf and CountIfs. Forget about it.

You should not think about using something else, but probably about when to do the update. If the calculation of formulas is slowing down the editing of your worksheet, you might switch to manual calculation, which will permit you to trigger the calculation manually when needed.

You may also think of redesigning your data, or move it to a database in a way that exploits the indexing features that DBMS offers. Unfortunately, Excel with all its power, is not a DBMS.

A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • 1
    Thank you! I do have the data stored in a couple of SQL tables, but haven't been able to get the statements right, and since I'm far more comfortable in vba than in SQL, I was hoping to stick with it. I'm afraid I may not have a choice but to learn something in SQL or deal with the lengthy processing time. Appreciate your input! – JP_Romano Mar 18 '17 at 13:57
  • @JP_Romano you're welcome. I believe DBMS/SQL is the way to go. Think of using the appropriate indexing to improve the search on the fields you're counting or searching. – A.S.H Mar 18 '17 at 14:06