Search code examples
google-sheetsgoogle-sheets-formulaspreadsheetarray-formulassumifs

Adding or subtracting values based on a vector of check boxes in Google Sheets

I hope you can help me with this:

I'm trying to create a savings-control sheet where I list my monthly payment and I'm trying to use the SUMIF formula to subtract my expenses by selecting what I have currently payed but I don't know if this may work with a vector of check boxes Sheets sample

the current formula as you can see in the image works fine but only for column D however if I check the rest of the boxes nothing is subtracted

This is how the formula looks like now: =A31+A32-SUMIF(D3:J14,TRUE,C3:C14) however only works from D3 to D14 and I need it to work from D3 to J14

Any help will be highly appreciate

Solution

  • I think the simplest solution is:

    =A31+A32-SUM(ARRAYFORMULA(N(D3:J14)*C3:C14))

    Formula rundown

    This formula is based on the function N that converts a boolean to an integer (1 for true, 0 for false). We can then multiply by the expense value. Here an example:

    =N(D3)*C3

    This will equal C3 iff D3 is checked.

    Having that we can make the entire table with ARRAYFORMULA:

    =ARRAYFORMULA(N(D3:J14)*C3:C14)

    Now we can sum all the values to have the total expenses:

    =SUM(ARRAYFORMULA(N(D3:J14)*C3:C14))

    Add the other cells and you get your result.

    References