How do you calculate income and expense ,where the income is one color and the expense is red in the same colum?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Calculating income and expense
0
Answers
0
Hi Jentz
If the expense figures are red due to the cell format (so expenses appear as red though if you look at the cell, it's a negative number and says - 1.50 say, but positive values are automatic/black), then you can use the worksheet function SUMIF as follows:
If you want to sum the red (negative) numbers in the range B2:B15 (you should change this in your formula), use this:
=SUMIF(B2:B15,"<0")To sum the black (positive numbers), use this:
=SUMIF(B2:B15,">=0")If you want to check this, then put =SUM(B2:B18) in another cell; the black SUMIF number less the red SUMIF number should equal that SUM.
If the numbers aren't red through normal formatting (seems unlikely), then a VBA solution would be needed (e.g. a User Defined Function)
Discussion
Well rigt now I use a seperate colum for expense and quantity another for income and quantity then I have a seperat colum for each calculation to get my end balance , but I would perfer to have only 1 colum for both income + expense and then another for the quantities, and then in 1 colum it would show the total income or expense in that row
Jentz Dec 7, '20 at 1:15 pm
Okay Jentz. So does the SUMIF solution work for you (given it requires you to enter expenses as negatives and format the colum for currency but with negative numbers showing as red, without the sign, if you prefer)?
John_Ru (rep: 6142) Dec 7, '20 at 1:41 pm
If not, I've created a UDF which should work but you need to be comfortable with using a macro-enabled workbook
John_Ru (rep: 6142) Dec 7, '20 at 1:43 pm
Jentz, please comment on my posts above.
John_Ru (rep: 6142) Dec 10, '20 at 6:28 am