Very long countifs formula - is there a better function?

**k.m.** · 05-19-2013, 06:00 PM

Hi

I am doing a document for statistics for football officials (I have been writing about the document in here before and got some great help), and now I have some pretty long formulas, I was wondering if could be cut down.

Basically I have 2 parts in the document.
Part 1 is penalty reports from the football games, which says which officials is calling each penalty, if the penalty is accepted or declined, and the grade of each called penalty (correct, incorrect og marginal). Each game is in its own sheet, and they're called "Sheet1" through "Sheet59" and they're all named "allsheets" with name manager (thanks vlady!)

Part 2 is then the statistics gathered from the penalty reports. I have to gather a whole lot of different stats, and for that I use countifs a whole lot, but I can't get it to work with multiple columns for the same criteria, and then I get some "countifs+countifs+countifs+countifs+countifs+countifs" type of formulas, and the document is getting really slow, but I have lived with it until now... :-)

Now I have come across a problem, that I with my current knowledge only can solve with a very very long formula...

In each penalty report, the initials for each official in the game is in the second row. U2 is referee, V2 is umpire, W2 is linesman and so on.
In the penalty report is 6 columns, that can say which official has called the penalty, but here they are written as position, and not by initial.
Now I need to find out how many fouls each official in the league has called, but there is 6 columns that can contain a position, and there is 7 different positions, so if one of the 6 "position" columns says "R" it has to find out who "R" is... I made a "working" monster countifs formula, but it can't be used as it makes everything freeze for minutes while it calculates :-)

I don't know if I can explain it good enough, as English isn't my native language, so I have attached a sample document with only report for one game to make it a bit faster, and stripped for some of the simpler statistics. I made the "monster formula" in just one cell here at one of the officials just to show how bad it is :-)

I hope that someone can help with a formula that can gather these informations. And if you have the time and urge for it, feel free to look at the other formulas in the document to see if there is a more efficient way to do it - I'm fairly new in working with Excel, but I have learned a few functions in my work with this document by asking on this forum, and I would like to learn all I can about Excel, as I will probably be doing more stuff like this document.

**Jacc** · 05-21-2013, 04:43 AM

I see that you are using whole columns in arrayformulas. That will slow down recalc. I tried to convert some of your data to Excel Tables but I couldn't get the array formulas to work. UDF's in combination with Tables may work.

**k.m.** · 05-21-2013, 10:21 AM

Okay, so it could help if I changed the formulas from whole columns to for instance D1:D250 if I'm sure there won't be more than 250 rows? Every row in the penalty reports is one play in a game, so it would be very unlikely to have more than 250 plays in a game even if it goes to overtime.

What is UDF's

I'm not too experienced with Excel, this is my first document with other than + - * / and sum

But I got some great help in here (although I haven't fully understood the use of INDIRECT and SUMPRODUCT, but I copy/paste the formulas people wrote in here, that's why it gets so long when there is many columns the data could be in)

**Jacc** · 05-21-2013, 03:24 PM

Yes, I think reducing it to D1:D250 would help.

UDF = user defined function. It is written in VBA (Visual Basic for Applications, same as macro) but for the user it looks as just another function.
While sometimes criticized for being slow they may very well end up being many times faster than arrayformulas like yours.

**k.m.** · 05-22-2013, 11:03 AM

Cool thanks, gotta learn that :-) It's already much faster now using 1:300 in each column though.
Now I'm trying to learn to make a macro that deletes all the rows without penalties (all the rows in all the "allsheets" list where all of columns C, D, L, M and U:AA are empty) - if I can make such a macro and run it once to remove the empty rows in all 59 sheets, then I can reduce the formulas further to like 1:35 in each column, as we never have more than 35 penalties in a game. Right now we just can't get just the penalties as the sheets is imported from another program, where we can only get all the plays, not just the penalty ones.

**Jacc** · 05-22-2013, 03:32 PM

A macro is one options I guess. Like I mentioned above, I tried to use Tables and refer to the Tables in the formulas. They have the advantage that they are no bigger than the data. If you post what you have now I can take a look at it again.

**k.m.** · 05-24-2013, 02:47 AM

Right now it looks like this attachment. It works quite a bit faster just by reducing it to 300 rows instead of whole columns. For now there is only 12 game-sheets though (as we're not far into the season yet), for every game played there will be another sheet, and we should reach 59 at the end of the season.

I have tested it with only one "game-sheet" for some of the formulas (Sheet5), as not all the information needed is typed into all the games. The "game sheets" (Sheet1-Sheet59) is exported from our video review program, so I can't change how they are set up.

I was thinking of reducing the amount of rows needed further, by making some kind of macro I just have to run once which then removes all the rows that have no penalties or other important information (rows where columns C, D, L, M and U:AA all empty). Then I could limit it to 40 rows instead of 300, that should help some more :-) But if another solution is faster then it is of course not needed.

**Jacc** · 05-26-2013, 05:51 AM

No UDF's so far but I did some tinkering with your formulas instead. The really long one I shortened down a bit. And I made a new named range that contains the number of rows on each sheet. A quick test (with the named ranges expanded to include 12 sheets) revealed that it took 25 seconds to open the one you posted and only about 12 seconds to open my modified version.

I think a SUMPRODUCT was missing on the Flags sheet, I fixed that.

**Jacc** · 05-26-2013, 06:37 AM

I didn't properly read your post about deleting rows with no data in until now. I put together such a macro here, button on Data sheet.
Of course you have to reset the data to test it properly. Be aware to have backups, there is no Undo with macros.

Byt the way, this workbook opens in 4 seconds or something like that.

**k.m.** · 05-26-2013, 05:22 PM

There something wrong in the first sheet (grades per game) as the first game seems empty, but the data for the first game shows at the second game and so on. And the counts is not correct - at the first game (which shows at the second) is 5 CC, 1 MC, 1 IC, 0 NC and 1 NG. But in the old sheet (and when I look in Sheet1) is should be 5 CC, 0 MC, 1 IC, 1 NC and 1 NG... How can it count wrong? I can't see why the first game seems empty either, as far as I can see it should look for the right things in the right sheet, but it returns strange numbers. Looking quickly at the first sheet, the counts and percentages is different from the original document in all games except 2... But I can't figure out why.

The second sheet seems like it's the same result with the old and new formulas, so I think that's fine

In the third sheet (flags per official) it seems like changing the range to more sheets messes something up both in the original document and the new one... But I can't see why. It's the counting of games for each position (C3:C9). With only sheet5 it counts correctly, but when using sheet1-sheet12 it fucks up for some reason, and again I can't figure out why... The "Flags per official" part seems to count correctly (with very little testing) in your document, but when I try to make "allsheets" list include sheet1-sheet12 in my original document, it fucks up in that part too. Only the counting of games, the other stuff seems to work correctly as far as I can see. It's kinda wierd...

The "Statistics" sheet I haven't tested yet, but I'll as soon as possible fill out the sheets 1-12 with all the data for proper testing.

It looks very cool with the button that removes the unneeded rows, and cool feature with the "allsheetrows" list so it only searches in the needed number of rows on every sheet, that's exactly what I was looking for!
I'll start filling in the data in the games right away to get better testing material.

**Jacc** · 05-27-2013, 07:56 AM

On the Formulas banner, to the right, there are some buttons to control recalc. Setting it to manual saves a lot of time when working with this workbook since you can do a lot of changes without having to wait for recalc everytime. When you do want a recalc you just hit one of the other two buttons (one is for workbook I think, the other one for sheet).

Originally Posted by k.m.

There something wrong in the first sheet (grades per game) as the first game seems empty, but the data for the first game shows at the second game and so on. And the counts is not correct - at the first game (which shows at the second) is 5 CC, 1 MC, 1 IC, 0 NC and 1 NG. But in the old sheet (and when I look in Sheet1) is should be 5 CC, 0 MC, 1 IC, 1 NC and 1 NG... How can it count wrong? I can't see why the first game seems empty either, as far as I can see it should look for the right things in the right sheet, but it returns strange numbers. Looking quickly at the first sheet, the counts and percentages is different from the original document in all games except 2... But I can't figure out why.

Since this was actually not an array formula (or SUMPRODUCT formula) it wasn't handling my new variable properly. Fixed that now.

Originally Posted by k.m.

In the third sheet (flags per official) it seems like changing the range to more sheets messes something up both in the original document and the new one... But I can't see why. It's the counting of games for each position (C3:C9). With only sheet5 it counts correctly, but when using sheet1-sheet12 it fucks up for some reason, and again I can't figure out why... The "Flags per official" part seems to count correctly (with very little testing) in your document, but when I try to make "allsheets" list include sheet1-sheet12 in my original document, it fucks up in that part too. Only the counting of games, the other stuff seems to work correctly as far as I can see. It's kinda wierd...

Again it's an issue with arrayformulas. An array formula (entered with Ctrl+Shift+Enter) will make functions that normally just accept a single cell as input, accept a range of cells as input. This effect can also be produced by surrounding the formula with SUMPRODUCT but it's not quite the same. Your INDIRECT formula with the allsheets variable seems to work with SUMPRODUCT but not as an array formula. I have no explanation for this and I'm not sure how arrayformulas compare with SUMPRODUCT. Anyone else want to shed some light on this?

Here is something you can try:
In your original workbook, change the range to include the first 12 sheets
In the Flags sheet, select cell C3.
Under the Formulas banner use the Evaluate Formula button to go through the calculation step by step
Now add the SUMPRODUCT function around the formula and try the Evaluate Formula button again. Different?

Originally Posted by k.m.

It looks very cool with the button that removes the unneeded rows, and cool feature with the "allsheetrows" list so it only searches in the needed number of rows on every sheet, that's exactly what I was looking for!
I'll start filling in the data in the games right away to get better testing material.

Good to hear something is working out!

I'm not very good at testing so it helps a lot that you do that part.

I did some loose testing now with recalc time and it seems there is no difference between current workbook and your most recent workbook.

Fair enough, a few more things are being calculated properly but not that much.

**k.m.** · 05-27-2013, 03:01 PM

Looks like it works now, the numbers look right :-) Unfortunately I won't have much time playing around with it this week as I'm gonna have a whole lot of overtime at work, but I'll continue the work next week.

I finished the game sheets with all the columns if you wanna use that for anything

**arlu1201** · 05-27-2013, 03:39 PM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

**k.m.** · 05-28-2013, 12:02 PM

As I wrote I haven't had time to look through the whole thing yet, but the stuff that looked wierd in my post before the last one, looked right now after a quick glance. There's still some testing to do though, to see if all the formulas create the right output, but that is what I wrote I wouldn't get time for this week unfortunately. That's why it wasn't marked as solved yet.

I already "starred" Jacc - I appreciate very very much how much he has helped me, I would never have gotten this far with the document without his help!

**Jacc** · 05-28-2013, 05:16 PM

You're doing it right.

Lot's and lot's of people on the forum don't do the basics like mark it solved and star (thanks!), hence we are glad to have moderators like arlu that tries to keep things tidy. I think you can just remove the SOLVED and reset it once you feel that everything is under control.

By the way, I did a more serious attempt at measuring the recalc time with the reduced data and the allsheetrows named range vs an identical workbook where all the ranges were changed back to rows 1:300. To my big disappointment there was no noticeable difference, both were around 8 seconds. Still, the data in the datasheets becomes easier to read when all the empty rows are deleted so at least the macro was worth the effort in my opinion.

**k.m.** · 05-29-2013, 10:51 AM

The macro is still very useful, as the supervisor is talking about making a sheet with all the penalties from every game, so next step would be another macro that copies the penalties from all the game sheets into a single sheet. That requires a few other changes too though, so that will be when all the current sheets and formulas are 100% done with testing and tweaking.

**Jacc** · 05-29-2013, 11:08 AM

Hmmm... If that was to be implemented ( a fairly basic macro, I'd say) then it would make sense to run the formulas from that sheet only. That would mean that a lot of SUMPRODUCT could be removed which would speed up recalc a lot.

**k.m.** · 05-29-2013, 11:41 AM

Didn't think of that. When I get some time again next week (hopefully) I think I'll play around with that.

I think the macrobutton should do something like:
1. Pulling row 1 in columns U-AD down so the initials and home/away team and game # is in all rows in all the game sheets
2. Then delete all rows without penalties in all the game sheets (and because of step 1 we don't need the first row anymore so that would be any row without data in column C)
3. Then copy the penalties from all games sheets to a new sheet which could be called something like "allgames" so that game 1 (Sheet1) is on top, then data from Sheet2 and so on.

How difficult would it be to make such a button?
The game sheets could still be there for other purposes, but you're right that the statistics could be gathered from the "allgames" sheet. Don't know why I didn't think of that myself from the start, as that should make the formulas much simpler, shouldn't it?

I'll try to play a little around with it when I get a little time again, which is hopefully next week if the overtime at work doesn't continue

**Jacc** · 07-08-2013, 11:32 AM

Not very difficult. Is this thread still alive?

**k.m.** · 07-08-2013, 12:33 PM

Well, kinda alive

I would have continued with it when I got time, but I never did get time... I still wanna finish the document, I just postponed it a little, because I have very little spare time at the moment.

Very long countifs formula - is there a better function?

LinkBack

Thread Tools

Rate This Thread

Display

Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Re: Very long countifs formula - is there a better function?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions