CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tmel
Tmel

Reputation: 1

Sum totals based on column name?

I have a Google Sheet which figures are manually entered in to in order to record activity across a variety of sites. The sheet contains a date column with each date as a row value. It then has three columns (Total tests, Total positives, Total voids) which are repeated for each site. There are 10 sites (which will grow) so for not there are 10 total tests, 10 total positives, 10 total void columns.

What I would like to do is sum a total based on the column name so that each day, it is automatically adding the total tests across all the sites, the total positives and total voids.

I tried using a SUM calculated field on a pivot table but when I did this, using 'Total tests', it only picked up the first column with this name and did not sum all of the columns with that name.

Is there a way using a pivot table (or alternative means) to sum the daily totals for those columns with the same name?

Upvotes: 0

Views: 304

Answers (1)

player0
player0

Reputation: 1

your sheet has a predictive layout (eg. each site has always 3 columns) so you can sue MOD of 3 to always get every 3rd column and sum them:

AH3:

=INDEX(MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF), 3)=0), SEQUENCE(COLUMNS(
               FILTER(C3:AF, MOD(COLUMN(C:AF), 3)=0)), 1, 1, 0)))

AI3:

=INDEX(MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF)-1, 3)=0), SEQUENCE(COLUMNS(
               FILTER(C3:AF, MOD(COLUMN(C:AF)-1, 3)=0)), 1, 1, 0)))

AJ3:

=INDEX(MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF)-2, 3)=0), SEQUENCE(COLUMNS(
               FILTER(C3:AF, MOD(COLUMN(C:AF)-2, 3)=0)), 1, 1, 0)))

enter image description here

to remove zeros you can do this in AH3:

=INDEX(IFERROR(1/(1/MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF), 3)=0), SEQUENCE(COLUMNS(
                            FILTER(C3:AF, MOD(COLUMN(C:AF), 3)=0)), 1, 1, 0)))))

update:

=ARRAYFORMULA(QUERY(QUERY({IF(A3:A<>"",ISOWEEKNUM(A3:A),),
 MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF),   3)=0), SEQUENCE(COLUMNS(
         FILTER(C3:AF, MOD(COLUMN(C:AF),   3)=0)), 1, 1, 0)), 
 MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF)-1, 3)=0), SEQUENCE(COLUMNS(
         FILTER(C3:AF, MOD(COLUMN(C:AF)-1, 3)=0)), 1, 1, 0)),
 MMULT(1*FILTER(C3:AF, MOD(COLUMN(C:AF)-2, 3)=0), SEQUENCE(COLUMNS(
         FILTER(C3:AF, MOD(COLUMN(C:AF)-2, 3)=0)), 1, 1, 0))}, 
 "select Col1,sum(Col2),sum(Col3),sum(Col4) where Col2 > 0 group by Col1"), 
 "offset 1", 0))

enter image description here

Upvotes: 1

Related Questions