Hello, I am trying to build a sales leaderboard and I’m almost done with it, but there’s this one part that I cannot figure out with ARRAYFORMULAS.
I’m trying to make it so that whenever a new row is added, a formula is automatically applied to the new row. I don’t want to create 10,000 rows and then drag the formula all the way down 10,000 rows because it displays all weird in the app and it’s not efficient.
I’ve used the ARRAY formula on the ‘Stat Tracker’ sheet within the Google Sheet in the ‘Stat Summary Of The Day’ and it works perfectly there. Whenever I add in a new row, the formula is automatically applied to the new row that was created.
But I am trying to do the same thing in the ‘Email Whitelist’ sheet under the ‘Outreaches (Ref Data)’ column, but it is not working as it should. I’m not sure how to make it so that the formula is applied all the way down through the whole column, even when a new row is created. The formula contains a SUMIF function.
Would anyone be able to help me with this? I’d truly appreciate any guidance or help. This is the final piece to make the leaderboard function perfectly.
I’ll share the leaderboard I’ve created too once it’s complete for anyone that might need it.
First of all, I like to structure my arrayformulas like below (formula taken and modified from Stat Tracker). This ensures that if the row value in column A is empty, it will create a blank value for the array. So in your sheet with rows 12-20, if you aren’t using per user data, this would hide the rows from the list.:
As for your SUM formula, I won’t admit how much I wracked my brain on this one before I realized how much I was overthinking it and how simple the solution is. Instead of trying to work out if the date is within the first and last day of this month, I just converted the date from Stat Tracker and today’s date into YYYYMM format and then joined it to the email address. This allowed me to do a simple equal compare with SUMIF. So 201911joseph@xxxx.xxx equals 201911joseph@xxxx.xxx. SUMIFS is not compatible with arrayformulas. Don’t ask why, because I don’t really understand why. Here is your formula.
I’ve literally spent DAYS trying to figure this out Jeff, thank you SO much. You are a lifesaver, no exaggeration haha.
This is genius wow, I would have never thought to do something like that by joining the text with the email address. Thanks for the arrayformula structure to make it blank if there’s nothing in it. I’ve never really done complex formulas before so this was quite a ride and I appreciate your help on this. The app is pretty much complete and the leaderboard functions perfectly!!
One part I still don’t understand yet is how you were able to make the formula fill all the way down to the last cell? I thought the SUMIF formula didn’t work with the ARRAYFORMULA? I’m wondering how you made that work. Thanks a million!
Hehe, I was trying some wild stuff too and a lot of Google searching. A couple of of sites I came across mentioned the joining of columns, so I tried joining some combination of date and email…using datevalue to get a simpler number value…trying to come up with some sort of mathematical formula… I also found some info where people were kind of reversing their greater than/less than formulas, getting a total sum and subtracting the amounts that didn’t match (kind of hard to explain). It was in the middle of trying different join combinations that it finally hit me…literally. I did a literall facepalm!
What’s different with my formula is that I’m using SUMIF instead of SUMIFS and only doing one compare instead of three. For whatever reason, SUMIF is compatible with Arrayformulas, but SUMIFS aren’t.
Glad I could help. I was pretty proud of myself once I figured it out.
Wow yeah that was deep in the archives of the internet haha. That sounds WAY more complicated than what I was trying to do LOL.
Well done with discovering this nifty formula. Saves hundreds of hours of headache.
I’m also trying to find out how I can make this specific sheet count up by rows to correspond to their ranking on the leaderboard. Would you know a way to create an arrayformula to create a series that counts up in ordinal format when a new row is added by chance? Right now I don’t have a formula to create this and I just input the ordinal values by hand.
Would something like this work? I’m not sure exactly what you’re asking for, but this formula will just number the rows starting at Row 2. You would put this formula in cell B1. It includes the column heading, but does not count the heading row.
={“Rank”;ArrayFormula(ROW(B2:B)-1)}
This one limits the row numbering to the first 20 (not including header row).
I didn’t flag it myself but I am able to see it, maybe because of my access level. In any event it could have been flagged because it shows some emails that maybe someone thought should not be seen by all. Again I am speculating on this reason.
