SOLVED

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

Copper 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 (Copper 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_Sinha 

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%.
1 best response

Accepted Solutions
best response confirmed by FilmExcel (Copper Contributor)
Solution

@FilmExcel Use SUMIF as demonstrated in the picture below.

Screenshot 2021-02-19 at 07.41.59.png

View solution in original post