Google-sheets – Can’t figure out SUM formula

google sheets

This is the formula I am trying to do:

=SUM(D9:D14)*2.55,
    (D16:D23)*3.90,
    (D25:D32)*6.40,
    (D34:D41)*10.60, 
    (D43:D46)*10.50,
    (D48:D51)*6,
    (D53:D59)*3,
    (D61:D64)*1.8,(D66:D69)*6

I need the sum of each set * different amounts and totaled on one line.

What am I doing wrong?

Best Answer

I believe this should also work

=SUM(SUM(D9:D14)*2.55,SUM(D16:D23)*3.90,SUM(D25:D32)*6.40,SUM(D34:D41)*10.60,SUM(D43:D46)*10.50,SUM(D48:D51)*6,SUM(D53:D59)*3,SUM(D61:D64)*1.8,SUM(D66:D69)*6)

Related Solutions

Google Sheets – Sum If Date is in Range

Try this formula for B1, copy-paste (or drag across) to other cells in column B:

=SUM(FILTER(D$1:D; A1 > C$1:C))

The FILTER includes only values from column D which have a corresponding value in column C which is lower than A1. So for row 1, this would mean =SUM(FILTER(D$1:D; 2013-03-02 > C$1:C)).

When copying this formula to the other rows, A1 should be automatically replaced with Ax, where X is the row number.

I have created an example spreadsheet to demonstrate.

Note that I use ; in the formula. Depending on your locale settings for Google Spreadsheets, you might have to replace ; with ,.

Google-sheets – How to collect the sum of different columns while filtering

You should be using a pivot table (select A:O, Data > Pivot table report... and add fields of your choice).

Maybe Primary Attendee in Rows, Month in Columns, Fee in Values and State in Filter - but the options are almost limitless (that's the power of a pivot table).

Best Answer

Related Solutions

Google Sheets – Sum If Date is in Range

Google-sheets – How to collect the sum of different columns while filtering

Related Topic