# Include cell value in SUM only if corresponding cell has certain text value

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'.

• 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
• 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

``````=SUMIF(E7:E17,"",D7:D17)