Counting values based on dates across two datasets

K

KelVel

New member
Joined
Mar 13, 2018
Messages
5
Reaction score
0
Points
0
Excel Version(s)
16.12
Hi there,

I’m hoping someone here can help with this. I did search the forum first but I couldn't see anything that was quite like this. I have two sets of data that I want to match up. One set shows me when a person used an app:

r7663n.jpg

The other set shows me when the same people answered a survey (about the app):
ngsio2.jpg

I need to know how to put these two sets of data together so that I can find out how many times people used the app between different survey submissions.
For example (using my imaginary table, below), Sam performed used the app three times between his submission of his first survey and the second survey. How might I generate a table like this?
x5pcpl.jpg

If that was too easy, the next step I’m hoping to achieve is to note the amount of time spent in the app between surveys, e.g.:

i19mvd.jpg
Does anyone know the formula/s that would make either of these two tables possible?

Thanks in advance.



 
Hi and welcome
please post your sheet on the forum ( Go advanced - Manage attachments) so we can work on it. Thanks
 
Hi Pecoflyer,
Thanks for that - I didn't think that was possible. See attached example data.
 

Attachments

  • test_survey_appuse_dates.xlsx
    35.1 KB · Views: 14
Please check the file attached. I did some of the work. I could not get formula for grouping.
Can anyone explain how to apply two conditions. I.e. SUMIF ID is 349 then SUMIF survey category is 1,2,3, or 0.
 

Attachments

  • test_survey_appuse_dates.xlsx
    69.9 KB · Views: 11
Thank you Sahil! This is far further than I have managed to take it.
 
If you could find final solution to it, Please do post the solution here for my improvement.

Thanks
 
HOPE!! Its Works...

Please find the file attached with and do verify the solution.
 

Attachments

  • test_survey_appuse_dates.xlsx
    75 KB · Views: 15
Hi Sahil,

You've done a great job on this and it seems to be largely working. One question though: the column 'ZZ', on the 'Survey details' tab doesn't seem to be calculating the remainder of the app use times. Is that what it was intended to be? It seems to be a calculating a larger value. That said you've given me what I needed. Thank you so much!
 
Hi Kelvel



Column ZZ is calculating the times. Please ensure formats are in required formats.


"Survey details' tab doesn't seem to be calculating the remainder of the app use times" and That said you've given me what I needed"

the above statements are confusing me. did u get what u want or something is still missing or u didnt understand?

I would like to explain in such a case.
 
Hi Sahil,

My apologies. I just realised that I was assuming that column zz was only calculating app usage that happened past the last survey, but there are cases where they were using the app before the first survey and these timings were of course added to to the ZZ column. I take it all back. It's perfect.

Thank you so much for your help and expertise!
 
You must log in or register to reply here.
Back
Top