SOLVED

Can't seem to figure out a formula to do the following

%3CLINGO-SUB%20id%3D%22lingo-sub-2148149%22%20slang%3D%22en-US%22%3ECan't%20seem%20to%20figure%20out%20a%20formula%20to%20do%20the%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148149%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20seem%20to%20figure%20out%20the%20formula%20I%20need%2C%20let%20alone%20how%20to%20execute%20it.%3C%2FP%3E%3CP%3EI%20have%20a%20table%20which%20contains%20something%20such%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20amount%20amount%20amount%26nbsp%3B%26nbsp%3B%26nbsp%3B%20total%20%26nbsp%3B%20%26nbsp%3B%20%25%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Paid%20%2FOwing%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24300%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24400%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24900%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2015%25%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20P%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24600%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010%25%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20O%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%241000%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24600%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24500%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%242100%26nbsp%3B%26nbsp%3B%26nbsp%3B%2035%25%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20P%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%241000%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%241000%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24400%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%242400%26nbsp%3B%26nbsp%3B%26nbsp%3B%2040%25%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20O%3C%2FP%3E%3CP%3E%3CSTRONG%3ETotal%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%242500%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%242200%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%241300%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%246000%26nbsp%3B%26nbsp%3B%26nbsp%3B%20100%25%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPercentage%20of%20Paid%20%3D%2050%25%3C%2FP%3E%3CP%3EPercentage%20of%20Owing%20%3D%2050%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20a%20formula%20for%20the%20percentage%20paid%20and%20percentage%20owing.%3C%2FP%3E%3CP%3EI%20tried%20to%20use%20COUNTIFS%20to%20count%20the%20number%20of%20Ps%20and%20Os%2C%20but%20couldn't%20figure%20a%20way%20to%20relate%20them%20to%20the%20cell%20adjacent%20(%25)%20to%20them.%3C%2FP%3E%3CP%3EIt%20seems%20I%20need%20an%20IFS%20as%20well.%20I%20am%20completely%20lost%20on%20how%20to%20do%20this.%3C%2FP%3E%3CP%3EAppreciate%20any%20help%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2148149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148367%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20seem%20to%20figure%20out%20a%20formula%20to%20do%20the%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F971704%22%20target%3D%22_blank%22%3E%40FilmExcel%3C%2FA%3E%26nbsp%3BUse%20SUMIF%20as%20demonstrated%20in%20the%20picture%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-02-19%20at%2007.41.59.png%22%20style%3D%22width%3A%20554px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255969i22C0E16B5A6AF277%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-02-19%20at%2007.41.59.png%22%20alt%3D%22Screenshot%202021-02-19%20at%2007.41.59.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148369%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20seem%20to%20figure%20out%20a%20formula%20to%20do%20the%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F971704%22%20target%3D%22_blank%22%3E%40FilmExcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20the%20SUMIF%20function.%20Let's%20say%20%25%20is%20in%20column%20F%20and%20paid%2Fowing%20is%20in%20column%20G%2C%20then%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(G2%3AG5%2C%22P%22%2CF2%3AF5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1613717027517.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255970iF2204D5F87063472%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1613717027517.png%22%20alt%3D%22JMB17_0-1613717027517.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148378%22%20slang%3D%22en-US%22%3ERe%3A%20Can't%20seem%20to%20figure%20out%20a%20formula%20to%20do%20the%20following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148378%22%20slang%3D%22en-US%22%3EI'm%20unable%20to%20simplify%20that%20how%20you%20get%20P%20%26amp%3B%20O%2C%20if%20considering%20Percentage%20of%20Paid%20%3D%2050%25%20and%20Percentage%20of%20Owing%20%3D%2050%25%2C%20none%20of%20the%20cells%20meets%20this%20criteria%2C%2C%20also%20write%20that%20you%20are%20considering%20%25%20of%20which%20for%20P%20%26amp%3B%20O%2C%2C%20ROW%20or%20COLUMN%20total%20!!%3C%2FLINGO-BODY%3E
New Contributor

Hello all,

 

I can't seem to figure out the formula I need, let alone how to execute it.

I have a table which contains something such as:

 

             amount amount amount    total     %      Paid /Owing

             $300      $400      $200      $900      15%     P

             $200      $200      $200      $600      10%     O

             $1000    $600      $500      $2100    35%     P

             $1000    $1000    $400      $2400    40%     O

Total    $2500    $2200   $1300   $6000  100%

Percentage of Paid = 50%

Percentage of Owing = 50%

 

I want a formula for the percentage paid and percentage owing.

I tried to use COUNTIFS to count the number of Ps and Os, but couldn't figure a way to relate them to the cell adjacent (%) to them.

It seems I need an IFS as well. I am completely lost on how to do this.

Appreciate any help with this.

9 Replies
best response confirmed by FilmExcel (New Contributor)
Solution

@FilmExcel Use SUMIF as demonstrated in the picture below.

Screenshot 2021-02-19 at 07.41.59.png

@FilmExcel 

 

Try the SUMIF function. Let's say % is in column F and paid/owing is in column G, then

 

=SUMIF(G2:G5,"P",F2:F5)

 

JMB17_0-1613717027517.png

 

I'm unable to simplify that how you get P & O, if considering Percentage of Paid = 50% and Percentage of Owing = 50%, none of the cells meets this criteria,, also write that you are considering % of which for P & O,, ROW or COLUMN total !!
This is perfect, thanks. I was over complicating it.
Thanks, replaced the A9 with “P” and O accordingly as JMB17 put it.
Works like a dream.
The million dollar question is that how OP is getting P & O in column G ,, what the mechanism behind, otherwise getting % for both is not a big issue!!

@Rajesh-S 

Surely whether an amount has been paid or not will be a matter of external fact and, as such, is input data rather than a calculation within Excel.

That's correct. The example I provided is a bit bad since it provides 50% for each category. But what I wanted was to enter either letter manually on a big budget sheet and then see the percentage of owed and paid.

As such, it'll be easier to calculate a percentage by having a letter to differentiate between each row when it was paid or still owing. So that with time, as we manually updated the Os to Ps, until the Paid is 100% and Owed is 0%.