0

I've made a table that helps me manage outstanding payments due (i.e. money that I owe) to another company.

This table has two columns, the first entitled 'Total Due', the second entitled 'Paid?'. I have a few rows for invoice totals owed and whether or not they've been paid yet. A brief example is below (please forgive the terrible attempt at creating a table - this form is pathetically strict on HTML usage!):

|Total Due |---- Paid? --|

|-- £20 ----|---- (Blank) --|

|-- £40 ----|---- YES -----|

|-- £25 ----|---- (Blank) --|

|-- £32 ----|---- YES -----|

I want to only SUM the 'Total Due' payments that haven't been paid (the ones that have a corresponding '(Blank)' cell in the 'Paid?' column) - i.e. IF the corresponding cell in the 'Paid?' column has the text 'YES', don't include the 'Total Due' amount in the sum.

In this example, I first want the formula to check if any cells in the 'Paid?' column have the text 'YES' in them. As two of them do, I then want the formula to only SUM £20 and £25.

I've tried this formula so far, but as you can see, it's very long and repetitive, and doesn't fully work:

=IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES",E13="YES",E14="YES",E15="YES",E16="YES",E17="YES"),0,IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES",E13="YES",E14="YES",E15="YES",E16="YES"),D17,IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES",E13="YES",E14="YES",E15="YES"),SUM(D16,D17),IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES",E13="YES",E14="YES"),SUM(D15:D17),IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES",E13="YES"),SUM(D14:D17),IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES",E12="YES"),SUM(D13:D17),IF(AND(E7="YES",E8="YES",E9="YES",E10="YES",E11="YES"),SUM(D12:D17),IF(AND(E7="YES",E8="YES",E9="YES",E10="YES"),SUM(D11:D17),IF(AND(E7="YES",E8="YES",E9="YES"),SUM(D10:D17),IF(AND(E7="YES",E8="YES"),SUM(D9:D17),IF(E7="YES",SUM(D8:D17),SUM(D7:D17))))))))))))

As you can see, this formula only works as long as you enter 'YES' in order downwards (i.e. if you enter 'YES' in the first corresponding cell, THEN the second corresponding cell, etc.). If you leave the first corresponding cell blank then enter 'YES' in the second corresponding cell (for the second payment), the formula ignores it and SUMS all 'Total's Due'.

  • 1
    You need sumif support.office.com/en-us/article/… – Raystafarian Sep 16 '16 at 14:32
  • Thanks for your reply! I tried that: =SUMIF(E7:E17,"YES",D7:D17), but it does the opposite; it only SUMS the corresponding value IF there's a "YES" in the 'Paid?' cell. – James Peliby Sep 16 '16 at 14:36
  • 1
    is number text or a value with £ prefix in format. must be extracted if so – Sunnyskyguy EE75 Sep 16 '16 at 14:38
  • @Raystafarian that did it! Duh! I feel stupid now. Tried a variety of IFs, SUMs and SUMIFs but I think I was trying too hard! Also I assumed Excel would ignore the speech-marks if no text was entered. – James Peliby Sep 16 '16 at 14:49
  • Yeah, it's not great practice to use "" but we don't have many options – Raystafarian Sep 16 '16 at 14:52
0

sumif is what you want to use:

=SUMIF(E7:E17,"",D7:D17)
  • Err:508 in Open Office which uses semi-colons =SUMIF(Range; Criteria; SumRange) – Sunnyskyguy EE75 Sep 16 '16 at 15:11
  • @TonyStewart I didn't see an open office tag. – Raystafarian Sep 16 '16 at 15:14
  • I was trying to make it work in OO but couldn't even with ; it gave 0 result – Sunnyskyguy EE75 Sep 16 '16 at 15:42
  • OO worked with "YES" but not "".. quirks of compatibility – Sunnyskyguy EE75 Sep 16 '16 at 16:09

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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