2

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

SecaidaDevStudent
  • 95
  • 1
  • 10

2 Answers2

1

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

Martí
  • 2,651
  • 1
  • 4
  • 11
0

Try

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

and see if that helps?

JPV
  • 26,499
  • 4
  • 33
  • 48