CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheetarray-formulassumifs
SecaidaDevStudent
SecaidaDevStudent

Reputation: 95

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

Upvotes: 2

Views: 639

Answers (2)

Martí
Martí

Reputation: 2861

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

Upvotes: 1

JPV
JPV

Reputation: 27262

Try

=A31+A32-sumproduct((countif(if(D3:J14, row(D3:D14)), row(D3:D14))>0),C3:C14)

and see if that helps?

Upvotes: 0

Related Questions