CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
apfick
apfick

Reputation: 55

how to script a simple count if

I tried to find this but I could not figure out the answer on the other questions that were posted. I have two conditions that are used to sort data. The months of the year and if it is ranked 1, 2, or 3.

what i need is on a summary page to count how many inputs there are for each month with specific rank

I hope this all makes sense I tried to clarify the best I can, I am really overwhelmed with this and have no type of coding/scripting experience. Thanks for any help!

I have used the codes below to return the dates and ranks of the data and to extract the month. It then uses those months in if statements to put on a summary page. What I do not know how to do is from here put a count formula in it. Like with this code here I want it to be similar to the formula if=month=1 and tiers(ranks)= 1 then count (it cant add because if it adds, when the page updates it will add to numbers that it already counted)

    for(var i =8;i<=j;i++) { //loops through a data table to see dates and ranks
    var dates = oppwon.getRange(i,22).getValue();
    var tiers = oppwon.getRange(i,14).getValue();
    var month = new Date(dates).getMonth()+1;

    switch (true){
      case((month==1)): //if it is january
         if(tiers==1)  // if it is rank 1
           jan1.setValue(); 
         if(tiers==2)
           jan2.setValue();

Upvotes: 2

Views: 256

Answers (2)

Swimmer F
Swimmer F

Reputation: 903

You could define a function

function countTiersByMonth ( dataTable, firstline, lastline ) {
    var result = [ [0,0,0],[0,0,0],[0,0,0], [0,0,0],[0,0,0],[0,0,0], [0,0,0],[0,0,0],[0,0,0], [0,0,0],[0,0,0],[0,0,0] ];
    var dates;
    var tiers;
    var month;
    for(i = firstline; i<=lastline; i++) { 
        dates = dataTable.getRange(i,22).getValue();
        tiers = dataTable.getRange(i,14).getValue();
        month = new Date(dates).getMonth();

        switch (tiers){ // we filter by tiers because it seems that you only care about
                        // tiers 1, 2, 3 wheras you care about all the months
            case 1: 
            case 2:
            case 3:
                result[month][tiers-1]++; //+1 for the respective tier
                                          //  of the respective month
                break;      //other tiers are ignored
        };
    };
    return result;
};

This takes the data table, the first significant line (8 in your example) and the last relevant line ("j" in your example) and outputs an array with 12 elements, one for each month, that contain 3 elements each, one for each tier you wanted to count. If you want, lets say the results for May, you call

tierlist = countTiersByMonth(oppwon, 8, j) // We do the counting here
print(tierlist[4][0]) // arrays start at 0, so May -> [4], Tier 1 -> [0]
print(tierlist[4][1]) // May, Tier 2
print(tierlist[4][2]) // May, Tier 3

Upvotes: 1

Chris Chen
Chris Chen

Reputation: 1293

Instead of setValue in the loop, you should consider doing the counting within variable/s and only setValue once the loop is completed.

Some suggested code per below:

for(var i =8;i<=j;i++) { //loops through a data table to see dates and ranks
var dates = oppwon.getRange(i,22).getValue();
var tiers = oppwon.getRange(i,14).getValue();
var month = new Date(dates).getMonth()+1;
var countTiers = {}; //count by tiers of months

countTiers[`m${month}`][`t${tiers}`]++;

You will end up getting an object like e.g. {m1: {t1: 2, t2: 1}} after the loop.

Then you can setValue in the desired column for the final count.

Upvotes: 2

Related Questions