Sum columnA/sum columnB, if row Has data up to columnB

    I don't know if it is wrong to post too many questions in a day or not, but this has been such a great help I thought I would try to clean up some of my own formulas that look hideous and take lots of time to edit. I didn't know how to title this but basically my customers are billed monthly. The months are laid out across Row 12. Below the months is the amount the customer has paid on their bill. The customer has a set bill located in Column M starting at 13-150. The bill amounts are different for each customer. I want to know for each month what our percentage of paid vs. billed was. This of course then includes over customers who started later on or earlier and I want to only include those who have paid a bill at least once and have a number in column M >0. I will attach the sheet so you can see, and I do have a very lengthy formula that I believe works but it took about 2 hours just to do each formula. The formula is inserted in "paid/billed" row 5.


    Here is the formula:
    =SUM(W13:W125)/(IF(SUM($N$13:W13)<1,0,$M$13)+IF(SUM($N$14:W14)<1,0,$M$14)+IF(SUM($N$15:W15)<1,0,$M$15)+IF(SUM($N$16:W16)<1,0,$M$16)+IF(SUM($N$17:W17)<1,0,$M$17)+IF(SUM($N$18:W18)<1,0,$M$18)+IF(SUM($N$19:W19)<1,0,$M$19)+IF(SUM($N$20:W20)<1,0,$M$20)+IF(SUM($N$21:W21)<1,0,$M$21)+IF(SUM($N$22:W22)<1,0,$M$22)+IF(SUM($N$23:W23)<1,0,$M$23)+IF(SUM($N$24:W24)<1,0,$M$24)+IF(SUM($N$25:W25)<1,0,$M$25)+IF(SUM($N$26:W26)<1,0,$M$26)+IF(SUM($N$27:W27)<1,0,$M$27)+IF(SUM($N$28:W28)<1,0,$M$28)+IF(SUM($N$29:W29)<1,0,$M$29)+IF(SUM($N$30:W30)<1,0,$M$30)+IF(SUM($N$31:W31)<1,0,$M$31)+IF(SUM($N$32:W32)<1,0,$M$32)+IF(SUM($N$33:W33)<1,0,$M$33)+IF(SUM($N$34:W34)<1,0,$M$34)+IF(SUM($N$35:W35)<1,0,$M$35)+IF(SUM($N$36:W36)<1,0,$M$36)+IF(SUM($N$37:W37)<1,0,$M$37)+IF(SUM($N$38:W38)<1,0,$M$38)+IF(SUM($N$39:W39)<1,0,$M$39)+IF(SUM($N$40:W40)<1,0,$M$40)+IF(SUM($N$41:W41)<1,0,$M$41)+IF(SUM($N$42:W42)<1,0,$M$42)+IF(SUM($N$43:W43)<1,0,$M$43)+IF(SUM($N$44:W44)<1,0,$M$44)+IF(SUM($N$45:W45)<1,0,$M$45)+IF(SUM($N$46:W46)<1,0,$M$46)+IF(SUM($N$47:W47)<1,0,$M$47)+IF(SUM($N$48:W48)<1,0,$M$48)+IF(SUM($N$49:W49)<1,0,$M$49)+IF(SUM($N$50:W50)<1,0,$M$50)+IF(SUM($N$51:W51)<1,0,$M$51)+IF(SUM($N$52:W52)<1,0,$M$52)+IF(SUM($N$53:W53)<1,0,$M$53)+IF(SUM($N$54:W54)<1,0,$M$54)+IF(SUM($N$55:W55)<1,0,$M$55)+IF(SUM($N$56:W56)<1,0,$M$56)+IF(SUM($N$57:W57)<1,0,$M$57)+IF(SUM($N$58:W58)<1,0,$M$58)+IF(SUM($N$59:W59)<1,0,$M$59)+IF(SUM($N$60:W60)<1,0,$M$60)+IF(SUM($N$61:W61)<1,0,$M$61)+IF(SUM($N$62:W62)<1,0,$M$62)+IF(SUM($N$63:W63)<1,0,$M$63)+IF(SUM($N$64:W64)<1,0,$M$64)+IF(SUM($N$65:W65)<1,0,$M$65)+IF(SUM($N$66:W66)<1,0,$M$66)+IF(SUM($N$67:W67)<1,0,$M$67)+IF(SUM($N$68:W68)<1,0,$M$68)+IF(SUM($N$69:W69)<1,0,$M$69)+IF(SUM($N$70:W70)<1,0,$M$70)+IF(SUM($N$71:W71)<1,0,$M$71)+IF(SUM($N$72:W72)<1,0,$M$72)+IF(SUM($N$73:W73)<1,0,$M$73)+IF(SUM($N$74:W74)<1,0,$M$74)+IF(SUM($N$75:W75)<1,0,$M$75)+IF(SUM($N$76:W76)<1,0,$M$76)+IF(SUM($N$77:W77)<1,0,$M$77)+IF(SUM($N$78:W78)<1,0,$M$78)+IF(SUM($N$79:W79)<1,0,$M$79)+IF(SUM($N$80:W80)<1,0,$M$80)+IF(SUM($N$81:W81)<1,0,$M$81)+IF(SUM($N$82:W82)<1,0,$M$82)+IF(SUM($N$83:W83)<1,0,$M$83)+IF(SUM($N$84:W84)<1,0,$M$84)+IF(SUM($N$85:W85)<1,0,$M$85)+IF(SUM($N$86:W86)<1,0,$M$86)+IF(SUM($N$87:W87)<1,0,$M$87)+IF(SUM($N$88:W88)<1,0,$M$88)+IF(SUM($N$89:W89)<1,0,$M$89)+IF(SUM($N$90:W90)<1,0,$M$90)+IF(SUM($N$91:W91)<1,0,$M$91)+IF(SUM($N$92:W92)<1,0,$M$92)+IF(SUM($N$93:W93)<1,0,$M$93)+IF(SUM($N$94:W94)<1,0,$M$94)+IF(SUM($N$95:W95)<1,0,$M$95)+IF(SUM($N$96:W96)<1,0,$M$96)+IF(SUM($N$97:W97)<1,0,$M$97)+IF(SUM($N$98:W98)<1,0,$M$98)+IF(SUM($N$99:W99)<1,0,$M$99)+IF(SUM($N$100:W100)<1,0,$M$100)+IF(SUM($N$101:W101)<1,0,$M$101)+IF(SUM($N$102:W102)<1,0,$M$102)+IF(SUM($N$103:W103)<1,0,$M$103)+IF(SUM($N$104:W104)<1,0,$M$104)+IF(SUM($N$105:W105)<1,0,$M$105)+IF(SUM($N$106:W106)<1,0,$M$106)+IF(SUM($N$107:W107)<1,0,$M$107)+IF(SUM($N$108:W108)<1,0,$M$108)+IF(SUM($N$109:W109)<1,0,$M$109)+IF(SUM($N$110:W110)<1,0,$M$110)+IF(SUM($N$111:W111)<1,0,$M$111)+IF(SUM($N$112:W112)<1,0,$M$112)+IF(SUM($N$113:W113)<1,0,$M$113)+IF(SUM($N$114:W114)<1,0,$M$114)+IF(SUM($N$115:W115)<1,0,$M$115)+IF(SUM($N$116:W116)<1,0,$M$116)+IF(SUM($N$117:W117)<1,0,$M$117)+IF(SUM($N$118:W118)<1,0,$M$118)+IF(SUM($N$119:W119)<1,0,$M$119)+IF(SUM($N$120:W120)<1,0,$M$120)+IF(SUM($N$121:W121)<1,0,$M$121)+IF(SUM($N$122:W122)<1,0,$M$122)+IF(SUM($N$123:W123)<1,0,$M$123)+IF(SUM($N$124:W124)<1,0,$M$124)+IF(SUM($N$125:W125)<1,0,$M$125))


    It's a mess!




    Thank you for your help.
    cya

    Re: Sum columnA/sum columnB, if row Has data up to columnB


    Hi Cya,


    if I saw that formula in a workbook that was handed to me, I would want to repeatedly hit myself in the face with a rusty blunt object. And probably try and find the author (with said tool still in my hand)


    I think the easiest thing you can do here is create another sheet that sits behind the front sheet. You can just copy and paste the sheet if you want. However instead of the actual amounts paid against each customer, the cells for each months can sum up the cumulative amounts to Jan/Feb/Mar based on the amounts paid for each month on the front sheet.


    You can then insert a column or rows on that sheet to do a test to see if the sum is greater/less than column M.


    You can then use THIS number in your formula on the front sheet.


    First thing is to create another sheet with the cumulative numbers on to avoid having them in the formula. Once you do this you'll be able to set up the other calcs easily

    Re: Sum columnA/sum columnB, if row Has data up to columnB


    I am sorry call me dumb, but I am confused. I should total the months and put them against what should paid to see if it is the same.


    I am trying to get an individual number for each customer based on amount they paid per month from their starting date to the current month and divided by the total months in the calculation. So that I have a finished average they have paid per month overall. This would need to exclude the two columns in between each year where I have some other calculations. I am not sure if what you are suggesting will help me get to that end. I think you know a lot more than I do, so apparently it is just my understanding of your recommendation.


    As far as I am reading it, you are suggesting to total each month, then see if each month is less or greater than column M?

    Re: Sum columnA/sum columnB, if row Has data up to columnB


    Hiya,


    no on the contrary, you know much more about this than I do :=)


    I came up with that suggestion just by looking at your formula and the workbook you supplied. I don't know about your data. So from that, I see


    - that the formula is going line by line doing the same calculation many times, namely
    IF(SUM($N$13:W13)<1,0,$M$13)...


    ..which is a comparison of the amount paid to date against the promised amount.


    I don't see an easy way to simplify this formula that will reduce the calculation time. So, instead of doing this conditional test a hundred times, why not take it out of the formula and keep the result of the test somewhere else, ie in a different sheet?


    For example in your front sheet in cell P5, the formula performs the conditional test using the addition of the amounts paid from Jan to Mar (N18-P18).


    (Apologies if these are not the correct refs as I've opened this up on my netbook using xl2003 and it's converted it into read only)


    What you can do is create a replica of this worksheet; just copy and paste it and remove all the monthly amounts paid.


    Now, instead of $50 in cell P18 on the copied sheet, you have a cumulative total so that it would be Jan + Feb + Mar. ie $150.


    Now, you can copy the sheet again, or create space on the same sheet to do the <>M column test. This will mean that you have an actual cell where you have the result of the test rather than doing it all in one go in your megaformula.


    Once you have these intermediate results in actual cells, you can remove them from your megaformula entirely and reference the CELLS themselves on the copied sheet that compute the test, rather than doing the test ie.
    IF(SUM($N$13:W13)<1,0,$M$13)..in the cell itself


    Because the copied sheet will be exactly the same as the original, you can reference the cells and copy them down/across in the original sheet easily


    Basically you're just chopping up the original megaformula and performing the tests inside them in other cells on a copied sheet. You then reference these cells in the original formula which will turn your formula into a one liner rather than a one hundred liner

    Re: Sum columnA/sum columnB, if row Has data up to columnB


    converteds,


    Okay so I took some time to do it now. It looks like it is going to work, and this might help me with some of my other problems as well. After making the second page my new formula looks like this


    =((SUM(T13:T25))/(SUM(IF(Sheet2!T2:Sheet2!T25>0,$M$13:$M$25,0))))


    It is an array formula. I may have gone heavy on the () but I always add a few extra just in case.
    At least it appears to work and does it great. Now I just have to apply this new fun working with two worksheets into my other questions.


    Thank you again for your help,
    cya

    Re: Sum columnA/sum columnB, if row Has data up to columnB


    Good job! Yeah that would work. Just be mindful that hundreds of array formulas may also slow your workbook down. (no one said it was easy)


    Not a problem, glad to be of use :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login