2

I'm using Excel for the accounting purposes of a group venture, to keep track of our expenses, costs, payments, and balances.

  • Details of each expense are stored in columns A:J
  • Total costs and balances are stored in columns K:M
  • Payments and dates are stored in columns N:S

enter image description here

Generally speaking, I'm responsible for 34% of the cost of most expenses, whereas each other party is responsible for 22% of the cost of most expenses. This is the case for the majority of expenses.

However, there are a few expenses that I would like to split only between specific parties – J2, J9, and J10 – which are emphasized by a green, bold font in the screenshot above.

Understanding the parties column:

  • 1111 (me, alex, nan, nate)
  • 1100 (me, alex)
  • 1000 (me)
  • 0101 (alex, nate)
  • et cetera

I'm not sure how to go about this, therefore I have a few questions:

  1. Is there a formula to ignore certain values in a row based on other values in the same row? For example, is there a way for the collective total cost formula, =SUM(H:H), to ignore H2, H9, and H10, other than something like =SUM(H3:H8,H11:H99999)?

  2. Conversely, is there a formula to select every value in the H column whose value in the J column is equal to 1100 for example?

3

You can use either of the formulas,

=SUMIF(J:J,"1100",H:H)

or even this one

=SUMPRODUCT(($J$2:$J$17="1100")*($H$2:$H$17))

Adjust cell references in the formula as needed.

  • I will test tomorrow when I wake up and get back to you! – oldboy Sep 28 '19 at 6:41
  • Thanks a lot for your help man! – oldboy Sep 28 '19 at 18:42
  • Glad to help you @BugWhisperer ,, keep asking ☺ – Rajesh S Sep 30 '19 at 6:11
  • do you know if in Excel 2010 there is any way to sum amounts in a column if the color of the font is a particular color? Or just do things in general based on the color of text? – oldboy Oct 1 '19 at 22:25
  • Yes I do @BugWhisperer,, on the basic of Font/Color & even back ground color SUM as well as few other functions can be used . – Rajesh S Oct 2 '19 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.