Pivot Tables show zero balance


R

rmm30

Hi - I have a pivot table....

I want to show customers that have an outstanding balance.

when i sort by month, i see everything, totals of who paid and who ha
not. I want to sort to see only who is outstanding.

How do I do this?

CURRENT SETUP:
Sort by page = date of invoice (by month)
Rows = client name, then the tota
 
Ad

Advertisements

D

Debra Dalgleish

You could add a Status column to your data source, and calculate the
account status. For example, with customer names in column C and amounts
in column H:

=IF(SUMIF($C$2:$C$39,C2,$H$2:$H$39)>0,"Outstanding","Paid")
Copy the formula down to all rows of data.

Refresh the pivot table, and add the new Status field to the column area.
From the dropdown list in the Status field button, remove the check
mark from Paid.
Hide the row Grand Totals.
 
R

rmm30

Thank you.

I must be an imbicil....

I tried the formula and it is not working.

The thinking behind the formula - so that I can understand the logic -
is please check for a client name in column C, if there is one there,
calculate what is in column H. If column H has data, return "paid".
If it is empty, return "outstanding". Do I follow this correctly.

Well, I tried that logic - adjusting the columns for my sheet - and it
returns either all Paid or all Outstanding.

Note: if a customer has not paid the cell is empty - no data.

Please help.

RMM
 
Ad

Advertisements

R

rmm30

OK, I got it to work! I changed the formula bit...

=IF(SUMIF(C3:C3000,C3,h3)>0,"Paid","Outstanding")

Thanks for your help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top