 SOLVED

New Contributor

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

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

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

@FilmExcel Use SUMIF as demonstrated in the picture below. # Re: Can't seem to figure out a formula to do the following

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) # Re: Can't seem to figure out a formula to do the following

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 !!

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

This is perfect, thanks. I was over complicating it.

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

Thanks, replaced the A9 with “P” and O accordingly as JMB17 put it.
Works like a dream.

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

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 ???

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

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.

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

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%.