Search code examples
google-sheetssumformula

Is there a Google Sheets formula to produce a "conditional" sum spanning multiple columns of a spreadsheet?

I have a Spreadsheet that Lists total items by specific people...

pic1

I am trying to set up a system where you can select certain people (by using checkboxes), and it lists the total number of items for the selected people....

pic2

Which Outputs:

pic3

I currently have it set up so that the people that you mark the checkbox for get put in a vertical column.... (I tried this to help with different formula parameters, but don't know if I really need to)

Alex
Charlie
Erika

From what I can tell =DSUM is closest to what I am trying to do, but I don't now the proper syntax for what I am trying, or if I should restructure my data to work better with this formula.

Solution

  • Formula:

    =SUMIF($B$2:$F$2, TRUE, B3:F3)

    Parameters:

    $B$2:$F$2 - location of the checkboxes

    TRUE - will include in the sum if checkbox is ticked

    B3:F3 - range of the specific item

    Note:

    • Just drag the formula on all rows.

    Output:

    output

    Or when using the transposed data:

    Formula:

    =sumif($B$2:$B, TRUE, C2:C)

    Parameters:

    $B$2:$B - location of the checkboxes

    TRUE - will include in the sum if checkbox is ticked

    C2:C - range of the specific item

    Output:

    output

    Note:

    • Just drag the formula on all columns.