Filter by Date range and sum Row values by Column (in Google Sheets)










1















Do you have a suggestion on how to make a formula for the problem written in the title?



Here is the example so that you can get an idea of the data format:



Name,Location,Category,Total views for your listing,Total uniqe page views for your listing,Average time on page (listing),total,Facebook,Reviews,Telephone,Treatment Menu,Get Directions,Book Appointment,Enquiry,Instagram,Date
John,NSW Sydney,Apple,1,1,0:00:12,0,0,0,0,0,0,0,0,0,13/Nov/2018
Mike,NSW Sydney,Orange,1,1,0:00:10,0,0,0,0,0,0,0,0,0,13/Nov/2018
Kenny,NSW Sydney,Pear,1,1,0:00:00,4,1,1,1,1,0,0,0,0,13/Nov/2018
John,NSW Sydney,Apple,20,1,0:00:12,0,0,0,0,0,0,0,0,0,14/Nov/2018
Chris,NSW Sydney,Orange,1,1,0:02:48,3,0,0,1,1,1,0,0,0,14/Nov/2018
Stef,NSW Sydney,Orange,10,1,0:07:22,6,1,1,0,1,0,1,1,1,14/Nov/2018


So, the issue is in making a formula that gets put into cells containing numerical values (from D to O), for each of the Name rows.



What it should look like is:



  • for John and for 13th and 14th of November, the cell in column B in that row should be 21

  • for Mike for 13th and 14th, it returns 1

  • for Jenny, which is not on the list, because she didn't have any values in any of the numerical columns for that day (or a group of days), it should return 0

All of these should appear in the "Main Dashboard" sheet, while the data is stored in the "directory statistics" sheet.



The predefined list of all the possible Names, Categories and Locations is in the "customList" sheet. This means that we don't need to search through the whole dataset of all the values for all the dates, but rather only search through the Names in the "customList". "Name" should be the key value that connects values in different dates.



So far, I have tried with this formula:
=Filter('directory statistics'!A2:A,'directory statistics'!$P2:$P=$P2)
It looks for the name column (A) and then returns the value if the date entered (in P2) matches the value in that sheet for the date column (P)



And then I got stuck! :)



Thank you!










share|improve this question
























  • Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

    – jdv
    Nov 14 '18 at 17:31















1















Do you have a suggestion on how to make a formula for the problem written in the title?



Here is the example so that you can get an idea of the data format:



Name,Location,Category,Total views for your listing,Total uniqe page views for your listing,Average time on page (listing),total,Facebook,Reviews,Telephone,Treatment Menu,Get Directions,Book Appointment,Enquiry,Instagram,Date
John,NSW Sydney,Apple,1,1,0:00:12,0,0,0,0,0,0,0,0,0,13/Nov/2018
Mike,NSW Sydney,Orange,1,1,0:00:10,0,0,0,0,0,0,0,0,0,13/Nov/2018
Kenny,NSW Sydney,Pear,1,1,0:00:00,4,1,1,1,1,0,0,0,0,13/Nov/2018
John,NSW Sydney,Apple,20,1,0:00:12,0,0,0,0,0,0,0,0,0,14/Nov/2018
Chris,NSW Sydney,Orange,1,1,0:02:48,3,0,0,1,1,1,0,0,0,14/Nov/2018
Stef,NSW Sydney,Orange,10,1,0:07:22,6,1,1,0,1,0,1,1,1,14/Nov/2018


So, the issue is in making a formula that gets put into cells containing numerical values (from D to O), for each of the Name rows.



What it should look like is:



  • for John and for 13th and 14th of November, the cell in column B in that row should be 21

  • for Mike for 13th and 14th, it returns 1

  • for Jenny, which is not on the list, because she didn't have any values in any of the numerical columns for that day (or a group of days), it should return 0

All of these should appear in the "Main Dashboard" sheet, while the data is stored in the "directory statistics" sheet.



The predefined list of all the possible Names, Categories and Locations is in the "customList" sheet. This means that we don't need to search through the whole dataset of all the values for all the dates, but rather only search through the Names in the "customList". "Name" should be the key value that connects values in different dates.



So far, I have tried with this formula:
=Filter('directory statistics'!A2:A,'directory statistics'!$P2:$P=$P2)
It looks for the name column (A) and then returns the value if the date entered (in P2) matches the value in that sheet for the date column (P)



And then I got stuck! :)



Thank you!










share|improve this question
























  • Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

    – jdv
    Nov 14 '18 at 17:31













1












1








1








Do you have a suggestion on how to make a formula for the problem written in the title?



Here is the example so that you can get an idea of the data format:



Name,Location,Category,Total views for your listing,Total uniqe page views for your listing,Average time on page (listing),total,Facebook,Reviews,Telephone,Treatment Menu,Get Directions,Book Appointment,Enquiry,Instagram,Date
John,NSW Sydney,Apple,1,1,0:00:12,0,0,0,0,0,0,0,0,0,13/Nov/2018
Mike,NSW Sydney,Orange,1,1,0:00:10,0,0,0,0,0,0,0,0,0,13/Nov/2018
Kenny,NSW Sydney,Pear,1,1,0:00:00,4,1,1,1,1,0,0,0,0,13/Nov/2018
John,NSW Sydney,Apple,20,1,0:00:12,0,0,0,0,0,0,0,0,0,14/Nov/2018
Chris,NSW Sydney,Orange,1,1,0:02:48,3,0,0,1,1,1,0,0,0,14/Nov/2018
Stef,NSW Sydney,Orange,10,1,0:07:22,6,1,1,0,1,0,1,1,1,14/Nov/2018


So, the issue is in making a formula that gets put into cells containing numerical values (from D to O), for each of the Name rows.



What it should look like is:



  • for John and for 13th and 14th of November, the cell in column B in that row should be 21

  • for Mike for 13th and 14th, it returns 1

  • for Jenny, which is not on the list, because she didn't have any values in any of the numerical columns for that day (or a group of days), it should return 0

All of these should appear in the "Main Dashboard" sheet, while the data is stored in the "directory statistics" sheet.



The predefined list of all the possible Names, Categories and Locations is in the "customList" sheet. This means that we don't need to search through the whole dataset of all the values for all the dates, but rather only search through the Names in the "customList". "Name" should be the key value that connects values in different dates.



So far, I have tried with this formula:
=Filter('directory statistics'!A2:A,'directory statistics'!$P2:$P=$P2)
It looks for the name column (A) and then returns the value if the date entered (in P2) matches the value in that sheet for the date column (P)



And then I got stuck! :)



Thank you!










share|improve this question
















Do you have a suggestion on how to make a formula for the problem written in the title?



Here is the example so that you can get an idea of the data format:



Name,Location,Category,Total views for your listing,Total uniqe page views for your listing,Average time on page (listing),total,Facebook,Reviews,Telephone,Treatment Menu,Get Directions,Book Appointment,Enquiry,Instagram,Date
John,NSW Sydney,Apple,1,1,0:00:12,0,0,0,0,0,0,0,0,0,13/Nov/2018
Mike,NSW Sydney,Orange,1,1,0:00:10,0,0,0,0,0,0,0,0,0,13/Nov/2018
Kenny,NSW Sydney,Pear,1,1,0:00:00,4,1,1,1,1,0,0,0,0,13/Nov/2018
John,NSW Sydney,Apple,20,1,0:00:12,0,0,0,0,0,0,0,0,0,14/Nov/2018
Chris,NSW Sydney,Orange,1,1,0:02:48,3,0,0,1,1,1,0,0,0,14/Nov/2018
Stef,NSW Sydney,Orange,10,1,0:07:22,6,1,1,0,1,0,1,1,1,14/Nov/2018


So, the issue is in making a formula that gets put into cells containing numerical values (from D to O), for each of the Name rows.



What it should look like is:



  • for John and for 13th and 14th of November, the cell in column B in that row should be 21

  • for Mike for 13th and 14th, it returns 1

  • for Jenny, which is not on the list, because she didn't have any values in any of the numerical columns for that day (or a group of days), it should return 0

All of these should appear in the "Main Dashboard" sheet, while the data is stored in the "directory statistics" sheet.



The predefined list of all the possible Names, Categories and Locations is in the "customList" sheet. This means that we don't need to search through the whole dataset of all the values for all the dates, but rather only search through the Names in the "customList". "Name" should be the key value that connects values in different dates.



So far, I have tried with this formula:
=Filter('directory statistics'!A2:A,'directory statistics'!$P2:$P=$P2)
It looks for the name column (A) and then returns the value if the date entered (in P2) matches the value in that sheet for the date column (P)



And then I got stuck! :)



Thank you!







google-sheets google-sheets-formula






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 14 '18 at 18:18







IgorKol

















asked Nov 14 '18 at 16:45









IgorKolIgorKol

856




856












  • Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

    – jdv
    Nov 14 '18 at 17:31

















  • Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

    – jdv
    Nov 14 '18 at 17:31
















Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

– jdv
Nov 14 '18 at 17:31





Show what you have tried as a Minimal, Complete, and Verifiable example and describe why the results are not satisfactory. No one is going to visit that link (which will be dead in an internet moment anyway) so make sure you edit the question and repeat any relevant details as formatted text.

– jdv
Nov 14 '18 at 17:31












1 Answer
1






active

oldest

votes


















0














I'm not sure why FILTER. Assuming Name in A1, and Row 1 and ColumnA in Main Dashboard set up as in my image, then:



=SUMIFS('directory statistics'!D$1:D$7,'directory statistics'!A$1:A$7,$A2,'directory statistics'!P$1:P$7,">="&B$1,'directory statistics'!P$1:P$7,"<="&C$1)


in B2 and copied down would appear to me to achieve the results you seek.



SO53305042 example






share|improve this answer

























  • thank you! @pnuts

    – IgorKol
    Nov 15 '18 at 20:28










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305042%2ffilter-by-date-range-and-sum-row-values-by-column-in-google-sheets%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














I'm not sure why FILTER. Assuming Name in A1, and Row 1 and ColumnA in Main Dashboard set up as in my image, then:



=SUMIFS('directory statistics'!D$1:D$7,'directory statistics'!A$1:A$7,$A2,'directory statistics'!P$1:P$7,">="&B$1,'directory statistics'!P$1:P$7,"<="&C$1)


in B2 and copied down would appear to me to achieve the results you seek.



SO53305042 example






share|improve this answer

























  • thank you! @pnuts

    – IgorKol
    Nov 15 '18 at 20:28















0














I'm not sure why FILTER. Assuming Name in A1, and Row 1 and ColumnA in Main Dashboard set up as in my image, then:



=SUMIFS('directory statistics'!D$1:D$7,'directory statistics'!A$1:A$7,$A2,'directory statistics'!P$1:P$7,">="&B$1,'directory statistics'!P$1:P$7,"<="&C$1)


in B2 and copied down would appear to me to achieve the results you seek.



SO53305042 example






share|improve this answer

























  • thank you! @pnuts

    – IgorKol
    Nov 15 '18 at 20:28













0












0








0







I'm not sure why FILTER. Assuming Name in A1, and Row 1 and ColumnA in Main Dashboard set up as in my image, then:



=SUMIFS('directory statistics'!D$1:D$7,'directory statistics'!A$1:A$7,$A2,'directory statistics'!P$1:P$7,">="&B$1,'directory statistics'!P$1:P$7,"<="&C$1)


in B2 and copied down would appear to me to achieve the results you seek.



SO53305042 example






share|improve this answer















I'm not sure why FILTER. Assuming Name in A1, and Row 1 and ColumnA in Main Dashboard set up as in my image, then:



=SUMIFS('directory statistics'!D$1:D$7,'directory statistics'!A$1:A$7,$A2,'directory statistics'!P$1:P$7,">="&B$1,'directory statistics'!P$1:P$7,"<="&C$1)


in B2 and copied down would appear to me to achieve the results you seek.



SO53305042 example







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 14 '18 at 19:54

























answered Nov 14 '18 at 19:44









pnutspnuts

48.5k76299




48.5k76299












  • thank you! @pnuts

    – IgorKol
    Nov 15 '18 at 20:28

















  • thank you! @pnuts

    – IgorKol
    Nov 15 '18 at 20:28
















thank you! @pnuts

– IgorKol
Nov 15 '18 at 20:28





thank you! @pnuts

– IgorKol
Nov 15 '18 at 20:28



















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305042%2ffilter-by-date-range-and-sum-row-values-by-column-in-google-sheets%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







這個網誌中的熱門文章

How to read a connectionString WITH PROVIDER in .NET Core?

Museum of Modern and Contemporary Art of Trento and Rovereto

In R, how to develop a multiplot heatmap.2 figure showing key labels successfully