Thread: Compare panel responses for each Q, filtering for loyals (sumif?, sumproduct?)

Hi all,

Very new to posting on an actual excel forums. Always used search functions and google to find what I needed and was always able to solve it alone, this one has gazumped me little bit though. Thank you in advance for any help I can get.

What I'm looking for:

Attached is a sample spreadsheet, created by exporting data entered into a simple access database. (First countries that sprang to mind, don't ask why...)

I want to measure shifts in behaviour of panelists I track. The panelists are given an anonymous code to start with, so e.g. I want to track changes in behaviour of my panelists in Argentina. Issue is: Not all panelists answer questionnaire each quarter.

I therefore want to put the panelists who contribute on a regular basis into a different grouping than panelists who only contribute for two sequential Qs, etc.

Below a few example scenarios (let's say: only for Argentina in my attached table)

scenario 1 (n=5) Product A Product B Product C
Q1 10 3 15
Q2 8 5 3
Q3 15 1 7

scenario 2 (n=3) Product A Product B Product C
Q1 5 8 3
Q2 1 0 9

scenario 3 (n=2) Product A Product B Product C
Q2 5 8 3
Q3 7 2 7

scenario 4 (n=4) Product A Product B Product C
Q1 15 5 9
Q3 5 10 4


So conditions in scenario 1 are: code in each Q present, country=Argentina, and Quarter=1, 2 or 3 for each respective row of course.

same for scenario 2, though code can only be present in Q1 and Q2.

and so on for the other scenarios.


I've used quite a simple example, so that everyone may understand what I'm looking for, I hope.... The actual table is a bit more complicated, but the basic idea is the same.

I'm not sure if sumif and/or sumproduct are the actual correct functions for my problem. I know I can do this wole thing manually, but with hundreds of records this will take me a wee bit too long for my liking.

Any help or feedback is greatly appreciated. Thank you!

Just to top it off , is there a single function, which could list all the codes in one cell of those who have contributed in all three Qs, seperated by a comma or similar?

Thanks Dan