3

We have a Google Sheet doc which contains a date column with each date as a row value, and also multiple columns (some of which have the same name) and we want to sum the values in the cells where the row is a specific date, and the column has a specific header. For example where the date is 01/03/2017 we want to sum all the values which have the column header "X" on that date. Can this be done?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Matt Scott Gibbs
  • 55
  • 1
  • 2
  • 6
  • Sample Data for those who need it below.... https://docs.google.com/spreadsheets/d/13XF0TbJqMZIhhJi_xQjN2PAK3ZHN0D549n7UdeomlWo/edit#gid=0 Many thanks, – Matt Scott Gibbs Mar 13 '17 at 11:04

3 Answers3

2

Yes it can be done

=SUMIF($C$3:$J$3,"X",OFFSET(C3:J3,MATCH(B1,B4:B15,0)+3,0))

Broken down

=sumif($C$3:$J$3 [<-header row with X],"X" [<-what we're looking for],C3:J3 [<-row to sum])

the formula above will sum the header row if there is an "X" (not very useful)

I used offset(C3:J3,[row number],0) with the header row range to push it down to the row matching 01/03/2017

To get the row number of 01/03/2017 I used Match() and put 01/02/2017 in cell B1

MATCH(B1,B4:B15 [range of dates] ,0)

I add 3 becuase my range starts at 4

You can hard code the date into the formula by replacing B1 with

DATEVALUE("01/03/2017")

I've not tried this in Google Sheets as I don't have access at the moment but it works in Excel and I'll try it in Sheets later.

Here's the formula that you can paste into A2 on your sheet "Sum of Data"

=SUMIF(Data!$B$1:$J$1,B$1,OFFSET(Data!$B$1:$J$1,MATCH($A2,Data!$A$2:$A,0),0))

It's all about changing the original formula to match your data and also locking the ranges correctly with the $ so that it will autofill down and across without breaking.

Gordon
  • 1,165
  • 1
  • 7
  • 12
  • Hi Gordon, many thanks for the answer, it looks like we are not far off, however I still can't quite get the formula to work correctly. Please see below the sheet I am trying to sum. https://docs.google.com/spreadsheets/d/13XF0TbJqMZIhhJi_xQjN2PAK3ZHN0D549n7UdeomlWo/edit#gid=0 We would like the cells in the "Sum of Data" to be filled in, based on the values in the "Data" tab. Would you be kind enough to provide your expertise on this problem. Many thanks, – Matt Scott Gibbs Mar 13 '17 at 10:51
  • I've updated my answer with a formula that works on your sheet and can be auto-filled down and across. – Gordon Mar 13 '17 at 11:32
  • Gordon my friend, thank you so much for your help. If ever you are in London, I owe you a pint! Have a great day and thanks once again! – Matt Scott Gibbs Mar 13 '17 at 11:45
0

Use INDEX to peel off the appropriate column for SUMIF.

=SUMIF(A:A, G4, INDEX(A:E, 0, MATCH(H4, 1:1, 0)))

enter image description here

  • As I understood the OP there will be multiple columns marked with `x`. I may be wrong. – Scott Craner Mar 10 '17 at 16:50
  • 1
    Just reread the question and I believe your interpretation is correct. Too bad no sample data (with expected results) was provided. As a matter of fact, it sounds like `01/03/2017` is unique. In that case, the OP needs to transpose this solution. –  Mar 10 '17 at 16:52
  • Hi all, I have created a sample data sheet for you to look into, thus far I am still having trouble. Any help is appreciated. Many thanks. https://docs.google.com/spreadsheets/d/13XF0TbJqMZIhhJi_xQjN2PAK3ZHN0D549n7UdeomlWo/edit#gid=0 – Matt Scott Gibbs Mar 13 '17 at 11:02
0

Considering a sheet where:

  • Cell B1 contains the date of interest (e.g. 01/03/2017)
  • Cell B2 contains the header of interest (e.g. X)
  • Cell B3 returns the sum of interest (values of all columns with header "X" on 01/03/2017)
  • Row 4 contains the headers to be evaluated (e.g. "Date", "A", "B", "X", "C", "X", "D")
  • Subsequent rows contain the actual data (e.g. dates in column A and data in columns B:G)

Refer to the image on the link below for details:
Example with cells of interest highlighted in yellow


The following formula should return the expected result:

=SUMIF(4:4,B2,INDEX(A:G,MATCH(B1,A:A,0),0))


I used Google Sheets in Portuguese. So, the formula actually tested was:

=SOMASE(4:4;B2;ÍNDICE(A:G;CORRESP(B1;A:A;0);0))

I hope that was usefeul.

Tadeu
  • 1
  • 1