Today's Excel Challenge...

matt · Oct 2, 2014

How can I distinguish between values in a column so that some are treated as paid and some as outstanding.

For example, if the bold values below are paid and the unbold are unpaid (and values are likely to switch from unpaid to paid or vice versa) how can I dynamically calculate the total of the paid and unpaid values?

Item 1 100.00
Item 2 500.00
Item 3 400.00
Item 4 100.00
Item 5 600.00
---------------
Total Unpaid xxxx.xx
Total Paid xxxx.xx

Ideally I would like to be able to easily and visually mark a value as paid or unpaid (cell or text colour maybe). I'm using Excel 2010 (Windows).

Any ideas?

Cheers

Buzzer · Oct 2, 2014

Use the SUMIF function and colour in the cells?

=SUMIF(A1:A5,"Blue",A1:A5) and =SUMIF(A1:A5,"Green",A1:A5)

EDIT - but easier would probably be to use another column with P and U and sumif based on those, I reckon.

matt · Oct 2, 2014

It's a pretty wide spreadsheet as it is, covering 15 months, so that may add to its unwieldiness. Any other options?

hans kraay fan club · Oct 2, 2014

matt said:
It's a pretty wide spreadsheet as it is, covering 15 months, so that may add to its unwieldiness. Any other options?

Highlighter pen?

KZNSeagull · Oct 2, 2014

Change the unpaid ones to negative numbers and use the SUMIF function?

Papak · Oct 2, 2014

Or change the colour of paid or unpaid, filter by text colour and use =subtotal(9,range) to add either the paid or unpaid.

essbee · Oct 2, 2014

Use R instead - a much more powerful and elegant freebie stats package.

Takes a bit of learning - but you'll never look back.

matt · Oct 2, 2014

Papak said:
Or change the colour of paid or unpaid, filter by text colour and use =subtotal(9,range) to add either the paid or unpaid.

Sooo, I would change the text colour of paid items to blue for example, and then in the formula where and how would I specify that I only want to include 'blue' values?

KZNSeagull · Oct 2, 2014

I think if you want to do it with BOLD and no formatting, you have to write a VBA function to do it. Give me 10 minutes and I will give it a go....

Papak · Oct 2, 2014

You filter by text colour (right click on a filtered column for the options) the subtotal formula using 9 only sums the currently filtered for rows.

I can make a mock up and stick it on dropbox if you want.

Papak · Oct 2, 2014

Here is an example

https://dl.dropboxusercontent.com/u/314207/Sum of filtered cells.xlsx

matt · Oct 2, 2014

Papak said:
Here is an example

https://dl.dropboxusercontent.com/u/314207/Sum of filtered cells.xlsx

Thank you very much. Ideally I would like to keep all the values visible rather than filtering them - is that possible with this approach?

Gazwag · Oct 2, 2014

hans kraay fan club said:
Highlighter pen?

I find that's ok until you scroll down then you have wipe the screen and start again

KZNSeagull · Oct 2, 2014

OK, this works in Excel 2000. I haven't got 2010, but I don't see why it will not work in that:

Open up the Visual Basic Editor (Alt F11) and insert a new module:

Function SumIfBold(MyRange As Range) As Double

Dim cell As Range
For Each cell In MyRange
If cell.Font.Bold = True Then
SumIfBold = SumIfBold + cell
End If
Next cell

End Function

In your spreadsheet, in an empty cell put =SumIfBold(A1:A5) replacing A1:A5 with your range of cells.

You will probably then have to save the spreadsheet and reopen it. If it asks you to Enable Macros, agree and then your cell should have the total of the bold cells. I don't think the cell will automatically update if you change something to bold, so you may have to go back to the cell with the call to the function and pretend to edit the call to the function and it will recalculate it then.
For non bold cells, you can create the total of all cells minus the total of bold.

Papak · Oct 2, 2014

matt said:
Thank you very much. Ideally I would like to keep all the values visible rather than filtering them - is that possible with this approach?

No but you could duplicate the sheet in another tab and have 1 displaying the paid total and the other the unpaid these values could then be displayed together on a 3rd sheet.

Ousethe VBA solution above assuming that it works.

matt · Oct 2, 2014

KZNSeagull said:
OK, this works in Excel 2000. I haven't got 2010, but I don't see why it will not work in that:

Open up the Visual Basic Editor (Alt F11) and insert a new module:

Function SumIfBold(MyRange As Range) As Double

Dim cell As Range
For Each cell In MyRange
If cell.Font.Bold = True Then
SumIfBold = SumIfBold + cell
End If
Next cell

End Function

In your spreadsheet, in an empty cell put =SumIfBold(A1:A5) replacing A1:A5 with your range of cells.

You will probably then have to save the spreadsheet and reopen it. If it asks you to Enable Macros, agree and then your cell should have the total of the bold cells. I don't think the cell will automatically update if you change something to bold, so you may have to go back to the cell with the call to the function and pretend to edit the call to the function and it will recalculate it then.
For non bold cells, you can create the total of all cells minus the total of bold.

Ooooooh cool - thank you. If I wanted to use blue text rather than bold as the variable, what would I change? Also, this doesn't change on the fly as such, is there any way to refresh all the formulae in the file? Obviously you may have other things to do....

KZNSeagull · Oct 2, 2014

matt said:
Ooooooh cool - thank you. If I wanted to use blue text rather than bold as the variable, what would I change? Also, this doesn't change on the fly as such, is there any way to refresh all the formulae in the file? Obviously you may have other things to do....

You can change the bit that says If cell.Font.Bold = True Then to If cell.Font.ColorIndex = <the number of the colour you want to test> Then

Colour numbers are here: http://msdn.microsoft.com/en-us/library/office/ff840443(v=office.15).aspx

So for black, the code would read If cell.Font.ColorIndex = 1 Then.

This does not appear to work if the text colour is automatic, by the way. You would have to change those to black first. You would have to have a seperate function for each colour, as to find the colour in a cell first complicates matters (but is not impossible). If you want that, I will have to play with that one.

No, it will not change it on the fly as you are not actually changing the value in the cell, so the function will not be called. To get it to recalculate the cell, click on the cell with the formula in, place your cursor up in the formula line at the top of the spreadsheet, then press tab right, and the cell should be recalculated (that's what I meant by pretend to edit the formula)

matt · Oct 2, 2014

View attachment 59118

Buzzer · Oct 2, 2014

KZNSeagull said:
No, it will not change it on the fly as you are not actually changing the value in the cell

If you add the line

application.volatile

at the beginning of the function then every time you press F2 and return it will refresh.....

...I think.

Buzzer · Oct 2, 2014

Buzzer said:
If you add the line

application.volatile

at the beginning of the function then every time you press F2 and return it will refresh.....

...I think.

I think wrong. The latter is no different from KZN's edit function and the former doesn't get recognised by the application.volatile line. I'll crawl back in my hole.

Got something to say or just want fewer pesky ads? Join us... 😊

Today's Excel Challenge...

Well-known member

Languidly Clinical

Well-known member

The voice of reason.

Well-known member

Not an NSC licker...

New member

Well-known member

Well-known member

Not an NSC licker...

Not an NSC licker...

Well-known member

5 millionth post poster

Well-known member

Not an NSC licker...

Well-known member

Well-known member

Well-known member

Languidly Clinical

Languidly Clinical

Share this page