2

I get this daily data consumption spreadsheet report from a vendor that looks like this

userid    feb1    feb2    feb3     .    feb29 
u1        100     34      23       .     4
u2        0       24      21             62
u3        300     25      5              1
u4        50      5       6      
.
.
un        23      52      3        .     42

where n is my total number of users.

What I care about is simply tracking the daily consumption of all users.. so my final sheet should look like this

date        daily consumption
feb1        14,971     
feb2        6,898       
feb3        10,666     
.
.

feb29        10,543     

Currently I'm doing this by writing this in each line in my final sheet, for example to get the 14,971 for feb1 I'm putting

=sum(importrange("<sheet_ref>","<sheet_name>!I2:I"))

Naturally this is very manual and slow work. I want to know how to do this using a single formula or pivot table etc. I tried using array formulas, queries, pivot tables but I keep on getting stuck. Any suggestions?

Appendix 1: sample data

Here is a sample of the raw data we have from our vendor:

enter image description here

And here is a sample of the sheet that calculates the totals:

enter image description here

4
  • This can be done. However, there are too many unknowns currently. Please share links to both sheets (or copies of them, connected with IMPORTRANGE to simulate your actual sheet setup, e.g., number of columns to import, etc.). Be sure to set the links' Share permissions to "Anyone with the link..." and "Editor."
    – Erik Tyler
    Feb 27 at 19:48
  • @ErikTyler I updated the question with the sample data you requested
    – abbood
    Feb 27 at 20:50
  • Both sheets allow "View Only" access, so no one can get into them to use the data. And copying them would break the IMPORTRANGE. Also, I notice that your dates in the raw sheet begin at 1/2 while your master sheet shows starting at 1/1. Will there be missing dates in the raw sheet? How many maximum columns will you be trying to bring over to the master at a time? 31? Will the data for the first of the month always be in raw sheet Column B? (As you see, there are still many unknowns). Please change your Share permission per my first comment to "Anyone with the link..." and "Editor." Thanks.
    – Erik Tyler
    Feb 28 at 1:38
  • @ErikTyler i gave edit access to both and fixed the date error. As for your questions, feel free to make any assumption you would like
    – abbood
    Feb 28 at 5:09
1

Try the following

=TRANSPOSE({IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!B1:H1"); 
           {INDEX(QUERY({IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!B1:H")}, 
                     "select "&JOIN(", ","sum(Col"&SEQUENCE(7)&")")&" where Col1 is not null
                      group by ' ' ",1),2)}})

OR maybe this one

={SEQUENCE(7,1,"1/2/2021",1), 
           TRANSPOSE(INDEX(QUERY(IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!B1:H"), 
                     "select "&JOIN(", ","sum(Col"&SEQUENCE(7)&")")&" where Col1 is not null
                      group by ' ' ",1),2))}

(in this 2nd formula, 7 is the number of columns B:H)

enter image description here

Functions used:

6
  • it worked! amazing!!!
    – abbood
    Feb 28 at 5:12
  • Glad I could help :) Feb 28 at 5:13
  • is there a preference to either? what's the advantage of the second way over the first?
    – abbood
    Feb 28 at 5:22
  • 1
    It is shorter and you avoid the double import. Feb 28 at 5:24
  • I was looking up sequence documentation. For the third parameter it says the number to start the sequence from.. how did it work with a date string? Ie “1/2/2021”?
    – abbood
    Feb 28 at 8:10
1

My solution does not refer to your existing sheets at all. I've added two sheets ("Master" and "Erik Help") to your sample spreadsheet, both tabs highlighted with bright green. The "Master" sheet is hidden (which I would recommend, because you won't need it in daily dealings. You can see it and Unhide it if you like by clicking the icon of three stacked bars at the bottom left of the spreadsheet.

When any more than one cell of data will be used in conjunction with IMPORTRANGE, I nearly always recommend importing the entire data set from the external sheet to its own sheet in the processing spreadsheet, then referring to that full data sheet (rather than IMPORTRANGE formulas) for anything you need to do in the processing spreadsheet.

Note the structure of the IMPORTRANGE formula in Master!A1:

=IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!A1:"&ROWS(IMPORTRANGE("1JwbcOmfoRgc4s4OVN4poQ18KLWbqY2tZ22NA07ZsZIg","Sheet1!A:A")))

This is flexible, in that it brings in "everything," even if you add rows or columns to the external sheet.

Then, in the "Erik Help" sheet, I refer to that hidden Master sheet only, since it now contains all the data I need.

The formula in "Erik Help," cell A1, is similarly written to be flexible — contracting or expanding as the Master data set contracts or expands. For instance, as you continue to add more days' data to the external sheet, both of my formulas will "keep up" with that, without the need to adjust them in order to continue getting updated results. Here is the 'Erik Help'!A1 formula:

=ArrayFormula(TRANSPOSE({FILTER(Master!B1:1,Master!B1:1<>"");MMULT(SEQUENCE(1,ROWS(Master!A2:A),1,0),FILTER(IF(ISNUMBER(INDIRECT("Master!B2:"&ROWS(Master!A:A))),INDIRECT("Master!B2:"&ROWS(Master!A:A)),0),Master!B1:1<>""))}))

This will produce a TRANSPOSEd version of the following array:

A FILTERed array consisting of any cells in Master!B1:1 that are not blank (i.e., that contain dates). This allows that range to expand and contract.

An MMULT ("Matrix Multiplication") calculation of a FILTERed array of all the numbers from B2 over where B1:1 is not blank.

I recommend you study MMULT; while difficult for many to wrap their head around initially, it's quite powerful. My short notes:

  1. Every element in the MMULT grid must be a number (which is why you see my formula checks ISNUMBER and assigns a value of 0 to anything that isn't already a number.

  2. MMULT multiplies one matrix (i.e., grid) by another. That is, it multiplies every value per row of the first matrix by every value per column of the second and then adds up all these values. (If you think about it, that's exactly what you are trying to do by "adding up every separate column of values.")

  3. Therefore, the number of columns in the first matrix must be equal to the number of rows in the second matrix (or vice versa). By setting one of those matrices to a long column or row consisting only of repeated 1's, you are actually adding, because anything multiplied by 1 is itself.

Here is a graphic I prepared that might better explain MMULT and what it does:

enter image description here

I do want to point out that your dates are perhaps not representing what you think they do currently. It seems to me that you think your dates are currently representing the dates of the first week of February. In fact, they are representing the second day of each month from January to July. That is, your date format (perhaps for your region) is showing d/m/yyyy format rather than m/d/yyyy. That is something you'll need to correct at the source (by writing your dates in m/d/yyy format). Otherwise, you'll begin to see real problems as soon as February 13 is added, because 13 cannot be translated to a month, and so the values from there to the end of each month will show as a string instead of a real date.

2
  • Hi Erik I got a problem. How can I enforce displaying the dates in a certain format using your formula? right now the dates appear like this (b/c this is how they are on the original sheet): 10/3/2021 11/3/2021 12/3/2021 13-03-2021 14-03-2021 15-03-2021 whereas I would like them all to be in the DD/MM/YYYY format
    – abbood
    Mar 24 at 10:41
  • Follow-up question from @abbood: webapps.stackexchange.com/q/153128/88163
    – Rubén
    Mar 30 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.